Understanding how SQL Server stores data in data files

By: Manvendra Singh   |   Read Comments   |   Related Tips: More > Database Administration

출처 : https://www.mssqltips.com/sqlservertip/4345/understanding-how-sql-server-stores-data-in-data-files/

Problem

Have you ever thought about how SQL Server stores data in its data files? As you know, data in tables is stored in row and column format at the logical level, but physically it stores data in data pages which are allocated from the data files of the database. In this tip I will show how pages are allocated to data files and what happens when there are multiple data files for a SQL Server database. 

Solution

Every SQL Server database has at least two operating system files: a data file and a log file. Data files can be of two types: Primary or Secondary.  The Primary data file contains startup information for the database and points to other files in the database. User data and objects can be stored in this file and every database has one primary data file. Secondary data files are optional and can be used to spread data across multiple files/disks by putting each file on a different disk drive. SQL Server databases can have multiple data and log files, but only one primary data file. Above these operating system files, there are Filegroups. Filegroups work as a logical container for the data files and a filegroup can have multiple data files.

The disk space allocated to a data file is logically divided into pages which is the fundamental unit of data storage in SQL Server. A database page is an 8 KB chunk of data. When you insert any data into a SQL Server database, it saves the data to a series of 8 KB pages inside the data file. If multiple data files exist within a filegroup, SQL Server allocates pages to all data files based on a round-robin mechanism. So if we insert data into a table, SQL Server allocates pages first to data file 1, then allocates to data file 2, and so on, then back to data file 1 again. SQL Server achieves this by an algorithm known as Proportional Fill.

The proportional fill algorithm is used when allocating pages, so all data files allocate space around the same time. This algorithm determines the amount of information that should be written to each of the data files in a multi-file filegroup based on the proportion of free space within each file, which allows the files to become full at approximately the same time. Proportional fill works based on the free space within a file.

Analyzing How SQL Server Data is Stored

Step 1: First we will create a database named "Manvendra" with three data files (1 primary and 2 secondary data files) and one log file by running the below T-SQL code. You can change the name of the database, file path, file names, size and file growth according to your needs.

CREATE DATABASE [Manvendra]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Manvendra', FILENAME = N'C:\MSSQL\DATA\Manvendra.mdf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'Manvendra_1', FILENAME = N'C:\MSSQL\DATA\Manvendra_1.ndf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB ),
( NAME = N'Manvendra_2', FILENAME = N'C:\MSSQL\DATA\Manvendra_2.ndf' ,SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )
 LOG ON
( NAME = N'Manvendra_log', FILENAME = N'C:\MSSQL\DATA\Manvendra_log.ldf',SIZE = 10MB , MAXSIZE = 1GB , FILEGROWTH = 10%)
GO

Step 2: Now we can check the available free space in each data file of this database to track the sequence of page allocations to the data files. There are multiple ways to check such information and below is one option. Run the below command to check free space in each data file.

USE Manvendra
GO
Select DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
    (size * 8.0/1024) as Size,
    ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
    From sys.database_files

You can see the data file names, file IDs, physical name, total size and available free space in each of the database files.

We can also check how many Extents are allocated for this database. We will run the below DBCC command to get this information. Although this is undocumented DBCC command this can be very useful information.

USE Manvendra
GO
DBCC showfilestats

With this command we can see the number of Extents for each data file. As you may know, the size of each data page is 8KB and eight continuous pages equals one extent, so the size of an extent would be approximately 64KB. We created each data file with a size of 5 MB, so the total number of available extents would be 80 which is shown in column TotalExtents, we can get this by (5*1024)/64.

UsedExtents is the number of extents allocated with data. As I mentioned above, the primary data file includes system information about the database, so this is why this file has a higher number of UsedExtents.

Step 3: The next step is to create a table in which we will insert data. Run the below command to create a table. Once the table is created we will run both commands again which we ran in step 2 to get the details of free space and used/allocated extents.

USE Manvendra;
GO
CREATE TABLE [Test_Data] (
    [Sr.No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [City] CHAR (25) DEFAULT 'Bangalore',
 [Name] CHAR (25) DEFAULT 'Manvendra Deo Singh');

Step 4: Check the allocated pages and free space available in each data file by running same commands from step 2. 

USE Manvendra
Go
Select DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
    (size * 8.0/1024) as Size,
    ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
    From sys.database_files

You can see that there is no difference between this screenshot and the above screenshot except for a little difference in the FreeSpace for the transaction log file.

Now run the below DBCC command to check the allocated pages for each data file.

DBCC showfilestats

You can see the allocated pages of each data files has not changed.

Step 5: Now we will insert some data into this table to fill each of the data files. Run the below command to insert 10,000 rows to table Test_Data.

USE Manvendra
go
INSERT INTO Test_DATA DEFAULT VALUES;
GO 10000

Step 6: Once data is inserted we will check the available free space in each data file and the total allocated pages of each data file.

USE Manvendra
Go
Select DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
    (size * 8.0/1024) as Size,
    ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
    From sys.database_files

You can see the difference between the screenshot below and the above screenshot. Free space in each data file has been reduced and the same amount of space has been allocated from both of the secondary data files, because both files have the same amount of free space and proportional fill works based on the free space within a file. 

Now run below DBCC command to check the allocated pages for each data files.

DBCC showfilestats

You can see a few more pages have been allocated for each data file. Now the primary data file has 41 extents and the secondary data files have a total of 10 extents, so total data saved so far is 51 extents. Both secondary data files have the same number of extents allocated which proves the proportional fill algorithm.

Step 7: We can also see where data is stored for table "Test_Data" for each data file by running the below DBCC command. This will let us know that data is stored on all data files.

DBCC IND ('Manvendra', 'Test_data', -1);

I attached two screenshots because the number of rows was very large to show all data file IDs where data has been stored. File IDs are shown in each screenshot, so we can see each data page and their respective file ID. From this we can say that table Test_data is saved on all three data files as shown in the following screenshots.

Step 8: We will repeat the same exercise again to check space allocation for each data file. Insert an additional 10,000 rows to the same table Test_Data to check and validate the page allocation for each data file. Run the same command which we ran in step 5 to insert 10,000 more rows to the table test_data. Once the rows have been inserted, check the free space and allocated extents for each data file.

USE Manvendra
GO
INSERT INTO Test_DATA DEFAULT VALUES;
GO 10000
Select DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
    (size * 8.0/1024) as Size,
    ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
    From sys.database_files

We can see again both secondary data files have the same amount of free space and similar amount of space has been allocated from the primary data file as well. This means SQL Server uses a proportional fill algorithm to fill data in to the data files.

We can get the extent information again for the data files.

DBCC showfilestats

Again we can see in increase in the UsedExtents for all three of the data files.

Next Steps

• Create a test database and follow these steps, so you can better understand how SQL Server stores data at a physical and logical level. 
• Explore more knowledge with SQL Server Database Administration Tips

Graphing MySQL performance with Prometheus and Grafana

출처 : https://www.percona.com/blog/2016/02/29/graphing-mysql-performance-with-prometheus-and-grafana/

50 Important Queries in SQL Server

출처 : http://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/

In this article I will explain some general purpose queries. I think each developer should have knowledge of these queries. These queries are not related to any specific topic of SQL. But knowledge of such queries can solve some complex tasks and may be used in many scenarios, so I decided to write an article on these queries.

Query 1: Retrieve List of All Database

Example:

Query 2: Display Text of Stored Procedure, Trigger, View 

Example:

Query 3: Get All Stored Procedure Relate To Database

Example:

To retrieve the View use “V” instead of “P” and for functions use “FN.

Query 4: Get All Stored Procedure Relate To Table

Example:

To retrieve the View use “V” instead of “P” and for functions use “FN.

Query 5: Rebuild All Index of Database

Example:

Query 6: Retrieve All dependencies of Stored Procedure: 

This query return all objects name that are using into stored procedure like table, user define function, another stored procedure.

Query:

Example:

Query 7: Find Byte Size Of All tables in database

Example:

Query 8: Get all table that don’t have identity column:

Query:

Example:

Query 9: List of Primary Key and Foreign Key for Whole Database

Example:

Query 10: List of Primary Key and Foreign Key for a particular table

Example:

Query 11: RESEED Identity of all tables

Example:

Query 12: List of tables with number of records

Example:

Query 13: Get the version name of SQL Server

Example:

Query 14: Get Current Language of SQL Server

Example:

Query 15: Disable all constraints of a table

Example:

Query16: Disable all constraints of all tables

Example:

Query 17: Get Current Language Id

Example:

Query18: Get precision level used by decimal and numeric as current set in Server:

Example:

Query 19: Return Server Name of SQL Server

Example:

Query 20: Get name of register key under which SQL Server is running

Example:

Query 21: Get Session Id of current user process

Example:

Query22: Get Current Value of TEXTSIZE option

Example:

Query 23: Retrieve Free Space of Hard Disk

Example:

Query24: Disable a Particular Trigger

Syntax:

Example:

Query 25: Enable a Particular Trigger

Syntax:

Example:

Query 26: Disable All Trigger of a table

We can disable and enable all triggers of a table using previous query, but replacing the "ALL" instead of trigger name.

Syntax:

Example:

Query 27: Enable All Trigger of a table

Example:

Query 28: Disable All Trigger for database

Using sp_msforeachtable system stored procedure we enable and disable all triggers for a database.

Syntax:

Example:

Query29: Enable All Trigger for database

Example:

Query30: List of Stored procedure modified in last N days

Example:

Query31: List of Stored procedure created in last N days

Example:

Query32: Recompile a stored procedure

Example:

Query 33: Recompile all stored procedure on a table

Example:

Query 34: Get all columns of a specific data type:

Query:

Example:

Query 35: Get all Nullable columns of a table

Example:

Query 36: Get All table that don’t have primary key

Example:

Query 37: Get All table that don’t have foreign key

Example:

Query 38: Get All table that don’t have identity column

Example:

Query 39: Get First Date of Current Month

Example:

Query 40: Get last date of previous month

Example:

Query 41: Get last date of current month

Example:

Query 42: Get first date of next month

Example:

Query 43: Swap the values of two columns

Example:

Query 44: Remove all stored procedure from database

Example:

Query 45: Remove all views from database

Example:

Query 46: Drop all tables

Example:

Query 47: Get information of tables’ columns

Example:

Query 48: Get all columns contain any constraints

Example:

Query 49: Get all tables that contain a view

Example:

Query 50: Get all columns of table that using in views

Example:

Read more articles on SQL Queries:

SP_Spaceused 라는 ... Procedure 를 확장한 버전

 - 파티셔닝 대상 등을 확인하거나 데이터 사이즈를 줄이기 위한 대상을 확인할때 유용함.

SELECT

    t.NAME AS TableName,

    s.Name AS SchemaName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB,

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

    sys.tables t

INNER JOIN     

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

    sys.schemas s ON t.schema_id = s.schema_id

WHERE

    t.NAME NOT LIKE 'dt%'

    AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255

GROUP BY

    t.Name, s.Name, p.Rows

ORDER BY

    t.Name

XA 트랜잭션의 이해

출처 : https://msdn.microsoft.com/ko-kr/library/aa342335(v=sql.110).aspx

SQL Server용 Microsoft JDBC Driver는 Java Platform, Enterprise Edition/JDBC 2.0 분산 트랜잭션(옵션)을 지원합니다.SQLServerXADataSource 클래스에서 가져온 JDBC 연결은 Java Platform, Enterprise Edition(Java EE) 응용 프로그램 서버와 같은 표준 분산 트랜잭션 처리 환경에 참여할 수 있습니다.

경고
SQL용 Microsoft JDBC Driver 4.2에서는 준비되지 않은 트랜잭션의 자동 롤백에 대한 기존 기능에 대해 새로운 제한 시간 옵션을 제공합니다. 자세한 내용은 이 항목의 뒷부분에 나오는 준비되지 않은 트랜잭션의 자동 롤백에 대해 서버 쪽 제한 시간 설정을 구성합니다.을 참조하세요.

분산 트랜잭션을 구현하는 데 필요한 클래스는 다음과 같습니다.

참고
XA 분산 트랜잭션 연결의 기본값은 커밋된 읽기 격리 수준입니다.

다음은 밀접하게 결합된 트랜잭션에 적용되는 추가 지침입니다.

• XA 트랜잭션을 MS DTC(Microsoft Distributed Transaction Coordinator)와 함께 사용할 경우 MS DTC의 현재 버전이 밀접하게 결합된 XA 분기 동작을 지원하지 않습니다. 예를 들어 MS DTC에는 XID(XA 분기 트랜잭션 ID)와 MS DTC 트랜잭션 ID 간에 일 대 일 매핑이 있으며 느슨하게 연결된 XA 분기에서 수행되는 작업이 다른 작업과 격리됩니다.

  MSDTC 및 밀접하게 결합된 트랜잭션에서 제공하는 핫픽스는 같은 GTRID(전역 트랜잭션 ID)를 사용하는 여러 개의 XA 분기가 한 개의 MS DTC 트랜잭션 ID에 매핑되는 밀접하게 결합된 XA 분기를 지원합니다. 이러한 지원을 통해 밀접하게 결합된 XA 분기가 SQL Server와 같은 리소스 관리자에서 다른 XA 분기의 변경 내용을 확인할 수 있습니다.

• SSTRANSTIGHTLYCPLD 플래그를 사용하면 BQUAL(XA 분기 트랜잭션 ID)은 다르지만 GTRID(전역 트랜잭션 ID) 및 FormatID(형식 ID)는 같은 밀접하게 결합된 XA 트랜잭션을 응용 프로그램에서 사용할 수 있습니다. 이 기능을 사용하려면 XAResource.start 메서드의 flags 매개 변수에 SSTRANSTIGHTLYCPLD를 설정해야 합니다.

  복사

  xaRes.start(xid, SQLServerXAResource.SSTRANSTIGHTLYCPLD);
  
  

MS DTC(Microsoft Distributed Transaction Coordinator)와 XA 데이터 원본을 함께 사용하여 분산 트랜잭션을 처리하려면 다음과 같은 단계가 필요합니다.

참고
JDBC 분산 트랜잭션 구성 요소는 JDBC 드라이버 설치의 xa 디렉터리에 있습니다. 이 구성 요소에는 xa_install.sql 및 sqljdbc_xa.dll 파일이 포함됩니다.

MS DTC 서비스 실행

MS DTC 서비스는 서비스 관리자에서 자동으로 표시하여 SQL Server 서비스를 시작할 때 자동으로 실행되도록 해야 합니다. XA 트랜잭션에 대해 MS DTC를 활성화하려면 다음 단계를 따릅니다.

Windows Vista 이상:

1. 시작 단추를 클릭하고 검색 시작 상자에 dcomcnfg를 입력한 다음 Enter 키를 눌러 구성 요소 서비스를 엽니다.시작검색 상자에 %windir%\system32\comexp.msc를 입력하여 구성 요소 서비스를 열 수도 있습니다.

2. 구성 요소 서비스, 컴퓨터, 내 컴퓨터를 확장한 다음 Distributed Transaction Coordinator를 확장합니다.

3. 로컬 DTC를 마우스 오른쪽 단추로 클릭한 다음 속성을 선택합니다.

4. 로컬 DTC 속성 대화 상자의 보안 탭을 클릭합니다.

5. XA 트랜잭션 사용 확인란을 선택하고 확인을 클릭합니다. 이렇게 하면 MS DTC 서비스가 다시 시작됩니다.

6. 확인을 다시 클릭하여 속성 대화 상자를 닫은 후 구성 요소 서비스를 닫습니다.

7. SQL Server를 중지한 후 다시 시작하여 MS DTC 변경 내용과 동기화되도록 합니다.

JDBC 분산 트랜잭션 구성 요소 구성

다음과 같은 단계를 통해 JDBC 드라이버 분산 트랜잭션 구성 요소를 구성할 수 있습니다.

1. JDBC 드라이버 설치 디렉터리에 있는 새 sqljdbc_xa.dll을 분산 트랜잭션에 참여할 모든 SQL Server 컴퓨터의 Binn 디렉터리에 복사합니다.

   참고
   32비트 SQL Server에서 XA 트랜잭션을 사용할 경우 SQL Server가 x64 프로세서에 설치되었더라도 x86 폴더에 있는 sqljdbc_xa.dll을 사용하십시오. x64 프로세서의 64비트 SQL Server에서 XA 트랜잭션을 사용할 경우 x64 폴더에 있는 sqljdbc_xa.dll을 사용하십시오.

2. 분산 트랜잭션에 참여할 모든 SQL Server 인스턴스에서 데이터베이스 스크립트 xa_install.sql을 실행합니다. 이 스크립트는 sqljdbc_xa.dll에서 호출하는 확장 저장 프로시저를 설치합니다. 이 확장 저장 프로시저는 SQL Server용 Microsoft JDBC Driver에 대한 분산 트랜잭션 및 XA 지원을 구현합니다. 이 스크립트를 실행하려면 SQL Server 인스턴스의 관리자 권한이 필요합니다.

3. 특정 사용자에게 JDBC 드라이버를 통해 분산 트랜잭션에 참여할 권한을 부여하려면 해당 사용자를 SqlJDBCXAUser 역할에 추가합니다.

SQL Server 인스턴스마다 한 번에 하나의 sqljdbc_xa.dll 어셈블리 버전만 구성할 수 있습니다. 응용 프로그램에서 여러 버전의 JDBC 드라이버를 사용하여 XA 연결을 통해 동일한 SQL Server 인스턴스에 연결해야 하는 경우가 있을 수 있습니다. 이 경우 최신 JDBC 드라이버와 함께 제공되는 sqljdbc_xa.dll을 SQL Server 인스턴스에 설치해야 합니다.

현재 SQL Server SQL Server 인스턴스에 설치되어 있는 sqljdbc_xa.dll 버전은 다음 세 가지 방법으로 확인할 수 있습니다.

1. 분산 트랜잭션에 참여할 SQL Server 컴퓨터의 LOG 디렉터리를 엽니다.SQL Server "ERRORLOG" 파일을 선택하여 엽니다. "ERRORLOG" 파일에서 "Using 'SQLJDBC_XA.dll' version ..." 구를 검색합니다.

2. 분산 트랜잭션에 참여할 SQL Server 컴퓨터의 Binn 디렉터리를 엽니다. sqljdbc_xa.dll 어셈블리를 선택합니다.

   • Windows Vista 이상: sqljdbc_xa.dll을 마우스 오른쪽 단추로 클릭한 다음 속성을 선택합니다. 그런 다음 자세히 탭을 클릭합니다.파일 버전 필드에 현재 SQL Server 인스턴스에 설치되어 있는 sqljdbc_xa.dll 버전이 표시됩니다.

3. 다음 섹션의 코드 예제에서와 같이 로깅 기능을 설정합니다. 출력 로그 파일에서 "Server XA DLL version:..." 구를 검색합니다.

준비되지 않은 트랜잭션의 자동 롤백에 대해 서버 쪽 제한 시간 설정을 구성합니다.

경고
이 서버 쪽 옵션은 SQL Server용 Microsoft JDBC Driver 4.2의 새로운 기능입니다. 업데이트된 동작을 가져오려면 서버에서 sqljdbc_xa.dll이 업데이트되었는지 확인하십시오. 클라이언트 쪽 제한 시간 설정에 대한 자세한 내용은 XAResource.setTransactionTimeout()을 참조하세요.

분산 트랜잭션의 제한 시간 동작을 제어하는 두 가지 레지스트리 설정(DWORD 값)이 있습니다.

• XADefaultTimeout(초): 사용자가 제한 시간을 지정하지 않는 경우 사용할 기본 제한 시간 값입니다. 기본값은 0입니다.

• XAMaxTimeout(초): 사용자가 설정할 수 있는 최대 제한 시간 값입니다. 기본값은 0입니다.

이러한 설정은 SQL Server 인스턴스와 관련되며 다음 레지스트리 키 아래에 만들어야 합니다.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<version>.<instance_name>\XATimeout

참고
64비트 컴퓨터에서 실행되는 32비트 SQL Server의 경우 다음 키 아래에 레지스트리 설정을 만들어야 합니다. HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL<version>.<instance_name>\XATimeout

제한 시간 값은 트랜잭션이 시작되고 제한 시간이 만료되는 경우 SQL Server에 의해 롤백될 때 각 트랜잭션에 대해 설정됩니다. 제한 시간을 이러한 레지스트리 설정 및 사용자가 XAResource.setTransactionTimeout()을 통해 지정한 설정에 따라 결정됩니다. 이러한 제한 시간 값이 해석되는 방식에 대한 몇 가지 예제는 다음과 같습니다.

• XADefaultTimeout = 0, XAMaxTimeout = 0

  기본 제한 시간 없음이 사용되고 최대 제한 시간 없음이 클라이언트에 적용됨을 의미합니다. 이 경우 클라이언트에서 XAResource.setTransactionTimeout을 사용하여 제한 시간을 설정하는 경우에만 트랜잭션의 제한 시간이 설정됩니다.

• XADefaultTimeout = 60, XAMaxTimeout = 0

  클라이언트에서 제한 시간을 지정하지 않는 경우 모든 트랜잭션의 제한 시간이 60초가 됨을 의미합니다. 클라이언트에서 제한 시간을 지정하면 해당 제한 시간 값이 사용됩니다. 최대 제한 시간 값 없음이 적용됩니다.

• XADefaultTimeout = 30, XAMaxTimeout = 60

  클라이언트에서 제한 시간을 지정하지 않는 경우 모든 트랜잭션의 제한 시간이 30초가 됨을 의미합니다. 클라이언트에서 제한 시간을 지정하면 60초(최대값)보다 작은 경우에 한해 클라이언트의 제한 시간이 사용됩니다.

• XADefaultTimeout = 0, XAMaxTimeout = 30

  클라이언트에서 제한 시간을 지정하지 않는 경우 모든 트랜잭션의 제한 시간이 30초(최대값)가 됨을 의미합니다. 클라이언트에서 제한 시간을 지정하면 30초(최대값)보다 작은 경우에 한해 클라이언트의 제한 시간이 사용됩니다.

sqljdbc_xa.dll 업그레이드

새 버전의 JDBC 드라이버를 설치할 때 서버에서 sqljdbc_xa.dll을 업그레이드하기 위해 새 버전의 sqljdbc_xa.dll을 사용해야 합니다.

중요
유지 관리 창에 있는 동안 또는 프로세스에 MS DTC 트랜잭션이 없을 때 sqljdbc_xa.dll을 업그레이드해야 합니다.

1. Transact-SQL 명령 DBCC sqljdbc_xa (FREE)를 사용하여 sqljdbc_xa.dll을 언로드합니다.

2. JDBC 드라이버 설치 디렉터리에 있는 새 sqljdbc_xa.dll을 분산 트랜잭션에 참여할 모든 SQL Server 컴퓨터의 Binn 디렉터리에 복사합니다.

   sqljdbc_xa.dll의 확장 프로시저가 호출되면 새 DLL이 로드됩니다. 새 정의를 로드하기 위해 SQL Server를 다시 시작할 필요가 없습니다.

사용자 정의 역할 구성

특정 사용자에게 JDBC 드라이버를 통해 분산 트랜잭션에 참여할 권한을 부여하려면 해당 사용자를 SqlJDBCXAUser 역할에 추가합니다. 예를 들어 다음과 같은 Transact-SQL 코드를 사용하여 이름이 'shelby'(SQL 표준 로그인 사용자 이름)인 사용자를 SqlJDBCXAUser 역할에 추가합니다.

USE master
GO
EXEC sp_grantdbaccess 'shelby', 'shelby'
GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'shelby'

SQL 사용자 정의 역할은 데이터베이스별로 정의합니다. 보안을 위해 고유한 역할을 만들려면 각 데이터베이스마다 역할을 정의하고 각 데이터베이스의 방식대로 사용자를 추가해야 합니다. SqlJDBCXAUser 역할은 master에 상주하는 SQL JDBC 확장 저장 프로시저에 대한 액세스 권한을 부여하는 데 사용되므로 master 데이터베이스에서만 정의할 수 있습니다. 먼저 개별 사용자에게 master에 대한 액세스 권한을 부여한 다음 master 데이터베이스에 로그인된 상태에서 SqlJDBCXAUser 역할에 대한 액세스 권한을 부여해야 합니다.

import java.net.Inet4Address;
import java.sql.*;
import java.util.Random;
import javax.transaction.xa.*;
import javax.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class testXA {

   public static void main(String[] args) throws Exception {

      // Create variables for the connection string.
      String prefix = "jdbc:sqlserver://";
      String serverName = "localhost";
      int portNumber = 1433;
      String databaseName = "AdventureWorks"; 
      String user = "UserName"; 
      String password = "*****";
      String connectionUrl = prefix + serverName + ":" + portNumber
         + ";databaseName=" + databaseName + ";user=" + user + ";password=" + password;

      try {
         // Establish the connection.
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         Connection con = DriverManager.getConnection(connectionUrl);

         // Create a test table.
         Statement stmt = con.createStatement();
         try {
            stmt.executeUpdate("DROP TABLE XAMin"); 
         }
         catch (Exception e) {
         }
         stmt.executeUpdate("CREATE TABLE XAMin (f1 int, f2 varchar(max))");
         stmt.close();
         con.close();

         // Create the XA data source and XA ready connection.
         SQLServerXADataSource ds = new SQLServerXADataSource();
         ds.setUser(user);
         ds.setPassword(password);
         ds.setServerName(serverName);
         ds.setPortNumber(portNumber);
         ds.setDatabaseName(databaseName);
         XAConnection xaCon = ds.getXAConnection();
         con = xaCon.getConnection();

         // Get a unique Xid object for testing.
         XAResource xaRes = null;
         Xid xid = null;
         xid = XidImpl.getUniqueXid(1);

         // Get the XAResource object and set the timeout value.
         xaRes = xaCon.getXAResource();
         xaRes.setTransactionTimeout(0);

         // Perform the XA transaction.
         System.out.println("Write -> xid = " + xid.toString());
         xaRes.start(xid,XAResource.TMNOFLAGS);
         PreparedStatement pstmt = 
         con.prepareStatement("INSERT INTO XAMin (f1,f2) VALUES (?, ?)");
         pstmt.setInt(1,1);
         pstmt.setString(2,xid.toString());
         pstmt.executeUpdate();

         // Commit the transaction.
         xaRes.end(xid,XAResource.TMSUCCESS);
         xaRes.commit(xid,true);

         // Cleanup.
         con.close();
         xaCon.close();

         // Open a new connection and read back the record to verify that it worked.
         con = DriverManager.getConnection(connectionUrl);
         ResultSet rs = con.createStatement().executeQuery("SELECT * FROM XAMin");
         rs.next();
         System.out.println("Read -> xid = " + rs.getString(2));
         rs.close();
         con.close();
      } 

      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }
   }
}

class XidImpl implements Xid {

   public int formatId;
   public byte[] gtrid;
   public byte[] bqual;
   public byte[] getGlobalTransactionId() {return gtrid;}
   public byte[] getBranchQualifier() {return bqual;}
   public int getFormatId() {return formatId;}

   XidImpl(int formatId, byte[] gtrid, byte[] bqual) {
      this.formatId = formatId;
      this.gtrid = gtrid;
      this.bqual = bqual;
   }

   public String toString() {
      int hexVal;
      StringBuffer sb = new StringBuffer(512);
      sb.append("formatId=" + formatId);
      sb.append(" gtrid(" + gtrid.length + ")={0x");
      for (int i=0; i<gtrid.length; i++) {
         hexVal = gtrid[i]&0xFF;
         if ( hexVal < 0x10 )
            sb.append("0" + Integer.toHexString(gtrid[i]&0xFF));
         else
            sb.append(Integer.toHexString(gtrid[i]&0xFF));
         }
         sb.append("} bqual(" + bqual.length + ")={0x");
         for (int i=0; i<bqual.length; i++) {
            hexVal = bqual[i]&0xFF;
            if ( hexVal < 0x10 )
               sb.append("0" + Integer.toHexString(bqual[i]&0xFF));
            else
               sb.append(Integer.toHexString(bqual[i]&0xFF));
         }
         sb.append("}");
         return sb.toString();
      }

      // Returns a globally unique transaction id.
      static byte [] localIP = null;
      static int txnUniqueID = 0;
      static Xid getUniqueXid(int tid) {

      Random rnd = new Random(System.currentTimeMillis());
      txnUniqueID++;
      int txnUID = txnUniqueID;
      int tidID = tid;
      int randID = rnd.nextInt();
      byte[] gtrid = new byte[64];
      byte[] bqual = new byte[64];
      if ( null == localIP) {
         try {
            localIP = Inet4Address.getLocalHost().getAddress();
         }
         catch ( Exception ex ) {
            localIP =  new byte[] { 0x01,0x02,0x03,0x04 };
         }
      }
      System.arraycopy(localIP,0,gtrid,0,4);
      System.arraycopy(localIP,0,bqual,0,4);

      // Bytes 4 -> 7 - unique transaction id.
      // Bytes 8 ->11 - thread id.
      // Bytes 12->15 - random number generated by using seed from current time in milliseconds.
      for (int i=0; i<=3; i++) {
         gtrid[i+4] = (byte)(txnUID%0x100);
         bqual[i+4] = (byte)(txnUID%0x100);
         txnUID >>= 8;
         gtrid[i+8] = (byte)(tidID%0x100);
         bqual[i+8] = (byte)(tidID%0x100);
         tidID >>= 8;
         gtrid[i+12] = (byte)(randID%0x100);
         bqual[i+12] = (byte)(randID%0x100);
         randID >>= 8;
      }
      return new XidImpl(0x1234, gtrid, bqual);
   }
}

JDBC 드라이버로 트랜잭션 수행

Sql server  버전별 페이징 쿼리

출처 : http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/

USE AdventureWorks2012
GO
--------------------------------------------------
-- SQL Server 2012
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET (@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO

--------------------------------------------------
-- SQL Server 2008 / R2
-- SQL Server 2005
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
GO

--------------------------------------------------
-- SQL Server 2000
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM
(
SELECT TOP (@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
FROM
(
SELECT TOP ((@PageNumber)*@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
) AS SOD
ORDER BY SalesOrderDetailID DESC
) AS SOD2
ORDER BY SalesOrderDetailID ASC
GO

* 가끔씩 필요한 백업파일의 백업사이즈 구하기

SELECT TOP 100

 s.database_name,

m.physical_device_name,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

CASE s.[type] WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

WHERE s.database_name = 'ezmesG2' -- Remove this line for all the database

ORDER BY backup_start_date DESC, backup_finish_date

GO

로컬 혹은 원격 서버의 패스워드를 변경하기 위한 방법

출처 : http://community.spiceworks.com/how_to/1966-how-to-change-local-user-or-admin-passwords-on-remote-computers

Steps (4 total)

1

Obtain PSTools

You can get PSTools from http://technet.microsoft.com/en-us/sysinternals/bb896649

2

Open the Command Console

To open the Command Console 
1. Click on Start 
2. Click on “Run” 
3. Type “CMD” in the “Run” box. 
4. Change the prompt to the PSTools folder by typing the full folder path. Example: C:\Blabla\PSTools

3

For a single computer

Inside the Command Console type: 

pspasswd.exe \\Remote_computer_name -u user_name -p new_password

Obviously you would replaced Remote_computer_name with the name of your remote PC, user_name with the actual username, and new_password with the actual new password for that user.

4

For a multiple computers:

Expand

1. Create a text file named PCs.txt or whatever you want .txt with the names of the PCs to change a given local user’s password. One computer name per line, no spaces or extra lines.

2. Save the text file in the PSTools folder for convenience or you’ll have to type the full file path in the command.

3. Inside the Command Console type: 
pspasswd.exe @pcs.txt -u user_name -p new_password

Conclusion

And that is all folks.
Visit http://technet.microsoft.com/en-us/sysinternals/bb897543 for more info.

PsPasswd v1.23

By Mark Russinovich

Published: October 17, 2012

 Download PsTools (1.6 MB)

Rate:

Share this content

 

 

 

 

 

 

Introduction

Systems administrators that manage local administrative accounts on multiple computers regularly need to change the account password as part of standard security practices. PsPasswd is a tool that lets you change an account password on the local or remote systems, enabling administrators to create batch files that run PsPasswd against the computers they manage in order to perform a mass change of the administrator password.

PsPasswd uses Windows password reset APIs, so does not send passwords over the network in the clear.

 

Installation

Just copy PsPasswd onto your executable path, and type "pspasswd" with the command-line syntax shown below..

 

Using PsPasswd

You can use PsPasswd to change the password of a local or domain account on the local or a remote computer.

usage: pspasswd [[\\computer[,computer[,..] | @file [-u user [-p psswd]]] Username [NewPassword]

computer	Perform the command on the remote computer or computers specified. If you omit the computer name the command runs on the local system, and if you specify a wildcard (\\*), the command runs on all computers in the current domain.
@file	Run the command on each computer listed in the text file specified.
-u	Specifies optional user name for login to remote computer.
-p	Specifies optional password for user name. If you omit this you will be prompted to enter a hidden password.
Username	Specifies name of account for password change.
NewPassword	New password. If ommitted a NULL password is applied.

 

Download PsTools
(1.6 MB)

경제의 교양을 읽는다 - 현대편

##경제의 교양을 읽는다.

#주류 경제학을 밖에서 비판하고 대안을 제시하다.

10. 상품에 의한 상품생산 (피에로 스라파)

- 자본주의 경제의 가장 중요한 특성은 자본을 사용하는 생산활동이다. 자본은 자본재 들의 집합이다. 그런데 자본재는 이중의 특성을 가지고 있다. 자본재는 다른 상품들을 생산하기 위해 사용되는 생산수단인 동시에, 그 자체가 경제 내에서 다른 자본재들에 의해 생산되는 상품이다. 다시 말해 자본재는 생산된 생산수단이다. 따라서 자본을 사용하는 생산활동, 즉 자본주의 경제의 특성은 ‘상품에 의한 상품 생산’이다.

11. 자본 축적론 (조앤 로빈슨)

- 조앤 로빈슨의 자본축적론은 가치 이론에 천착하여 주어진 자원의 효율적 배분의 문제로 경제학적 문제를 제한해온 신고전학파 경제학의 문제의식에 대한 비판인 동시에, 항상 유효수요의 문제에 직면해 있는 경제가 시간에 걸쳐 어떻게 변화해가는가를 영국 고전학파의 관전에사 바라보면서 (케인즈에서 시작하는) 현재적인 분석방법을 사용하려는 건설적인 시도이다.

12. 통화주의라는 재앙 (니콜라스 칼도어)

- 칼도어는 통화주의라는 재앙 을 인생 끝 무렵인 1982년에 출간하였다. 이 책에서 칼도어는 통화주의의 근간에 깔려 있는 화폐 개념, 현실에서 관찰 되는 경제현상에 대한 통화주의적 해석이 지닌 문제점, 그리고 통화주의의 정책 제안이 실제로 실현되었을 때 가져올 ‘재앙’을 명철하게 밝히고 비판한다. 칼도어는 케인즈나 밀턴 프리드먼과 같은 의미의 화폐이론가가 아니다.

#주류 경제학을 안에서 비판하고 확장하다 II

13. 경험에 기초한 미시경제학 (하버트 사이먼)

- 사이먼은 개별 경제주체를 현실에서는 존재할 수 없는 완전무결한 자동기계로 상정하여 이론을 펼치는 데 반대했다. 대신 경제주체는 계산과 정보처리 능력이 제한되어 있어, 휴리스틱이라는 체계적이지 않은 방법으로 선택 대안들을 탐색하며, 일정한 목표수준을 충족하는 대안이 생겨나면 바로 탐색을 중단하는 만족자 이지, 효용 극대화자가 아니라고 생각했다.

14. 행동경제학의 토대가 된 인지심리학 연구

- 현실에서 경제주체가 주류 경제학에서의 ‘이상화된 경제주체’와 어떻게 다른지를 카너먼의 연구는 잘 보여준다. 그렇다면 정책 수단을 통해 전자를 후자에 더욱 근접하도록 하는 것이 (인지)심리학을 토대로 한 경제학, 즉 행동경제학이 할 일인가 아니면 ‘이상화된 경제주체’의 상에 수정을 가하여 합리성의 기준을 바꾸는 것이 더욱 생산적일까? 카너먼은 이 문제에 대하 첫 번째 입장을 옹호한다. 그는 심리학을 미시적 기초로 하는 경제학을 실천하는 사회과학자는 개별 경제주체들 자신들보다 그 자신들이 행복해질 수 있는 방법을 더 잘 알 수 있다는 주장을 옹호하고, 온정주의에 원칙적으로 반대할 이유는 없다고 생각한다.

15. 경제학에서의 합리성 : 구성적 형태와 생태적 형태 (버논 스미스)

- 버논 스미스의 비인격적 교환/인격적 교환 이라는 2가지 주제의 실험은 다음과 같은 정형화된 사실을 낳았다. 첫째, 경쟁이 제대로 작동하여 효율성을 달성하는 시장이 존재하기 위한 조건에 시장참가자들이 완전한 정보를 갖고 있어야 한다는 점은 포함될 필요가 없다. 오히려 그러한 조건이 충족되는 경우 시장의 효율성이 저하되는 것을 스미스의 실험실 연구는 잘 보여준다. 둘째 인격적 교환 중 두 사람 사이의 일회적 상호작용만으로 그 결과가 결정되는 상황에서, 사회적 교환(상호 이익을 위한 협력)이 빈번하게 일어난다는 점이 알려지게 되었다. 즉 인격적 교환에 임하는 개인들은 타인을 고려하는 행위를 흔히 보인다는 결론이 받아들여지게 되었다.

#협의의 경제학을 넘어서

16. 치명적 자만 (프리드리히 하이에크)

- 사회주의가 이론적으로 오류라는 점을 사회주의자들에게 보이려는 목표를 갖고 있다. 그는 이 책에서도 사회주의를 비판하고 시장주의를 옹호하는 자신의 사회절학적 입장을 반복했는데, 다른 저작보다 더 넓은 시각에서 군더더기 없이 자신의 입장을 선명하게 드러냈다.

17. 자본주의, 사회주의, 민주주의 (조지프 슘페터)

- 자본주의의 안락사와 사회주의의 자연스런 도래를 예언했다. 슘페터는 여기서 마르크스의 예언에 동의했지만 노동가치론을 위시한 마르크스의 이론은 비판했다. 무엇보다 그는 마르크스와 달리 폭력혁명을 통해 자본주의를 타도할 필요가 없다고 주장했다. 사회주의를 위한 혁명은 경제적 혁명이지 결코 정치적 혁명이 되어서는 안 된다고 말한다. 이미 자본주의 내부에서 사회주의로의 점진적 이행을 촉진시키는 요인들이 등장했기 때문이다.

18. 풍요한 사회 (존 갤브레이스)

- 풍요한 사회는 경제성장에 성공해 풍요로운 미국 사회가 겪는 새로운 종류의 질병들에 대해 진단하고 처방했다. 그의 다른 책과 마찬가지로 경제를 정치 및 사회와 결합시켜 논의하였다. 또한 이를 위해 주류 경제학뿐만 아니라 과거의 중요 경제사상들을 근거로 활용했다.

19. 윤리학과 경제학 (아마티아 센)

- 센은 공리주의와 이에 근거한 신고전학파 경제학을 비판하면서 이에 대한 대안을 모색했다. 경제학과 역사적으로 긴밀한 관계에 있으며 현대의 경제학이 불가결한 기반으로 삼는 공리주의는 인간이 쾌락과 고통 등 감정을 가진 존재라는 점을 주목하면서 이에 근거해 이론과 이념을 설정했다. 경제학은 1870년대에 인간이 합리적이라고 주장하면서 공리주의를 받아들여, 쾌락과 고통을 계산해 이를 극대화할 수 있는 능력으로 인간의 합리성을 파악했다. 그 후 경제학은 효용이론을 선택이론으로 변화시켰다.

20. 갈등의 전략 (토머스 쉘링)

- 쉘링이 이 책에서 분석하는 상황들은 모두 비협조적 게임의 상황이다. 쉘링은 전략적 상황에 놓은 합리적인 개인들이 어떻게 자신의 이득을 극대화하기 위해 행동하는가를 아주 치밀한 논리 전개로 분석하였다. 쉘링이 드는 예들은 당시 국제관계에서 초미의 관심사였던 군비증강처럼 무척이나 심각하고 무거운 주제부터 약속 장소를 정하는 문제처럼 사소해 보이지만 매일매일의 일상에서 우리가 마주치는 아주 현실적이고 구체적인 문제들까지 포괄한다. 쉘링이 이야기하듯 이 책에 실린 에세이들은 ‘순수’연구와 ‘응용’연구의 혼합물이다.