수안이의 컴퓨터 연구실

http://www.devarticles.com/c/a/sql-serv ··· oring%2F

SQL Server Hardware Tuning and Performance Monitoring
(Page 1 of 5 )

In this article, Sayed will lead the way in presenting you guys with a performance enhancing solution for your system. Discover how to optimise your system by investigating what hardware you really need for your applications. 

The hardware configuration that SQL Server runs on top of can make or break you. How do you know how much hardware is really needed by your applications? How do you know if your applications have grown to the point where they are now overloading your system? 

In this article, I will give you the information you need to solve the specific bottleneck or to resolve future bottlenecking issues. To find out your hardware bottleneck is to use Performance Monitor. Using Performance Monitor, we can identify issues in each of a set of performance objects.

In the following lines we will see what the best way for hardware tuning and performance monitoring is:

I would like to say that the subject of SQL server performance tuning is big and complex. So, I will try to explain today SQL server hardware tuning and performance monitoring.

If you need more covering you can visit "The God Father", of course I mean SQL-server-performance website maintained by Brad.  Because it has a lot of valuable information about SQL Server performance tuning subject. At the end of this article I will give you resources to help you and to understand more about this important topic.

SQL Server Hardware Tuning and Performance Monitoring - Performance Monitoring Issues
(Page 2 of 5 )

Memory Performance Monitoring

Now that you know the memory limitations in SQL Server imposed by the operating system, what can you do to optimise that memory and determine if you need more? This is where we get into what is popularly called The Art of Performance Monitoring .

Due to the dynamic nature of the memory in SQL Server 7.0 and SQL server 2000, Microsoft removed support for one of the most useful tools available in SQL Server 6.5, DBCC MEMUSAGE. Now, to get this information, you have to monitor a variety of performance counters in the Buffer Manager object and the Cache Manager object since the information is no longer statically mapped in a table like sysconfigures.

While this handy tool is listed as Unsupported  now and no longer returns the breakdown of memory usage, try running it. You will see that this unsupported snap shot continues to return the top 20 list of buffered tables and indexes. This can be a very handy list. When analysing performance of an individual application un a test environment, it can be invaluable.

The Art of Performance Monitoring

Performance monitoring is an art, in that it is a combination of talent, experience, knowledge, and sometimes just plain luck. How do you know if you can do it? You have to try, try, and try again. Keep at it, read up on it. Keep a performance monitor continually open against your production server. Here are some good guidelines to get you started.

1. Make sure that you are running your typical processes (SQL Server) and  work loads (queries and stored procedures) during your monitoring.

2. Do not just do real-time monitoring of your servers. Capture long running logs. In Windows NT, install the Datalog/Monitor service from the NT Resource Kit; this functionality is available out of the box in Windows 2000.

3. Always have disk counters turned on by running from a command prompt the command DISKPERF Y  and then rebooting. Even in a production environment, the overhead is minimal;  the last thing you want to do in the middle of a crisis where logical and physical disk counters are necessary is to have to reboot.

4. For routine, daily, desktop monitoring, set up the chart windows with an interval of 18 seconds. In both the Windows NT PerMon and the Windows 2000 MMC SysMon, this will give your chart a Window of 30 minutes. For me, this has proven to be the ideal interval for both seeing the past and minimizing the impact on the server.

5. Utilize SQL Profiler for individual queries and processes in coordination with PerMon or SysMon to get a good picture of the impact of individual queries.

6. Know the terminology of Performance Monitoring. Objects are lists of individual statistics available. An example is the Processor object. A counter is a single statistic that falls under the heading of an object. An example is the  per centprocessor Time counter under the Processor object. An instance is further breakdown of a counter statistic into duplicate components. Not all counters will have separate instances. The per centprocessor Time counter has instances for each processor and a _Total instance as a summery of all processor activity.

7. Know your tools. While you may know how to set up a chart in PerMon, learn how to set up a log with Datalog or Performance Log. Other tools to be familiar with are DBCC MEMUSAGE, Task Manager, and SQL Enterprise Manager Current Activity.

8. Do not be afraid to experiment. The BackOffice Resource Kit has tools in it for creating test data (DataSim), creating test databases (DBGen), and simulating loading from multiple clients (SqlLS).

SQL Performance Monitoring and Bottlenecks

Bottlenecks occur when the hardware resources cannot keep up with the demands of the software. For example, when a software process or combination of processes, wants more I/O from a disk than the disk can physically deliver, a bottleneck occurs at the disk. When the CPU subsystem becomes too saturated and processes are waiting, a bottleneck has occurred. Bottlenecks are usually fixed in one of two ways.

The first is to identify the limiting hardware and increase its capabilities. In other words, get a faster hard driver or increase the speed of the CPU. The second way is to make the software processes utilize the hardware more efficiently. This could be done by putting an index on a table so that either the disk I/O necessary to service a query is reduced, or the CPU units necessary to process a join are lessened.

The following are five key areas to monitor when tracking server performance and identifying bottlenecks. Each bottleneck candidate will have varied performance monitoring objects and couners to consider.

• Memory usage SQL Server needs, relative to itself and to the operating system memory. If SQL Server has enough memory but the operating  system is starved of memory such that it has to frequently swap through the pagefile to disk, overall performance will suffer dramatically.
• CPU processor utilization High CPU utilization rates indicate the CPU subsystem is underpowered. Solutions could be upgrading the CPU or increasing the number of processors.
• Disk I/O performance Failure of the disk or disk controller to satisfy read or write requirements in a timely manner impacts performance.
• User connections. Improperly setting the number of user connections could rob memory otherwise available to SQL Server.
• Blocking Locks One process keeps another process from accessing or updating data. This is particularly noticeable to users and is the cause of some of your most server performance problems from a user perspective.

SQL Server Hardware Tuning and Performance Monitoring - Memory Tuning Issues
(Page 3 of 5 )

Memory Tuning: The Operating System and SQL Server

Start your intensive analysis of memory by looking at two counters,

• Memory: Available Bytes
• Memory: Pages Faults/sec

The Available Bytes counter tells how much memory is available for use by processes.

The Pages Faults/sec counter tells us the number of hard page faults, pages which had to be retrieved from the hard disk since they were not in working memory. It also includes the number of pages written to the hard disk to free space in the working set to support a hard page fault.

A low number for Available Bytes indicates that there may not be enough memory available or processes, including SQL Server, may not be releasing memory. A high number of  Pages Faults/sec indicate excessive paging. Further looks at individual instances of  process:Page Faults/sec to see if the SQL Server process, for example, has excessive paging. A low rate of  Pages Faults/sec (commonly 5-10 per second) is normal, as the operating system will continue to do some house keeping on the working set.

As previously noted, starting with SQL Server 7, memory is auto-tuning by default. In general though, you want to give SQL as much dedicated memory as possible. This is mostly dependent on what other application may be running on the server. By using the sp_configure stored procedure, you can set the values to MIN SERVER MEMORY and MAX SERVER MEMORY to dedicated values.

If SQL Server is the only application on the server, MIN SERVER MEMORY and MAX SERVER MEMORY to the same value. If SQL Server co-exists with one or more applications, lower the MIN SERVER MEMORY setting to count for the memory demands of the other application(s). If the other application fails to start in a timely manner, it may be because SQL Server has been operating at or near the MAX SERVER MEMORY setting and is slow in releasing memory to the new, and now starved, application. In this instance, lower the value of MAX SERVER MEMORY. Obviously, MAX SERVER MEMORY always needs to be greater than or equal to MIN SERVER MEMORY.

NOTE: If you have installed and are running the Full-Text Search support (Microsoft Search service, also known as MSSearch), then you must set the max server memory option manually to leave enough memory for the MSSearch service to run. Microsoft supplies a handy formula here:

Total Virtual Memory (SQL Server MAX + Virtual Memory for Other Processes) = 1.5 * Server Physical Memory.   

Once we have tuned the SQL Server memory settings, it is a good idea to decide if you want SQL Server 7/2000 to tune the process memory automatically or have values set for the configuration. For better performance, you can lock the amount of working set memory that SQL Server reserves. The trade-off here is that you may receive out of memory messages from other applications on the same server.

If you do decide to fix the amount of working set memory, two configuration settings are necessary. First, equalize the MIN SERVER MEMORY and MAX SERVER MEMORY settings. Then turn on the SET WORKING SET SIZE configuration flag using sp_configure. MAX SERVER MEMORY should generally not exceed the RAM available for the server.

SQL Server Process Memory Tuning

Once you have gotten the overall OS and SQL server memory tuned, look further at the SQL Server memory usage. Four counters are desirable here:

• process: Working Set:sqlserver
• SQL Server: Buffer Manager: Buffer Cach Hit Ratio
• SQL Server: Buffer Manager: Free Buffers
• SQL Server: Memory Manager: Total Server Memory (KB)

The process: Working Set:sqlserver instance shows the amount of memory that SQL Server is using. If the number is consistently lower than the amount SQL Server is configured to muse by the MIN SERVER MEMORY and MAX SERVER MEMORY  options, then SQL Server is configured for too much memory. Otherwise, you may need to increase RAM and  MAX SERVER MEMORY.

Buffer Cach Hit Ratio  should  be consistently greater than 90. This indicates that the data cach supplied 90 per cent of the requests for data. If this value is consistently low, it is a very good indicator that more memory is needed by SQL Server. If Available Bytes is low, this means that we need to add more RAM.

When Free Buffers is low, this means that there is not enough RAM to maintain a consistent amount of data cach. It too, is indicative of a need for more memory.

If Total Server Memory for SQL Server is consistently higher than the overall server memory, it indicates that there is not enough RAM.

SQL Server Hardware Tuning and Performance Monitoring - More Performance Monitoring Issues
(Page 4 of 5 )

CPU Performance Monitoring

In CPU performance monitoring, we are going to be using several counters:

• Processor: Per cent Processor Time
• Processor: Per cent Privileged Time
• Processor: Per cent User Time
• System: Per cent Total Processor Time

Generally, CPU performance monitoring is straightforward. You need to start by monitoring Processor: per cent Processor Time. If you have more than one processor, you should monitor each instance of this counter and also monitor System: per cent Total Processor Time to determine the average for all processors.

Utilization rates consistently above 80-90 per cent may indicate a poorly tuned or designed application. On the other hand, if you have put all the other recommendations of this book into use, they may indicate a need for a more powerful CPU subsystem. In general, I would spend a little bit of time analysing the applications before immediately going out and buying three more processors.

Spending this time experimenting to discover CPU performance problems and correcting them through software improvements will often keep you from just spending money on a more powerful CPU that only covers up poorly written software for little or no time.

If you do see high CPU utilization, you will then want to monitor Processor: Per cent Privileged Time. This is the time spent performing kernel level operations, such as disk I/O. If his counter is consistently above 80-90 per cent and corresponds to high disk performance counters, you may have a disk bottleneck rather than a CPU bottleneck.

What about SQL Server? Processor: Per cent User Time measures the amount of processor time consumed by non-kernel level applications. SQL is such an application. If this is high and you have multiple processes running on a server, you may want to delve further by looking at specific process instances through the instances of the counter Process: Per cent User Time. This can be very useful for occasions such as when our operating system engineers installed new anti-virus software on all our servers. It temporarily brought them to their knees until we were able to determine the culprit through analysing Process:Per cent User Time for the anti-virus software instance.

Disk Tuning and Performance Monitoring

Begin disk performance monitoring by looking at the following counters:

• PhysicalDisk: Per cent Disk Time
• PhysicalDisk: Current Disk Queue Length
• PhysicalDisk: Avg. Disk Queue Length

Applications and systems that are I/O-bound may keep the disk constantly active. This is called disk thrashing.

You should always know how many channels, what types of arrays, how many disks are in each array, and which array/channel your data and transaction logs are located on before you start thinking about disk performance tuning.

The PhysicalDisk: Per cent Disk Time counter monitors the percentage of time that the disk is conducting check the PhysicalDisk: Current Disk Queue Length counter to see the number of requests that are queued up waiting for disk access.

It is important at this point to be familiar with your disk subsystem. If the number of waiting I/O requests is a sustained value more than 1.5 to 2 times the number of spindles making up the physical disk, you have a disk bottleneck. For example, a RAID 5 configuration with seven spindles/disks would be a candidate for disk performance tuning should the Current Disk Queue Length continually rest above 12-14.

To improve performance in this situation, consider adding faster disk drives, moving some processes to an additional controller-disk subsystem, or adding additional disks to a RAID 5 array.

Most disks have one spindle, although RAID devices usually have more. A hardware RAID 5 device appears as one physical disk in Windows NT PerMon or Windows 2000 sysMon. RAID devices created through software appear as multiple instances.

WARNING: The Per cent Disk Time counter can indicate a value greater than 100 per cent if you are using a hardware based RAID configuration. If it does, use the PhysicalDisk: Avg. Disk Queue Length counter to determine the average number of system requests waiting for disk access. Again, this is indicative of a performance problem if a sustained value of 1.5 to 2 times the number of spindles in the array is observed.

SQL Server Hardware Tuning and Performance Monitoring - SQL Server Performance Tuning Resources, Websites and Articles
(Page 5 of 5 )

sql-server-performance.com: A website dedicated to SQL Server Performance Tuning. Contains a lot of information to cover performance tuning. A very valuable site :)

Communities & Newsgroups

http://www.microsoft.com/technet/itcommunity/chats/trans/sql/sql0513.asp
Review expert answers to user questions with this chat regarding getting the most from SQL Server.

http://www.microsoft.com/technet/itcommunity/chats/trans/sql/pass1124.asp
this chat was hosted by Neil MVP/MCSE, Protech Computing Ltd., as well as several MVPs and members of the Microsoft SQL Server team.

http://support.microsoft.com/default.aspx?scid=kb;en-us;322883 
The performance tuning guide that this article references provides principles and techniques to help database administrators configure SQL Server 7.0 for maximum performance and to help them determine the cause of poor performance in a SQL Server.

http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
To troubleshoot performance issues, you must complete a series of steps to isolate and determine the cause of the problem. Possible causes include: Blocking System resource contention Application design problems Queries or stored procedures that have...

http://support.microsoft.com/default.aspx?scid=kb;en-us;319942
This article describes the following configuration settings and considerations for their use: Affinity Mask Lightweight Pooling Max Async IO Max Worker Threads Memory Priority Boost Set Working Set Size SQL Server can obtain a very high level of...

Support WebCast

http://support.microsoft.com/default.aspx?scid=kb;en-us;324885
Session Summary During this session, we will show a practical approach to analyze performance tuning problems that involve Microsoft SQL Server lock monitoring data. We will briefly discuss sp_who2, sysprocesses, and syslocks. You should have a firm...

SQL Server 2000 Performance Tuning Books

1. Microsoft SQL Server 2000 Performance Tuning Technical Reference
2. The SQL Server 2000 Performance Optimization and Tuning Handbook

출처 : http://www.codeproject.com/database/connectionstrings.asp?print=true

Overview

Generally, one of the first steps when you are trying to work with databases is open it. You can find several types of those, and each have a different mode of connection. When you try to connect with your database sometimes, you don't know the correct connection string that you must use. It is for that I wrote this article. I wanted to compile the connection strings to the majority of known databases...

ODBC DSN Less Connection

ODBC Driver for dBASE

strConnection = _T("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;"
                   "Dbq=c:\\DatabasePath;");

Note: You must specify the filename in the SQL statement... For example:

CString strQuery = _T("Select Name, Address From Clients.dbf");

ODBC Driver for Excel

strConnection = _T("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"
    bq=C:\\DatabasePath\\DBSpreadSheet.xls;DefaultDir=c:\\databasepath;");

ODBC Driver for Text

strConnection = _T("Driver={Microsoft Text Driver (*.txt; *.csv)};"
        "Dbq=C:\\DatabasePath\\;Extensions=asc,csv,tab,txt;");

If you are using tab delimited files, you must create the schema.ini file, and you must inform the Format=TabDelimited option in your connection string.

Note: You must specify the filename in the SQL statement... For example:

CString strQuery = _T("Select Name, Address From Clients.csv");

Visual FoxPro

If you are using a database container, the connection string is the following:

strConnection = _T("Driver={Microsoft Visual Foxpro Driver};UID=;"
    ourceType=DBC;SourceDB=C:\\DatabasePath\\MyDatabase.dbc;Exclusive=No");

If you are working without a database container, you must change the SourceType parameter by DBF as in the following connection string:

strConnection = _T("Driver={Microsoft Visual Foxpro Driver};UID=;"
    "SourceType=DBF;SourceDB=C:\\DatabasePath\\MyDatabase.dbc;Exclusive=No");

ODBC Driver for Access

strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=c:\\DatabasePath\\dbaccess.mdb;Uid=;Pwd=;");

If you are using a Workgroup (System database): you need to inform the SystemDB Path, the User Name and its password. For that, you have two solutions: inform the user and password in the connection string or in the moment of the open operation. For example:

strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=C:\\VC Projects\\ADO\\Samples\\AdoTest\\dbTestSecurity.mdb;"
        "SystemDB=C:\\Program Files\\Microsoft Office\\Office\\SYSTEM.mdw;"
        "Uid=Carlos Antollini;Pwd=carlos");

or may be:

strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=C:\\VC Projects\\ADO\\Samples\\AdoTest\\dbTestSecurity.mdb;"
        "SystemDB=C:\\Program Files\\Microsoft Office\\Office\\SYSTEM.mdw;");
if(pDB.Open(strConnection, "DatabaseUser", "DatabasePass"))
{
    DoSomething();
    pDB.Close();
}

If you want to open in Exclusive mode:

strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=c:\\DatabasePath\dbaccess.mdb;Exclusive=1;");

ODBC Driver for SQL Server

For Standard security:

strConnection = _T("Driver={SQL Server};Server=MyServerName;"
        "Trusted_Connection=no;"
        "Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");

For Trusted Connection security (Microsoft Windows NT integrated security):

strConnection = _T("Driver={SQL Server};Server=MyServerName;"
     "Database=myDatabaseName;Uid=;Pwd=;");

Also, you can use the parameter Trusted_Connection that indicates that you are using the Microsoft Windows NT Authentication Mode to authorize user access to the SQL Server database. For example:

strConnection = _T("Driver={SQL Server};Server=MyServerName;"
    "Database=MyDatabaseName;Trusted_Connection=yes;");

If the SQL Server is running in the same computer, you can replace the name of the server by the word (local) like in the following sample:

strConnection = _T("Driver={SQL Server};Server=(local);"
        "Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");

If you want to connect with a remote SQL Server, you must inform the address, the port, and the Network Library to use:

The Address parameter must be an IP address and must include the port. The Network parameter can be one of the following:

• dbnmpntw Win32 Named Pipes
• dbmssocn Win32 Winsock TCP/IP
• dbmsspxn Win32 SPX/IPX
• dbmsvinn Win32 Banyan Vines
• dbmsrpcn Win32 Multi-Protocol (Windows RPC)

For more information, see Q238949.

strConnection = _T("Driver={SQL Server};Server=130.120.110.001;"
     "Address=130.120.110.001,1052;Network=dbmssocn;Database=MyDatabaseName;"
     "Uid=myUsername;Pwd=myPassword;");

ODBC Driver for Oracle

For the current Oracle ODBC driver from Microsoft:

strConnect = _T("Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;"
        "Uid=MyUsername;Pwd=MyPassword;");

For the older Oracle ODBC driver from Microsoft:

strConnect = _T("Driver={Microsoft ODBC Driver for Oracle};"
     "ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;");

ODBC Driver for MySQL

If you want to connect to a local database, you can use a connection string like the following:

strConnect = _T("Driver={MySQL ODBC 3.51 Driver};Server=localhost;"
     "Database=MyDatabase;User=MyUserName;Password=MyPassword;Option=4;");

If you want to connect with a remote database, you need to specify the name of the server or its IP in the Server parameter. If the Port is distinct to 3306 (default port), you must specify it.

strConnect = _T("Driver={mySQL ODBC 3.51 Driver};Server=MyRemoteHost;"
     "Port=3306;Option=4;Database=MyDatabase;Uid=MyUsername;Pwd=MyPassword;");

The parameter Option can be one or more of the following values:

• 1 - The client can't handle that MyODBC returns the real width of a column.
• 2 - The client can't handle that MySQL returns the true value of affected rows. If this flag is set then MySQL returns 'found rows' instead. One must have MySQL 3.21.14 or newer to get this to work.
• 4 - Make a debug log in c:\myodbc.log. This is the same as putting MYSQL_DEBUG=d:t:O,c::\myodbc.log in AUTOEXEC.BAT.
• 8 - Don't set any packet limit for results and parameters.
• 16 - Don't prompt for questions even if driver would like to prompt.
• 32 - Enable or disable the dynamic cursor support. This is not allowed in MyODBC 2.50.
• 64 - Ignore use of database name in 'database.table.column'.
• 128 - Force use of ODBC manager cursors (experimental).
• 256 - Disable the use of extended fetch (experimental).
• 512 - Pad CHAR fields to full column length.
• 1024 - SQLDescribeCol() will return fully qualified column names.
• 2048 - Use the compressed server/client protocol.
• 4096 - Tell server to ignore space after function name and before '(' (needed by PowerBuilder). This will make all function names keywords!
• 8192 - Connect with named pipes to a MySQLd server running on NT.
• 16384 - Change LONGLONG columns to INT columns (some applications can't handle LONGLONG).
• 32768 - Return 'user' as Table_qualifier and Table_owner from SQLTables (experimental).
• 65536 - Read parameters from the client and ODBC groups from my.cnf.
• 131072 - Add some extra safety checks (should not be needed but...).

If you want to have multiple options, you should add the above flags! For example: 16 + 1024 = 1030 and use Option= 1030;.

For more information, go to MyODBC Reference Manual.

ODBC Driver for AS400

strConnect = _T("Driver={Client Access ODBC Driver (32-bit)};System=myAS400;"
      "Uid=myUsername;Pwd=myPassword;");

ODBC Driver for SyBase

strConnect = _T("Driver={Sybase System 10};Srvr=MyServerName;Uid=MyUsername;"
       "Pwd=myPassword;");

ODBC Driver for Sybase SQL AnyWhere

strConnect = _T("ODBC;Driver=Sybase SQL Anywhere 5.0;"
    "DefaultDir=C:\\DatabasePath\;Dbf=C:\\SqlAnyWhere50\\MyDatabase.db;"
    "Uid=MyUsername;Pwd=MyPassword;Dsn=\"\";");

DSN Connection

ODBC DSN

strConnect = _T("DSN=MyDSN;Uid=MyUsername;Pwd=MyPassword;");

OLE DB Provider

OLE DB Provider for SQL Server

For Standard security:

strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
        "Initial Catalog=MyDatabaseName;"
        "User Id=MyUsername;Password=MyPassword;");

For Trusted Connection security (Microsoft Windows NT integrated security):

strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
        "Initial Catalog=MyDatabaseName;"
        "Integrated Security=SSPI;");

If you want to connect to a "Named Instance" (SQL Server 2000), you must to specify Data Source=Servere Name\Instance Name like in the following example:

strConnect = _T("Provider=sqloledb;Data Source=MyServerName\MyInstanceName;"
    "Initial Catalog=MyDatabaseName;User Id=MyUsername;Password=MyPassword;");

If you want to connect with a SQL Server running on the same computer, you must specify the keyword (local) in the Data Source like in the following example:

strConnect = _T("Provider=sqloledb;Data Source=(local);"
        "Initial Catalog=myDatabaseName;"
        "User ID=myUsername;Password=myPassword;");

To connect to SQL Server running on a remote computer (via an IP address):

strConnect = _T("Provider=sqloledb;Network Library=DBMSSOCN;"
        "Data Source=130.120.110.001,1433;"
        "Initial Catalog=MyDatabaseName;User ID=MyUsername;"
        "Password=MyPassword;");

OLE DB Provider for MySQL (By Todd Smith)

strConnection = _T("Provider=MySQLProv;Data Source=test");

Where test is the name of MySQL database. Also, you can replace the name of the database by the following connection string: server=localhost;DB=test.

OLE DB Provider for AS400

strConnect = _T("Provider=IBMDA400;Data source=myAS400;User Id=myUsername;"
     "Password=myPassword;");

For more information, see: Using the OLE DB Provider for AS/400 and VSAM.

OLE DB Provider for Active Directory

strConnect = _T("Provider=ADSDSOObject;User Id=myUsername;Password=myPassword;");

For more information, see: Microsoft OLE DB Provider for Microsoft Active Directory Service.

OLE DB Provider for DB2

If you are using a TCP/IP connection:

strConnect = _T("Provider=DB2OLEDB;Network Transport Library=TCPIP;"
        "Network Address=130.120.110.001;"
        "Initial Catalog=MyCatalog;Package Collection=MyPackageCollection;"
        "Default Schema=MySchema;User ID=MyUsername;Password=MyPassword;");

If you are using APPC connection:

strConnect = _T("Provider=DB2OLEDB;APPC Local LU Alias=MyLocalLUAlias;"
        "APPC Remote LU Alias=MyRemoteLUAlias;Initial Catalog=MyCatalog;"
        "Package Collection=MyPackageCollection;Default Schema=MySchema;"
        "User ID=MyUsername;Password=MyPassword;");

For more information, see: Using the OLE DB Provider for DB2.

OLE DB Provider for Microsoft Jet

• Connecting to an Access file using the JET OLE DB Provider:

  Using Standard security:

  strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
          "Data Source=C:\\DatabasePath\\MmDatabase.mdb;"
          "User Id=admin;Password=;");

  If you are using a Workgroup (System database):

  strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
          "Data Source=C:\\DataBasePath\\mydb.mdb;"
          "Jet OLEDB:System Database=MySystem.mdw;");
  pRs.Open(strConnect, "MyUsername", "MyPassword");

• Connecting to an Excel Spreadsheet using the JET OLE DB Provider:

  strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
          "Data Source=C:\\DatabasePath\\DBSpreadSheet.xls;"
          "Extended Properties=\"\"Excel 8.0;HDR=Yes;\"\";");

  Note: If "HDR=Yes", the provider will not include the first row of the selection into the recordset. If "HDR=No", the provider will include the first row of the cell range (or named ranged) into the recordset.

  For more information, see: Q278973.

• Connecting to a Text file using the JET OLE DB Provider:

  strConnect = 
          _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DatabasePath\\;"
          "Extended Properties=\"\"text;"
          "HDR=Yes;FMT=Delimited;\"\";");

  Note: You must specify the filename in the SQL statement... For example:

  CString strQuery = _T("Select Name, Address From Clients.txt");

  For more information, see: Q262537.

• Connecting to an Outlook 2000 personal mail box using the JET OLE DB Provider: (By J. Cardinal)

  strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;Outlook 9.0;"
        "MAPILEVEL=;DATABASE=C:\\Temp\\;")

  Replace c:\temp with any temporary folder. It will create a schema file in that folder when you open it which shows all the fields available. Blank MAPILEVEL indicates top level of folders).

• Connecting to an Exchange mail box through JET: (By J. Cardinal)

  strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;Exchange 4.0;"
        "MAPILEVEL=Mailbox - Pat Smith|;DATABASE=C:\\Temp\\;")

  You must replace c:\temp with any temporary folder.

  Replace Pat Smith with the name of the mail box and you must keep vertical pipe character | to indicate top level of folders. Place sub folder after vertical pipe if accessing specific folder.

  Note: you can enter queries against the mail store just like a database... For example:

  CString strQuery = _T("SQL "SELECT Contacts.* FROM Contacts;");

  For more information, see: The Jet 4.0 Exchange/Outlook IISAM.

OLE DB Provider for ODBC Databases

If you want to connect with a Microsoft Access database:

strConnect = _T("Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=c:\\DatabasePath\\MyDatabase.mdb;Uid=MyUsername;Pwd=MyPassword;");

If you want to connect with a SQL Server database:

strConnect = _T("Provider=MSDASQL;Driver={SQL Server};Server=MyServerName;"
        "Database=MyDatabaseName;Uid=MyUsername;Pwd=MyPassword;");

If you want to use DSN:

strConnect = _T("Provider=MSDASQL;PersistSecurityInfo=False;"
        "Trusted_Connection=Yes;"
        "Data Source=MyDSN;catalog=MyDatabase;");

For more information, see: Microsoft OLE DB Provider for ODBC.

OLE DB Provider for OLAP

Microsoft® OLE DB for Online Analytical Processing (OLAP) is a set of objects and interfaces that extends the ability of OLE DB to provide access to multidimensional data stores.

strConnect = _T("Provider=MSOLAP;Data Source=MyOLAPServerName;"
     "Initial Catalog=MyOLAPDatabaseName;");

Connection using HTTP:

This feature enables a client application to connect to an Analysis server through Microsoft® Internet Information Services (IIS) by specifying a URL in the Data Source property in the client application's connection string. This connection method allows PivotTable® Service to tunnel through firewalls or proxy servers to the Analysis server. A special Active Server Pages (ASP) page, Msolap.asp, enables the connection through IIS. The directory in which this file resides must be included as part of the URL when connecting to the server (for example, http://www.myserver.com/myolap/).

Using a URL

strConnect = _T("Provider=MSOLAP;Data Source=http://MyOLAPServerName/;"
     "Initial Catalog=MyOLAPDatabaseName;");

Using SSL

strConnect = _T("Provider=MSOLAP;Data Source=https://MyOLAPServerName/;"
     "Initial Catalog=MyOLAPDatabaseName;");

For more information, see: OLE DB for OLAP, Connecting Using HTTP.

OLE DB Provider for Oracle

OLE DB Provider for Oracle (from Microsoft)

The Microsoft OLE DB Provider for Oracle allows ADO to access Oracle databases.

strConnect = _T("Provider=MSDAORA;Data Source=MyOracleDB;User Id=myUsername;"
     "Password=myPassword;");

For more information, see: Microsoft OLE DB Provider for Oracle.

OLE DB Provider for Oracle (from Oracle).

For Standard security:

strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;"
        "User Id=myUsername;Password=myPassword;");

For a Trusted connection:

• OS Authenticated connect setting user ID to "/":

  strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;"
     "User Id=/;Password=;");

• OS Authenticated connect using OSAuthent:

  strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;")

  Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name.

For more information, see: Oracle Provider for OLE DB Developer's Guide.

OLE DB Provider for Visual FoxPro

strConnect = _T("Provider=vfpoledb;"
     "Data Source=C:\\DatabasePath\\MyDatabase.dbc;");

For more information, see: Microsoft OLE DB Provider for Visual FoxPro.

OLE DB Provider for Index Server (By Chris Maunder)

strConnect = _T("provider=msidxs;Data Source=MyCatalog;");

For more information, see: Microsoft OLE DB Provider for Microsoft Indexing Service.

OLE DB Data Link Connections

Data Link File - UDL

strConnection = _T("File Name=c:\\DataBasePath\\DatabaseName.udl;");

If you want to create a Data Link File, you can make a new empty text file, change its extension by .udl, then double click over the file, and the operating system calls for you the Data Link app.

About Carlos Antollini

Goetz Graefe
Microsoft, Redmond, WA 98052-6399
GoetzG@Microsoft.com

Abstract
Microsoft SQL Server was successful for many years for transaction processing and decision support workloads with neither merge join nor hash join, relying entirely on nested loops and index nested loops join. How much difference do additional join algorithms really make, and how much system performance do they actually add? In a pure OLTP workload that requires only record-to-record navigation, intuition agrees that index nested loops join is sufficient. For a DSS workload, however, the question is much more complex. To answer this question, we have analyzed TPC-D query performance using an internal build of SQL Server with merge-join and hash-join enabled and disabled. It shows that merge join and hash join are both required to achieve the best performance for decision support workloads.

Tony Patton ( TechRepublic ) 2006/06/09


.NET 애플리케이션 코드에서 예외를 처리하는 것은 매우 간단하고 try/catch 코드 블록을 이용한 직관적인 절차이다. 데이터베이스 부분에서도 역시 예외를 모니터 할 수 있는데, 이 기사에서는 SQL 서버와 T-SQL을 이용한 데이터베이스 코드에서 에러를 처리하는 방법을 살펴본다.

개발자들은 예외(exception)를 처리하는데 친절하게도 많은 노력을 기울이기 때문에 사용자들은 알 수 없는 시스템 에러 메시지를 걱정할 필요가 없다. 이런 이유 때문에, 예외 처리는 모든 .NET 애플리케이션의 표준 항목이다. try/catch 블록은 개발자가 예외를 잡아내고 그 시점에서의 애플리케이션 실행을 컨트롤할 수 있도록 해준다. 많은 에러들은 데이터베이스 처리 중에 발생하지만 많은 개발자들은 데이터베이스 부분에서 생기는 에러를 처리하는 것을 알지 못한다. 이 기사에서는 SQL 서버와 T-SQL을 이용한 데이터베이스 코드에서 에러를 처리하는 방법을 알아보자.

T-SQL에서 발생한 에러 처리하기
SQL 서버가 제공하는 T-SQL 언어는 저장 프로시저, 함수 등에서 발생할 수 있는 치명적이지 않은 에러를 쉽게 처리할 수 있게 해주지만, 모든 에러가 쉽게 처리할 수 있게 되는 것은 아니다. 사실, 에러에는 치명적인 에러와 치명적이지 않은 에러가 있는데, 치명적이지 않은 에러와는 달리 치명적인 에러는 실행이 중단된다.

트랜잭션
변경사항이 모두 완료돼 모든 것이 정상인 것을 확실히 하기 위해서는 데이터베이스 코드에 트랜잭션을 사용해야만 한다. SQL 서버 온라인 도움말은 selects, inserts, updates 혹은 deletes와 같은 명령행의 연속으로 이루어진 논리적 작업 단위라고 설명한다. 만약 트랜잭션동안 에러가 없다면 트랜잭션의 모든 변경 사항은 데이터베이스에 적용될 것이며, 만약 에러가 발생하면, 어떤 변경사항도 데이터베이스에 적용되지 않는다.

트랜잭션은 BEGIN TRANSACTION과 END TRANSACTION 명령 사이에 포함된다. ROLLBACK TRANSACTION 명령은 모든 변경사항을 취소하도록 하여, 어떤 변경사항도 이루어지지 않게 한다. COMMIT TRANSACTION 명령은 변경사항을 데이터베이스에 반영한다. 이제, T-SQL에서 에러를 처리하는 방법을 알아보자.

@@Error
@@Error 함수는 T-SQL을 만들 때 에러를 처리하도록 해준다. 이 함수는 시스템의 에러 코드를 돌려준다. 만약 에러가 없으면 0을 리턴 한다. @@Error 함수는 각 T-SQL 명령이 실행되면 초기화되기 때문에, 명령을 호출한 직후 바로 불러야한다.

RAISERROR
RAISERROR 명령은 커스텀 에러 메시지를 만들거나 sysmessages 테이블에 이미 있는 메시지를 사용할 수 있게 해준다. 이 구문의 문법은 온라인으로 볼 수 있지만, 가장 기본적인 형태는 에러의 심각도, 상태와 함께 메시지나 메시지 ID를 포함한다. 상태는 SQL 서버에서 사용하지 않기 때문에 임의의 숫자를 이용해 처리한다. 심각도는 에러의 심각성을 나타내는데 0~18은 사용자가 사용할 수 있으며 19~25는 관리자를 위해 예약돼 있다.

예제 1. 이 예제 저장 프로시저는 Northwind 데이터베이스의 개별 레코드를 업데이트하는데 이 기능들을 사용한다. 에러가 없을 경우 전화 번호 칼럼의 값을 프로시저를 통해 수정한다. 만약 에러가 발생하면 음수를, 에러가 없으면 양수를 돌려주는 리턴 값을 사용한다.

저장 프로시저의 리턴 값 사용하기
.NET 코드에 저장 프로시저의 리턴 값을 사용할 수 있다. SqlCommand 객체는 저장된 리턴 값뿐만 아니라 쉽게 프로시저에 파라미터를 넘길 수 있도록 해준다. 파라미터의 Direction 속성은 저장 프로시저 호출을 통한 리턴 값을 얻는데 사용되는데, 이 속성은 InputOutput과 Output이 될 수 있다. 다음 예제에서는 상태 값을 받기 위해 Output을 사용하였다.

다음 예제는 Northwind 데이터베이스의 customers 테이블의 특정 레코드에 새로운 값을 저장하는 간단한 ASP.NET 페이지이다. id 값은 실제로는 hidden 필드로 저장된다. form을 통해 값을 쉽게 넘길 수 있지만, 데모를 위해 예제와 같이 했다. text 필드에 입력된 값은 phone 필드를 업데이트 하는데 사용된다.

파라미터는 SqlCommand 객체에 추가할 수 있으며 저장 프로시저의 파라미터 값과 정확히 일치해야한다. 이 작업은 SqlCommand 객체의 ExecuteNonQuery를 통해 실행된다. 이것이 실행되면, 파라미터를 통해 리턴 값을 받을 수 있다.

다음 예제는 리턴 값을 검사 하고(-1은 문제가 있음을 뜻한다) Label 컨트롤에 메시지를 표시한다. 추가로 데이터베이스 처리 중에 발생할 수 있는 치명적인 에러를 잡기 위해 try/catch 블록이 사용되었다. 예제 2. 예제 3은 같은 작업을 하는 VB.NET 코드이다

필요한 모든 것 제공
.NET 애플리케이션 코드에서 예외를 처리하는 것은 간단하고 try/catch 코드 블록을 이용한 직관적인 절차이다. 하지만, 데이터베이스 부분에서도 역시 예외를 모니터 할 수 있는데, SQL 서버의 T-SQL은 코드를 실행하면서 확인할 수 있는 모든 것들을 제공한다.@


http://www.zdnet.co.kr/builder/dev/etc/ ··· 2C00.htm

개발자를 위한 튜닝 가이드
쿼리디자인

쿼리 디자인

	번호 수칙 체크 1 SELECT는 필요한 결과값만을 요구하는가? 2 적절한 WHERE조건을 사용하는가? 3 COUNT(컬럼명) 대신 COUNT(*)을 사용하는가? 4 커서 및 임시 테이블의 내용을 최대한 자제하는가? 5 VIEW의 총 사용을 줄였는가? 6 저장 프로시저를 사용하는가? 7 저장 프로시저를 적절하게 리컴 파일 하며 사용하는가 ? 8 작명 된 저장 프로시저 SP외의 접두어를 사용하는가 ? 9 모든 개체의 소유자는 DBO로 지정하며 생성했는가 ? 10 데드락이 발생하는 부분을 라이브락 형태로 변경했는가? 11 SET NOCOUNT ON을 사용하는가? 12 실무 사례: 저장 프로시저 관리 방법

스티브 맥코넬이 이런 말을 했습니다.
뛰어난 디자이너는 습득한 지식을 사용하지 않는 것과 그 지식을 처음부터 확보하지 못한 것을 동일하게 봅니다.
이말 뜻을 다음과 같이 해석하고 싶습니다. 여러분은 쿼리 분석기의 기능들이 어떤 것이 있고, 단축키가 메뉴우측에 작게 표시되어 있다는 것을 대부분 알고 있습니다. 하지만 잘 사용하지는 않고 있을 것입니다. 라고 말입니다. 그래서 먼저 단축키와 그 사용법에 대해 안내하는 시간을 우선 가지도록 하겠습니다.
다음을 실습해보고 자세한 것은 표를 참조합시다


-- CTRL + E , F5 -- 실행하기 ---------------------------------------------------------------- use pubs go select * from titles -- CTRL + T => 결과 Text로 보기 select * from titles -- CTRL + D => 결과 Text로 보기 select * from titles -- CTRL + K => 실행계획 보기 select * from titles -- F8 => 개체브라우저 보이기/감추기 -- CTRL + R => 결과창 보이기/감추기 -- 그외 CTRL + C , CTRL + V , CTRL + X -- CTRL + SHIFT + C => 주석달기 select * from titles

다음의 표를 참조합시다.

	없음	Shift +	Ctrl+	Alt+	Shift+Ctrl+
A			전체 선택
B			중간 구분선 선택
C			복사		주석 달기
D			표 형태로 결과 표시	데이터베이스 선택
E			실행
F			찾기		파일로 결과 저장
G
H			교체
I			인덱스 튜닝마법사
J
K			실행 계획 보기
L			예상 실행 계획 보기		선택 내용을 소문자로
M
N			새 쿼리 윈도우
O			연결
P
Q
R			결과창 보이기/감추기		주석제거
S			저장
T			텍스트로 결과 표시
U					선택 내용을 대문자로
U			선택 내용을 대문자로
V			붙여넣기
W
X			자르기
Y			다시하기
Z			취소
F1	도움말	선택 내용을 도움말로 보기
F8	객체 브라우저보이기감추기

주요 단축키 사용 안내 입니다.

수칙 1. SELECT는 필요한 결과값만을 요구하는가?
select title , price from titles where title_id = 'BU1032'
Select 하는 내용도 필요한 항목만을 가지고 오도록 되어 있어서 리소스가 전혀 낭비되지 않고 있습니다.

select title , price from titles
필요한 칼럼을 가져오기는 하지만 불필요한 전체 행(Row)들을 가져오고 있습니다.

select * from titles
불필요한 칼럼정보, 행(Row) 데이터를 가져오고 있습니다.


따라 하기 - 3개의 쿼리를 한번에 실행하기

1. 3개의 쿼리를 한 Session에서 실행하여 결과3개를 동시에 살펴봅니다.




2. 결과 값으로 출력되는 데이터량의 차이를 확인합니다. 어느 쿼리가 가장 간결한 결과를 반환합니까?
( 반드시 꼭 필요한 결과만 반환하게 하는 것이 좋습니다.
select title , price from titles where title_id = 'BU1032' 가 적절합니다.)


수칙 2. 적절한 WHERE 조건을 사용하는가?

인덱스란 데이터를 빨리 찾기 위해서 사용됩니다. 인덱스가 없다면 특정데이터를 찾기 위해서 모든 데이터페이지를 검색(Table Scan)해야만 합니다 그에 비해 인덱스가 존재하고 그 인덱스가 사용되는 것이 효과적이라면 SQL서버는 해당 인덱스 페이지를 사용하여 쉽게 데이터를 가져올 수 있는데 이를 인덱스 검색(Index Seek)이라 합니다.
그러나 이렇게 인덱스가 있더라도 이를 사용 불가능하게 하는 나쁜 쿼리가 있으니 이는 검색조건에서 불필요하게 칼럼이 변형된 경우입니다. 다음의 여러 나쁜 예를 좋은 예와 비교해 봅시다.
SARG(Search Argument)란 쿼리가 반환하는 결과를 제한하기 위하여 옵티마이저가 인덱스와 결합해서 사용할 수 있는 쿼리 내의 조건절을 말하는데 다음의 형태를 가집니다.

컬럼 연산자/변수

옵티마이저가 쓸모 있게 변환하는 것은 CTRL+K 실행 계획 상부 표시에서 관찰할 수 있습니다.
set showplan_all on select * from authors where au_lname like 'Ma%' -- OBJECT:([pubs].[dbo].[authors].[aunmind]), SEEK:([authors].[au_lname] >= 'Ma' AND [authors].[au_lname] < 'MB'), WHERE:(like([authors].[au_lname], 'Ma%', NULL)) ORDERED FORWARD -- set showplan_all off

따라 하기

1. 인덱스 찾기(Index Seek)를 확인합니다.



2. 다음과 같이 약간의 조건절(where) 변형만으로 인덱스페이지가 사용되지 않음을 확인합니다.



3.그렇다면 조건절(where) 변형하고 싶을 땐 어떻게 해야 할까요?



4. 다음 예제도 복습해 봅시다.



5. 항상 실행 계획을 참조하여 재차 쿼리를 확인해야 합니다.




수칙 3. COUNT(컬럼명) 대신 COUNT(*)을 사용하는가?

COUNT(*) 와 COUNT(컬럼명)의 차이는 중요합니다. COUNT 하는 해당 테이블 컬럼에 NULL 값을 포함하고 있다면 이 두 예제는 서로 다른 결과를 반환합니다. COUNT(컬럼명)은 그룹에 포함된 각 행을 평가하여 NULL이 아닌 값의 개수를 반환합니다. COUNT(*)는 NULL 값과 중복된 값을 포함한 그룹의 항목 개수를 반환합니다.
일반적으로, COUNT(컬럼명)을 사용하여 특정한 컬럼의 행 개수를 세는 것보다 COUNT(*)을 사용하여 옵티마이저가 행의 개수를 반환하는 최상의 방법을 선택하도록 해주는 것을 더 선호하는 방식이다.



[참고] NULL을 처리하는 방법

use pubs go -- 돈 받고 파는 책을 출력하세요 select * from titles where price is not null -- 비매품인 책을 출력하세요 select * from titles where price is null -- 비매품책을 제외한 모든 책의 평균가격? select avg(price) from titles -- 비매품책을 0원으로 두고 계산한 평균가격? select avg(isnull(price,0)) from titles

[유용한 관용구]
칼럼의 중복 행의 수를 찾아봅시다

use pubs go -- 중복 칼럼이 각각 몇 개 항목인지를 찾아보자 -- type별로 몇 개의 책이 있을까? select type,count(*) as [중복 행의 수] from titles group by type having count(*)> 1


수칙 4. 커서 및 임시 테이블의 내용을 최대한 자제하는가?

결론부터 말씀 드리자면 커서보다는 임시테이블이 임시테이블보다는 테이블 변수를 사용하는 것이 성능에 보탬이 됩니다. 단 SQL2000에서만 테이블 변수가 가능합니다.
커서는 내부적으로 임시 테이블을 사용하기 때문에 임시테이블을 쓴다고 부하가 더 발생하진 않습니다. 오히려 커서의 부가적 기능 때문에 서버 자원을 더 낭비하게 됩니다. (커서로 할 수 있는 건 임시테이블이나 테이블 변수로도 모두 처리가 가능합니다.)


따라 하기 - 다음은 테이블 변수를 사용하여 기존 커서를 대체하는 것을 구현했습니다

1. 훌륭하게 커서를 대신하는 문장입니다.



CTRL + K로 확인하면 테이블 변수로 사용할 경우 실제 테이블에 잠금을 전혀 걸지않는 것을 알 수 있습니다(중요) 그와 반대로 커서를 사용할 경우 프로시저 시작부터 끝까지 지속적으로 사용 부분을 계속해서 잠그고 있어서 다른 작업들이 대기해야 되는 문제가 생깁니다.


수칙 5. VIEW의 총사용을 줄였는가?

VIEW는 보안과 편리성에 관련된 이슈를 다루는 데 있어 최고입니다,
그러나, 일반적으로 보안상에 이슈를 제외한 경우에는 불필요한 부하가 가중될 수 있고 많은 경우에 더 불필요한 데이터를 반환합니다 예를 들면 VIEW에서 10개를 가져오고 거기에 WHERE 조건을 붙여서 7개만 가져오는 경우가 그렇습니다.


select lastname,firstname from employees VS select * from EmployeesView 중간 단계가 있는 쪽이 효율이 떨어집니다.


수칙 6. 저장 프로시저를 사용하는가?

저장 프로시저는 복잡한 SQL문을 단순화 시켜주고, 보안 문제를 해결해주며 더 나아가 빠른 성능에 매개변수,출력매개변수,리턴 값을 사용할 수 있습니다.

제작

1. 구문분석 2. 표준화 3. 보안 점검(프로시저 생성권한) 4. 저장(syscomments)

첫 번째 실행 시

1. 보안 점검(프로시저 실행 권한) 2. 최적화 3. 컴파일과 이에 따른 실행계획을 캐쉬에 저장 4. 실행

반복해서 실행 시

1. 캐쉬에 실행 계획 있을 때는 그대로 실행 2. 캐쉬에 실행 계획이 없을 때는 첫 번째로 저장 프로시저 실행하는 것과 동일

쿼리는 한번만 실행할 때는 일반 SQL이 훨씬 간단합니다. 그러나 반복적으로 실행되면 저장 프로시저가 월등히 빠르고 편리합니다.


수칙 7. 저장 프로시저를 적절하게 리컴 파일 하는가?

데이터가 변화하면(인덱스를 추가하거나 인덱스된 열의 데이터를 변경하는 등의 작업 수행 시) 그에 걸맞게 실행계획도 변화해 갑니다. 그에 대처하기 위해서 다음과 같은 리컴파일 방법를 제공합니다.
저장 프로시저 리컴 파일 모드에는 다음의 3가지가 있습니다.





는 SQL SERVER가 이 저장 프로시저의 계획을 캐시하지 않기 때문에 이 저장 프로시저가 실행 할 때 마다 다시 컴파일 됩니다(실행 속도가 느려짐).



는 지금 이순간만 리컴파일 하고 다시 저장 프로시저 실행하면 예전 실행 계획대로 작동하는 것입니다. 제공하는 매개 변수가 불규칙하거나 저장 프로시저를 만든 다음 데이터가 많이 변경되었을 경우 이 옵션을 사용합니다.



는 저장 프로시저가 다음에 실행될 때 첫 실행처럼 컴파일되고 실행되도록 하는 것입니다.

[문서화되지 않은 DBCC 명령어]
-- pubs 데이터베이스의 모든 저장 프로시저를 재컴파일 해보자 select db_id('pubs') dbcc flushprocindb(5) -- 모든 인덱스를 재구축한다 -- 관리자가 사용할 경우 엄청난 시간이 소요될 수 있습니다 dbcc dbreindexall('pubs')


수칙 8. 저장 프로시저 작명 시 SP외의 접두어를 사용한다

시스템 저장 프로시저는 master 데이터베이스내에서 sp_라는 접두어로 시작하는 것이 좋으며 모든 데이터베이스에서 실행될 수 있습니다. 각 사용자 데이터베이스에서는 다른 접두어를 사용하는 것이 보기에도 좋고 알아보기에도 수월합니다.
또한 시스템 저장 프로시저는 어느 데이터베이스에서 수행하건 해당 데이터베이스의 내용을 참조합니다.


따라 하기

일반sp_ 저장 프로시저를 시스템sp_ 저장 프로시저로 만들어 봅니다.
1. 사용자 정의 저장 프로시저는 master데이터베이스에 존재하더라도 master내용만 참고합니다.



2. 그러나 다음과 같이 시스템 저장 프로시저화 한다면



3. 부연하자면 모든 데이터베이스에서 사용하는 프로시저의 경우 sp_로 시작하게 작성한후 sp_MS_marksystemobject로 시스템 프로 시저화 작업을 하는게 필요합니다. 이 내용은 엄격하게 구분되서 실행되는 것이 혼란을 줄일 수 있습니다.



수칙 9. 모든 개체의 소유자는 DBO 이다

소유자가 다르면 복잡한 소유권 체인문제가 발생합니다.



lucia가 테이블의 소유자 입니다. lucia는 뷰를 만들었는데 maria에게 뷰를 볼 수 있게 했습니다. maria는 이를 Pierre가 볼 수 있게 했는데 Pierre는 Maria가 만든 뷰를 select권한을 받았음에도 불구하고 실행이 안됩니다. 이는 소유권 체인이 중간에 분실 됐기 때문입니다. 불필요한 이런 시스템은 시스템의 성능저하를 가져다 줍니다. 모든 소유자는 dbo로 통일하는 것을 권장합니다.


따라 하기 - 소유자를 dbo로 바꿔보자

1. 소유자를 dbo로 바꿀 때는 다음의 저장 프로시저를 사용하면 됩니다.



2. 추가로 시스템테이블을 업데이트하는 방법을 통해 데이터베이스 차원에서 소유자를 바꾸는 방법도 있으며 커서를 사용하는 방법도 존재합니다.


수칙 10. 데드락이 발생하는 부분을 라이브락 형태로 변경했는가?

데드락이란 라이브락과 반대되는 개념입니다. 둘 이상의 트랜잭션이 서로가 실행해야 될 내용을 이미 잠그고 있어 마치 교차로에서 서로 엉켜 꼼짝할 수 없는 상황을 의미 합니다. 이를 해결하기 위한 SQL서버의 노력은 한쪽을 일방적으로 취소 시키는 것인데 이는 시스템의 성능저하로 나타납니다. 이를 해결하기 위한 가장 좋은 방법은 일방통행 방식으로 변경하는 것입니다. 이것이 라이브락 입니다.


따라 하기

1. 우선 준비를 위해 테이블을 만들고 데이터를 넣습니다.




2. 창을 두 개 열어서 동시에 실행합니다. CTRL+TAB으로 잽싸게 창을 바꿔서 실행해 봅니다.



3. 위의 데드락의 가장 바른 해결방법은 순차적인 라이브락 형태로 변경하는 것입니다.




수칙 11. SET NOCOUNT ON을 사용하는가?

불필요한 메시지가 네트워크 트래픽을 낭비하고 있습니다. 특히'몇 개 행이 적용 되었습니다' 같은 메시지가 그런 대표적인 예입니다.


따라 하기

1. set nocount on을 실행하고 쿼리를 실행합니다.




프로시저를 작성할 때도 set nocount on과 같은 환경설정은 먼저 실행해 두고 프로시저를 작성하면 환경이 저장된 채로 프로시저가 제작되므로 편리합니다.


수칙 12. 실무 사례: 저장 프로시저 관리 방법

저장 프로시저 관리방법





use 데이터베이스명 -- 저장 프로시저는 use 데이터베이스명 문과 같이 써두어야 어디 소속인지 명확히 파악이 가능합니다. go -- 소스 create proc dbo.저장 프로시저명 -- 소유자가 명확하게 dbo로 지정 되 있어야 성능 향상이 이뤄집니다. 컴파일 잠금 시간 대폭 감소 as begin -- 가장 바깥쪽의 begin end 및 불필요한 begin end문은 과감히 생략합니다.(소스만 길어짐) end -- 실행예제 exec 데이터베이스명.dbo.저장 프로시저명 -- 데이터베이스 이름까지 명시해야 오브젝트 참조에서 발생할 수 있는 불 명확성을 줄여줌으로 바람직합니다.

http://www.dbguide.net/tuning_guide/tuning_guide_11.jsp

한용희 | 롯데정보통신 칠성IS 사업팀

운영체제 │ 윈도우 2000, 윈도우 2003, 윈도우 XP
개발도구 │ MS SQL 서버 2005 베타 2, 비주얼 스튜디오 2005 베타 1
기초지식 │ MS SQL 서버 2000, C#
응용분야 │ MS SQL 서버 2005 관리와 개발

SQL 서버 2005가 나오면서 개인적으로 가장 크게 관심을 보인 부분이 바로 테이블 파티셔닝이다. 현재 롯데칠성음료에서도 매달 100만 건의 거래명세표 내역이 쌓이면서 이를 처리하기 위한 대용량 데이터베이스에 대해 높은 관심을 기울이고 있다. 이번 호에서는 대용량 데이터베이스를 위한 테이블 파티셔닝과 가용성을 높이기 위한 스냅샷 그리고 미러링에 대해 알아 볼 것이다.

지난 3회에 걸쳐 SQL 서버 2005의 새로운 특징들에 대해 알아봤다. 이번 호에서는 마지막으로 대용량 데이터베이스를 위한 기존의 파티션뷰를 대체하는 테이블 파티셔닝과 데이터베이스 이력 관리를 위한 스냅샷, 그리고 가용성을 높이기 위한 클러스터링에 견줄 수 있는 미러링에 대해 알아 볼 것이다.

기업의 데이터는 시간이 지날수록 점점 많아지고 있다. 분석을 위한 데이터는 점점 더 쌓여만 가고 더 이상 하나의 테이블에 이 모든 정보를 담아 두는 것이 비효율적일 때가 있다. 보통 기가나 테라 단위의 데이터를 하나의 테이블에 담아 두게 되면 테이블 유지 보수가 힘들며 성능 또한 느려지게 된다. 이러한 데이터는 대부분 과거의 데이터가 함께 있어서 그러는데, 아마 몇 년 전의 데이터는 거의 사용하지 않을 것이다. 이럴 때에는 테이블을 나누어서 최근의 데이터는 높은 성능을 내는 I/O에 담아 두고, 예전의 데이터는 비교적 낮은 성능의 저렴한 I/O 장치에 담아두는 것이 효율적일 것이다. 이럴 때 사용하는 것이 바로 파티셔닝이다.

SQL 서버 7.0/2000에서 분할된 뷰

파티셔닝을 위한 전략은 SQL 서버 7.0에서부터 지원했다. 분할된 뷰(partitioned view)를 이용하여 각각의 테이블을 UNION으로 묶어서 마치 하나의 테이블로 볼 수 있도록 했다.

CREATE VIEW Production.vTransaction AS SELECT * FROM Production.Transaction_2003_09 UNION ALL SELECT * FROM Production.Transaction_2003_10 UNION ALL SELECT * FROM Production.Transaction_2003_11

이와 같이 2003년 9월의 테이블과 2003년 10월의 테이블, 2003년 11월의 테이블을 UNION으로 결합함으로써 분할된 뷰를 만들 수 있다. 이 때 각 테이블은 파티셔닝 컬럼을 CHECK 조건을 이용하여 미리 제한해둬야 한다. 예를 들면 앞의 각 테이블에 TransactionDate라는 날짜 컬럼이 있다면 제한 조건으로 다음과 같이 한다.

CHECK ( TransactionDate between ‘2003-09-01’ AND ‘2003-09-30’)

이렇게 파티셔닝 컬럼을 정의하고 이 컬럼에 INDEX를 걸어 두면 분할된 뷰를 이용하여 테이블에 접근할 때 다른 날짜의 테이블은 읽지 않게 된다. SQL 서버 2000에서는 분할 뷰를 이용하여 데이터 갱신 작업이 효과적으로 수행하도록 지원하였으며 분산 분할된 뷰(distributed partitioned view)로까지 발전을 하여 각각의 테이블이 한 서버가 아닌 다른 서버에 있어도 가능하도록 발전했다. 하지만 분할된 뷰 방식의 파티셔닝은 여러 테이블을 하나의 뷰로 모았기 때문에 관리상 불편한 점이 많았다. 예를 들면 테이블 구조를 바꾼다거나 인덱스를 재생성하거나 변경하는 경우 각각의 테이블을 모두 반영해줘야 하기 때문이다.

SQL 서버 2005의 테이블 파티셔닝

SQL 서버 2005에서는 뷰를 통한 파티셔닝이 아닌 테이블 단위의 파티셔닝을 지원한다. 즉 하나의 테이블을 여러 조각으로 쪼개어 관리하는 것이 가능하다. 그러므로 분할된 뷰처럼 각 테이블을 따로 관리할 필요가 없다. 예를 들면 인덱스를 만드는데 있어서 하나의 테이블만 만들면 되므로 관리상 이점이 있다. 또한 성능에 있어서도 더 좋은 성능을 보여준다. 분할된 뷰에서는 각각의 테이블을 보고 나중에 합치는 방식으로 진행되었지만, 테이블 파티셔닝에서는 멀티 CPU 환경이라면 병렬처리(demand parallelism)를 이용하여 빠른 쿼리를 수행할 수도 있다. 쿼리를 컴파일하는데 있어서도 분할된 뷰에서는 테이블이 많을수록 느렸지만, 테이블 파티셔닝에서는 파티션 개수에 상관없이 빠른 속도를 보장한다.

테이블 파티셔닝은 파티셔닝 함수와 스키마를 이용하여 구현한다. 파티셔닝 함수로는 경계 영역을 구분하고 스키마로는 실제 물리적인 파일 그룹에 각 파티션을 맵핑한다.

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000)

이 예제는 myRangePF1이라는 파티션 함수를 정의하는데 있어 경계 부분을 왼쪽에 포함하는 함수를 만들고 있다. 이와 같이 실행하면 다음과 같이 4개의 파티션 영역을 정의한다.



파티션 1 2 3 4
값 col < = 1 col < = 1 and col < = 100 col < = 100 AND col < = 1,000 col > 1,000

즉, 경계를 왼쪽 부분에 포함하기 때문에 1,100,1000은 각각 왼쪽 파티션에 포함하게 된다. 만약 LEFT 대신에 RIGHT라고 쓴다면 1,100,1000은 각각 오른쪽 파티션에 포함하게 된다. 파티션 함수를 만들었으면 실제 물리적인 영역에 맵핑할 수 있는 스키마를 정의해야 한다.

CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg)

이 구문은 앞에서 정의한 파티션 함수를 바탕으로 각각 4개의 파일 그룹에 맵핑하고 있다. 따라서 이런 경우는 각각의 파티션이 별개의 물리적인 공간에 저장되게 된다. 물론 하나의 파일 그룹에 담을 수도 있다. 그럼 이제 SQL 서버 2000의 분할된 뷰와 SQL 서버 2005의 테이블 파티셔닝의 차이점에 대해 알아보자.

분할된 뷰 vs. 테이블 파티셔닝

먼저 분할된 뷰를 만들어보자. 기본적으로 SQL 서버 2005 베타 2를 설치하면 AdventureWorks에 TransactionHistory라는 큰 테이블이 존재한다. 이를 먼저 분할된 뷰로 만들기 위해 다음과 같이 여러 개의 테이블로 나누고 각각 CHECK 제약 조건을 주고 인덱스를 생성해보자. 전체 코드는 ‘이달의 디스켓’에 있다.

테이블 분할
SELECT * INTO Production.Transaction_2003_09 FROM Production.TransactionHistory WHERE TransactionDate between ‘2003-09-01’ and ‘2003-09-30’; SELECT * INTO Production.Transaction_2003_10 FROM Production.TransactionHistory WHERE TransactionDate between ‘2003-10-01’ and ‘2003-10-31’; ...



체크 제약 조건 삽입
ALTER TABLE [Production].[Transaction_2003_09] WITH CHECK ADD CONSTRAINT [CK_Transaction_2003_09] CHECK (TransactionDate between ‘2003-09-01’ AND ‘2003-09-30’); ALTER TABLE [Production].[Transaction_2003_10] WITH CHECK ADD CONSTRAINT [CK_Transaction_2003_10] CHECK ( TransactionDate between ‘2003-10-01’ AND ‘2003-10-31’); ...

인덱스 만들기
CREATE CLUSTERED INDEX [IX_Transaction_2003_09_TransactionDate] ON [Production].[Transaction_2003_09]([TransactionDate]); CREATE CLUSTERED INDEX [IX_Transaction_2003_10_TransactionDate] ON [Production].[Transaction_2003_10]([TransactionDate]); ...

뷰 만들기
CREATE VIEW Production.vTransaction AS SELECT * FROM Production.Transaction_2003_09 UNION ALL SELECT * FROM Production.Transaction_2003_10 UNION ALL ...

이제 다 만들었으면 과연 잘 만들었는지 샘플 쿼리를 실행해보자.

SELECT * FROM Production.vTransaction WHERE TransactionDate between ‘2003-09-01’ and ‘2003-10-31’;
-----------------------------------------------------------------------------------------
(20494 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Transaction_2003_10’. Scan count 1, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Transaction_2003_09’. Scan count 1, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

이 결과를 보면 예상대로 다른 테이블은 읽기가 없고 해당 테이블에서만 읽기가 일어난 것을 볼 수 있을 것이다. 그럼 실제 실행 계획을 보자. <화면 1>을 보면 다른 테이블을 모두 본 다음에 필터로 걸러서 나중에 결합(concatenation) 연산을 이용하여 합치는 것을 볼 수 있다. 앞에서는 PK(Primary Key)가 없어서 그런 것이고, 만약 파티셔닝 컬럼이 PK라면 다른 테이블은 아예 보지도 않고 필터링도 없어 바로 데이터를 가져온다. 하지만 PK가 있는 테이블도 저장 프로시저를 이용하여 쿼리를 하면 어차피 현재와 똑같이 필터링하여 결합하므로 큰 차이는 없다고 봐도 된다. 읽기 수를 보더라도 다른 테이블은 필터링을 하므로 0이 나온다. 이제는 테이블 파티셔닝을 이용해보자. SQL 서버 2005 베타 2에서 엔진 예시(Engine Example)를 설치하여 다음 폴더에 가보면 테이블 파티셔닝 예제가 있다.

C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\Engine\Administration\
Partitioning\Scripts\PartitionAW.sql

이 예제를 실행시키면 TransactionHistory 테이블을 파티셔닝을 하는데, 2003년 10월 이전부터 2004년 8월 이후까지 12개의 파티션으로 나누어서 만든다. 다음은 주요 코드 중에 하나이다.

-- Range partition table TransactionHistory
CREATE PARTITION FUNCTION TransactionRangePF1 (datetime) AS RANGE RIGHT FOR VALUES (‘10/01/2003’, ‘11/01/2003’, ‘12/01/2003’, ‘1/01/2004’, ‘2/01/2004’, ‘3/01/2004’, ‘4/01/2004’, ‘5/01/2004’, ‘6/01/2004’, ‘7/01/2004’, ‘8/01/2004’); GO

파티션 함수를 만드는데 있어 월별로 총 12개의 파티션으로 나누고 있다.

CREATE PARTITION SCHEME TransactionsPS1 AS PARTITION TransactionRangePF1 TO ([PRIMARY], [PRIMARY], [PRIMARY] , [PRIMARY], [PRIMARY], [PRIMARY] , [PRIMARY], [PRIMARY], [PRIMARY] , [PRIMARY], [PRIMARY], [PRIMARY] , [PRIMARY]);

여기에서 만든 함수를 스키마를 이용하여 물리적인 공간에 맵핑하는 데 있어 하나의 파일 그룹에 맵핑하고 있다.

CREATE TABLE [Production].[TransactionHistory]( [TransactionID] [int] IDENTITY (1, 1) NOT NULL, [ProductID] [int] NOT NULL, [ReferenceOrderID] [int] NOT NULL, [ReferenceOrderLineNumber] [smallint] NOT NULL , [TransactionDate] [datetime] NOT NULL , [TransactionType] [nchar](1) NOT NULL, [Quantity] [int] NOT NULL, [ActualCost] [money] NOT NULL, [ModifiedDate] [datetime] NOT NULL ) ON TransactionsPS1 (TransactionDate);

테이블을 생성할 때 앞에서 만든 스키마 위에 만들고 있다. 다 만들었으면 잘 만들었는지 예제 쿼리를 실행해보자.

SELECT * FROM Production.TransactionHistory WHERE TransactionDate between ‘2003-09-01’ and ‘2003-10-31’;
-----------------------------------------------------------------------------------------
(20494 row(s) affected)
Table ‘TransactionHistory’. Scan count 2, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

테이블이 하나이므로 하나의 테이블에서 두 번의 스캔이 일어났다. 이제 실행 계획을 보자. <화면 2>를 보면 ‘Nested Loops Join’을 이용하여 해당 테이블을 ‘Index Seek’하여 데이터를 가져오고 있다. 이 쿼리에서는 두 달 치의 데이터를 읽으므로 두 번의 스캔이 일어남을 확인할 수 있다.

슬라이딩 윈도우 구현

파티션된 테이블을 관리하다 보면 오래된 데이터는 거의 사용을 하지 않게 된다. 어쩌다 한 번씩 통계 자료용으로 사용하는 경우가 대부분이다. 이러한 데이터를 계속 고성능의 I/O 장치에 담아 두는 것은 비효율적이다. 따라서 오래된 데이터는 더 이상의 트랜잭션이 일어나지 않으므로 비교적 낮은 성능의 저렴한 I/O 장치로 이식하는 것이 효율적이다. 이러한 과정을 ‘슬라이딩 윈도우(파티션 스위칭)’라고 한다. 슬라이딩 윈도우의 우리말 뜻은 ‘미닫이창’이다. 즉 밀어서 여닫는 창이라는 뜻인데, 오래된 데이터는 밀어서 내보내고 대신 새 데이터를 받아들인다는 의미로 보면 될 것이다.

슬라이딩 윈도우를 구현하는데 있어 대량의 데이터가 이동하므로 느릴 것이라고 생각 할 수 있으나, 실제로는 메타 데이터만 이동하므로 상당히 빠르게 작업할 수 있다. 다음 예제 역시 SQL 서버 2005를 설치한 다음 폴더에 가면 슬라이딩 윈도우 예제가 있다.





C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\Engine\Administration\
SlidingWindow\Scripts\sliding.sql

이번 예제에서는 2003년도 9월의 데이터를 TransactionHistory 테이블에서 떼어 내어 TransactionHistoryArchive 테이블로 옮기는 작업이다. 초기 상태는 <그림 2>와 같다. TransactionHistory에는 12개의 파티션이 있고 Transaction HistoryArchive에는 2개의 파티션이 있다. TransactionHistoryArchive는 자주 사용하지 않는 데이터를 모아 두는 곳이므로 두 개의 파티션만을 만들었다. 여기에서 TransactionHistory 테이블에서 2004년도 9월 데이터를 위한 새로운 데이터를 위한 공간을 확보하자.

ALTER PARTITION FUNCTION TransactionRangePF1() SPLIT RANGE (‘9/01/2004’);

새로운 공간을 확보하기 위하여 기존 공간을 분할하여 총 13개의 파티션을 만들었다. 이제는 TransactionHistoryArchive에도 역시 새로운 공간을 확보하자.

ALTER PARTITION FUNCTION TransactionArchivePF2() SPLIT RANGE (‘10/01/2003’);








각각의 테이블에 새로운 공간을 할당하였으니 이제 파티션을 옮겨 보자.

ALTER TABLE [Production].[TransactionHistory] SWITCH PARTITION 1 to [Production].[TransactionHistoryArchive] PARTITION 2;

옮겼으면 기존 파티션을 병합하여 초기 상태로 만들어줘야 한다. 먼저 TransactionHistory부터 병합하자. 그 전에 sys.partition_ range_values라는 테이블을 조회하여 파티션 정보를 조회해보자.

function_id boundary_id parameter_id value
----------- ----------- ------------
65536 1 1 2003-10-01 00:00:00.000
65536 2 1 2003-11-01 00:00:00.000
65536 3 1 2003-12-01 00:00:00.000
65536 4 1 2004-01-01 00:00:00.000
65536 5 1 2004-02-01 00:00:00.000
65536 6 1 2004-03-01 00:00:00.000
65536 7 1 2004-04-01 00:00:00.000
65536 8 1 2004-05-01 00:00:00.000
65536 9 1 2004-06-01 00:00:00.000
65536 10 1 2004-07-01 00:00:00.000
65536 11 1 2004-08-01 00:00:00.000
65536 12 1 2004-09-01 00:00:00.000
65537 1 1 2003-09-01 00:00:00.000
65537 2 1 2003-10-01 00:00:00.000
(14 row(s) affected)

이제까지 제대로 작업을 했다면 총 14개의 행이 있을 것이다. 이제 다음과 같이 병합을 하자.

ALTER PARTITION FUNCTION TransactionRangePF1() MERGE RANGE (‘10/01/2003’);
병합을 한 후 다시 테이블 파티션 정보를 보자.

function_id boundary_id parameter_id value
----------- ----------- ------------
65536 1 1 2003-11-01 00:00:00.000
65536 2 1 2003-12-01 00:00:00.000
65536 3 1 2004-01-01 00:00:00.000
65536 4 1 2004-02-01 00:00:00.000
65536 5 1 2004-03-01 00:00:00.000
65536 6 1 2004-04-01 00:00:00.000
65536 7 1 2004-05-01 00:00:00.000
65536 8 1 2004-06-01 00:00:00.000
65536 9 1 2004-07-01 00:00:00.000
65536 10 1 2004-08-01 00:00:00.000
65536 11 1 2004-09-01 00:00:00.000
65537 1 1 2003-09-01 00:00:00.000
65537 2 1 2003-10-01 00:00:00.000
(13 row(s) affected)

병합을 했으므로 총 13개의 행이 생겼다. 이제 Transaction HistoryArchive도 병합을 하자.

ALTER PARTITION FUNCTION TransactionArchivePF2() MERGE RANGE (‘9/01/2003’);

이렇게 함으로써 슬라이딩 윈도우 작업을 완료할 수 있다. 작업이 간단하지는 않지만 이런 일련의 작업들이 실제로는 메타 데이터를 가지고 작업을 하기 때문에 상당히 빠르게 수행된다.

간편한 이력 관리를 위한 데이터베이스 스냅샷

SQL 서버 2005에서는 간단하게 데이터베이스에 대한 백업본을 만들 수 있다. 보통 개발자가 어떤 간단한 작업을 할 때 실수할까봐 트랜잭션을 걸고 작업을 한다. 그러다가 실수를 하면 롤백하면 되기 때문이다. 그런데 이렇게 작업을 하면 잠금이 걸리기 때문에 다른 사용자들은 대기하고 기다려야 하는 불편이 있다. 하지만 이제는 데이터베이스 스냅샷을 사용하면 현재 데이터베이스의 내용을 간단하게 백업을 할 수 있기 때문에 트랜잭션을 걸지 않아도 된다. 만약 실수를 하게 되면 간단하게 복구를 할 수 있다.

스냅샷은 읽기만 할 수 있는 데이터베이스이다. 만들 때에는 실제 데이터의 복사본을 만드는 것이 아니고 메타 데이터만으로 만들기 때문에 상당히 빠르고 적은 용량으로 만들 수 있다. 실제 구현을 보면 먼저 스냅샷은 현재 데이터베이스와 동일한 저장 공간을 예약하고 원본 데이터베이스에서 변경이 일어나면 먼저 스냅샷 데이터베이스에 복사를 한 후 원본 데이터베이스를 변경한다. 이를 복사-쓰기(copy-on-write) 기술이라고 부른다.
<그림 9>를 보면 원본 데이터베이스의 2라는 값이 10으로 바뀔 때 먼저 2라는 값을 스냅샷 데이터베이스에 복사를 하고 자기 자신의 값을 10으로 바꾸고 있다. 스냅샷 데이터베이스는 결국 원본 데이터베이스에서 바뀌기 전의 상태 값만 가지고 있고, 나머지는 원본 데이터베이스를 참조한다. 그래서 생성 시간이 빠르고 공간도 적게 차지하는 것이다. 그럼 직접 실습을 해보자.

CREATE DATABASE Test; USE Test; CREATE TABLE Dummy ( Data int ); INSERT INTO Dummy VALUES (1); INSERT INTO Dummy VALUES (2); INSERT INTO Dummy VALUES (3); INSERT INTO Dummy VALUES (4);

스냅샷 생성
CREATE DATABASE Test_01 ON ( NAME = Test, FILENAME = ‘c:\program files\microsoft sql server\mssql.1\mssql\data\Test_01.ss’ ) AS SNAPSHOT OF Test;










Test라는 데이터베이스를 만들고 Dummy라는 테이블을 만들어 1,2,3,4라는 값을 넣고 Test_01이라는 Test 데이터베이스의 스냅샷을 만들었다. 앞에서 만든 Test_01.ss 라는 파일의 실제 크기를 보면 <화면 3>과 같다.
크기는 1.56MB를 할당했지만 실제 사용하는 크기는 128KB밖에 안 된다는 것을 확인할 수 있을 것이다. 이제 Test 테이블에서 2라는 값을 10으로 바꾸고 스냅샷에서 제대로 값을 보존하고 있는지 확인해보자.

UPDATE Dummy SET Data = 10 WHERE Data = 2; SELECT * FROM dummy; USE Test_01; SELECT * FROM dummy;
------------------------------
Data
------------------------------
1
10
3
4
(4 row(s) affected)
Data
------------------------------
1
2
3
4
(4 row(s) affected)

스냅샷 테이블이 이전 값을 잘 간직하고 있음을 확인할 수 있을 것이다. 다시 Test_01.ss의 파일 크기를 보면 384KB로 그 크기가 커져 있는 것을 확인해 볼 수 있다. 즉 2라는 값을 저장하므로 그만큼의 공간이 늘어난 것이다. 이번에는 원본 데이터베이스를 복구해보자.

USE master; RESTORE DATABASE Test FROM DATABASE_SNAPSHOT = ‘Test_01’; USE Test; SELECT * FROM dummy;
------------------------------
Data
------------------------------
1
2
3
4
(4 row(s) affected)

제대로 복구된 것을 확인할 수 있다.

멈추지 않는 시스템을 위한 DB 미러링

SQL 서버 2000에는 서버가 다운되더라도 다른 서버가 대신 작동하게 하는 기능으로 클러스터링을 이용했다. 그러나 클러스터링을 구축하기 위해서는 공유 디스크와 같은 별도의 하드웨어가 필요했다. 또한 디스크 자체를 공유하므로 디스크가 깨지는 경우에는 좋은 해결책이 아니었다. 또 광케이블로 연결해야 하므로 100마일이라는 거리 제한도 있었다.

SQL 서버 2005에서는 또 다른 해결책으로 미러링이라는 것을 지원한다. 미러링은 두 대의 SQL 서버를 운영하면서 서로 로그 정보를 주고받으면서 동일한 데이터를 유지한다. 따라서 별도의 공유 디스크가 필요 없으며, 디스크 자체가 깨지더라도 서로 디스크 복사본을 유지하기 때문에 문제가 안 된다. 또한 별도의 광케이블이 아닌 일반 네트워크 선을 사용하므로 거리 제한도 없다. 여기에 클러스터링은 서버에 문제가 생겨 교체되는데에 30초 이상의 시간이 걸리지만 미러링은 2~3초면 서버가 교체되어 자동으로 작동한다.

그렇다고 미러링이 클러스터링의 대안은 될 수 없다. 미러링은 시스템 데이터베이스에는 사용하지 못하고 단지 사용자 DB만 사용할 수 있다. 따라서 클러스터링은 전체 시스템을 보호하는 용도로, 미러링은 중요한 사용자 데이터베이스를 보호하는 용도로 사용하는 것이 적당할 것이다. <그림 10>은 미러링의 동작 방법이다.

미러링은 데이터 자체를 서로 전송하는 것이 아니라 로그만을 전달한다. 애플리케이션으로부터 데이터 수정 작업이 들어오면 이를 먼저 로그에 기록한 다음 미러 서버에게도 로그 정보를 전달하여 미러 데이터베이스에도 동일 정보를 유지하도록 해준다. 이러한 동작은 감시 서버(Witness Server)가 계속 감시하고 있다가 만약 주 서버가 다운이 되면 바로 미러 서버를 주 서버로 바꾸어 동작하게 한다. 그동안 애플리케이션은 별도의 프로그램 수정 없이도 자동으로 미러 서버를 주 서버로 간주하여 접속을 유지한다. 그럼 직접 실습을 해보자.

제대로 된 실습을 위해서는 주 서버, 미러 서버, 감시 서버 이렇게 3대가 있어야만 하지만, 간단한 실습을 위해 한 서버에 이 세 개의 서버를 인스턴스 이름만 달리하여 설치하면 테스트가 가능하다. 3개의 서버를 모두 설치한 후 다음 같이 종단점을 만들자.

CREATE ENDPOINT EndPoint_Principal STATE=STARTED AS TCP (LISTENER_PORT=5055) FOR DATABASE_MIRRORING (ROLE=ALL);

종단점은 외부에서 이 서버에 접근할 수 있는 문을 열어주는 의미이다. TCP 프로토콜을 사용하여 5055 포트를 열어 주었다. 마찬가지로 다른 미러 서버와 감시 서버도 종단점을 만든다. 단 이 때 한 서버에서 테스트를 하는 것이므로 서로 다른 포트 번호를 부여해줘야 한다. 미러 서버는 5056, 감시 서버는 5057 이런 식으로 다른 포트 번호를 부여하자. 그런 다음 앞서 스냅샷에서 실습한 Test DB를 주 서버에서 백업하여 미러 서버에 복구를 한다. 따라서 주 서버와 미러 서버는 동일한 Test DB를 가지게 된다. 그런 다음 미러링을 위한 파트너를 다음과 같이 미러 서버에서부터 작업을 한다.

ALTER DATABASE [Test] SET PARTNER = ‘TCP://WIN2003-SQLServer2005:5055’

마찬가지로 주 서버에서는 미러 서버와 감시 서버를 연결한다.

-- mirrored 서버 지정 ALTER DATABASE [Test] SET PARTNER = ‘TCP://WIN2003-SQLServer2005:5056’

-- witness 서버 지정 ALTER DATABASE [Test] SET WITNESS = ‘TCP://WIN2003-SQLServer2005:5057’

이제 주 서버에서 다음과 같은 데이터 작업을 해보자.

INSERT INTO Dummy VALUES (11); INSERT INTO Dummy VALUES (12);

이제 미러 서버에서 동일한 데이터가 존재하는지 확인해보자. 이 때 미러 서버는 항상 읽기 전용의 복구 모드로 동작을 하기 때문에 접근을 할 수가 없다. 접근을 하면 다음과 같은 에러 메시지가 나온다.

Database Test cannot be opened - it is acting as a mirror database.

따라서 앞서 실습한 스냅샷을 이용하여 접근을 해야 한다. 미러 서버의 스냅샷을 만들고 Dummy 테이블을 조회해보자.




SELECT * FROM dummy;
-----------
1
2
3
4
11
12

값이 제대로 들어가 있는 것을 확인해 볼 수 있을 것이다. 이제 주 서버를 한 번 다운시켜 보자. SQL Computer Manager에서 주 서버의 동작을 멈춘다. 그러면 자동으로 미러 서버가 주 서버가 되고 기존의 주 서버는 미러 서버로 서로 스위치가 된다. 새로운 주 서버에서 Dummy 테이블을 조회해보자. 이전까지는 스냅샷 없이는 조회가 안 되던 것이 이제는 잘 될 것이다.

정식 SQL 서버 2005를 기다리며

지난 4개월 동안 SQL 서버 2005의 베타 2 버전을 살펴보았다. 앞으로 새로운 버전이 나오면서 또 어떻게 바뀔지는 모르지만 베타 2 정도면 SQL 서버 2005에서 구현하려는 핵심 기능은 대부분 들어 있다고 봐도 될 것이다. 그 핵심 기능을 요약해 보면 개발자의 관점에서는 닷넷과의 통함이 될 수 있을 것이고, 관리자의 관점에서는 향상된 가용성(availability)이라고 할 수 있을 것이다. 이제 올 하반기에 나올 정식 SQL 서버 2005를 기다리면서 또 다른 항해를 준비하자.



제공 : DB포탈사이트 DBguide.net

출처 : 마이크로소프트웨어 [2005년 5월]

안정성를 보장하는 보안환경구현

Zewei Song | World Publishing 사

메인 고객지불 데이터베이스(SQL 서버 2000)에는 2백만명의 고객에 대한 e-머니와 1,600백만 건의 거래 레코드가 저장되어 있다. 각 레코드에는 매우 민감한 신용카드와 은행계좌 정보가 포함되어 있기 때문에 고객의 개인신용정보와 회사의 자산을 보호하기 위해서 반드시 암호화될 필요가 있다. 어플리케이션 기반의 암호화 솔루션을 도입하는 것은 ASP, Visual Basic 6.0, .NET 기반으로 개발된, 50 개 부분이 넘는 핵심 어플리케이션을 새로 작성해야 하는 상황이었기 때문에 채택하기가 곤란한 상황이었다. 전체적인 성능과 비즈니스 프로세스에 최소한의 영향을 미치게 되는 데이터베이스 서버기반의 암호화 솔루션이 필요한 상황이었다. 이를 위해 .NET 프레임워크와 SQL 서버 확장 저장 프로시저를 기반으로 데이터베이스 서버기반 암호화 솔루션을 개발하였다. 솔루션을 개발할 때, 기존 어플리케이션에 대한 수정을 최소화하고, 전체 시스템의 성능에 영향을 최소화해야 한다는 요구사항을 충족시켜야 했다.

처리절차

처음에는, 이러한 요구사항을 충족하는 상용 제품을 사용할 계획을 가지고 있었다. 일반적으로 사용되는 SQL 암호화 제품 중 ActiveCrypt 사의 XP_Crypt, Application Security 사의 DbEncrypt, NetLib 사의 Encryptionizer 제품을 선정하여 적용여부를 검토하였다. 세 가지 제품모두 데이터 암호화 기능을 훌륭하게 지원하였으나, 회사에서 필요로 하는 데이터 암호화 수준에는 부족한 부분이 있었다. 세 가지 제품 중 하나를 선택하는 경우, 50 여개의 핵심 어플리케이션에 상당한 수정작업을 수행하거나, 전면 재개발을 해야 하는 상황이었기 때문에, 회사로서는 이러한 수정작업을 위한 시간 및 자원이 부족하여 채택할 수 없는 상황이었다.
.NET 프레임워크에서 암호화 서비스를 제공하기 때문에, 먼저, .NET 프레임워크를 사용하여 SQL서버 수준에서 통합된 암호화 기능을 지원할 수 있는지에 대해 검토하였다. SQL 서버의 OLE 자동화 저장 프로시저를 통해, .NET DLL을 내부에 포함한 VB6 COM DLL을 호출할 수 있다는 것을 알게 되었다. 이러한 방법을 활용하여, .NET 프레임워크에서 제공하는 암호화 서비스를 SQL 서버와 통합한 솔루션을 개발할 수 있었다.
회사의 요구사항을 충족시키는 암호화 솔루션을 개발하는 작업을 시작하였다. 솔루션의 기본 개념은 암호화된 데이터를 저장하는 테이블의 명칭을 변경하고, 해당 테이블에 대한 뷰를 변경이전 테이블 명칭으로 생성하여, 암호화된 테이블의 데이터에 접근할 수 있도록 하는 것이었다. 어플리케이션에서는 암호화되지 않은 데이터에 접근할 수 있도록 하기 위해서, 뷰에서는 해당 데이터를 복호화하는 사용자정의함수를 호출하게 된다. 뷰에 데이터를 추가, 변경, 삭제하는 작업에 대해서는 INSTEAD OF 트리거를 사용하여, 실제적으로 테이블에 저장되는 데이터는 반드시 암호화될 수 있도록 설정하였다.
최초 개발한 솔루션에는 몇 가지 문제가 발견되었다. 암호화 솔루션의 기본개념은 매우 단순하고, 직관적이었으나, 실제로 운영해 본 결과, 상당한 성능관련 문제가 발생하였고, 고객 및 거래내역 테이블과 같은, 수백만 레코드를 저장하고 있는, 대용량 테이블에 대해서는 특히 문제가 되었다. 이러한 문제를 해결하기 위해, DBA와 지속적으로 업무협조를 하였다. 암호화 솔루션을 사용하기 위해, 인덱스에서 활용하기 위한 중복데이터를 저장하는 추가 컬럼을 추가하는 것과 같은, 데이터베이스 설계상 몇 가지 트릭을 적용했다.
모든 암호화 솔루션의 가장 핵심부분은 암호화 키에 대한 관리 부분이다. 개발한 솔루션에서는 RSA를 사용하여 암호화 키를 암호화 하고, 암호화된 암호화 키를 \system32 폴더에 XML 파일 형식으로 저장하였다. RSA는 매우 강력한 비대칭 알고리즘을 사용하기 때문에, 암호화 키를 매우 안전하게 암호화할 수 있으나, 성능이 매우 느리다는 문제가 있다. (테스트 결과, 대칭형 알고리즘을 사용하는 것에 비해, 1000배정도 느린 성능을 나타냈다.) 이를 위해, 암호화키를 최초로 사용할 때 복호화한 다음, 복호화된 키를 메모리 상에 저장하는 프로그램을 개발하였다.
데이터에 대한 암호화 속도도 문제가 되었다. 이를 위해, 테스트환경에서 초당 20,000회의 암호화/복호화 작업을 처리할 수 있는, 매우 빠른 속도를 보장하는, .NET 대칭형 알고리즘인 Rijndael-256을 사용하였다. 하지만, SQL 서버에서 OLE 자동화 저장 프로시저를 사용하여, VB 6.0으로 랩핑된 Rijndael-256 알고리즘을 호출하였기 때문에, 암호화 속도가 초당 200 회 정도로 줄어들었다. 나중에, OLE 자동화 저장 프로시저를 사용하는 것보다, 확장 저장 프로시저를 사용하여 VB6 COM으로 랩핑하지 않은 .NET DLL을 호출하는 것이, 훨씬 빠르다는 것을 알게 되었다. 최종적으로, 암호화를 처리절차를 구현하기 위한 확장 저장 프로시저를 Visual C++ DLL로 생성하였다. 이러한 수정작업을 통해 초당 암호화 속도를 350 퍼센트 증대시킬 수 있었다.

솔루션

[그림 1]에는, 크게 4 가지 기본 구성요소로 구성된 암호화 솔루션에 대한 개관이 나타나 있다. 첫번째 구성요소는 System.Security.Cryptography 네임스페이스를 기반으로, 대칭형 데이터 암호화 처리절차(암호화/복호화/해시)와 비대칭형 알고리즘 기반 암호화 키 관리 기능을 담당하는 .NET DLL 이다. 두번째는, .NET DLL을 랩핑하는 Visual C++ DLL로, 그 자체로 SQL 서버에서 호출할 수 있는 확장 저장 프로시저의 역할을 하게 된다. 나머지 구성요소는, 암호화 키를 관리하기 위한 .NET WinForm 기반 어플리케이션과, SQL 서버기반 솔루션을 유지보수하기 위한 SQL 스키마 변경내역을 동적으로 생성하는 .NET WinForm 기반 어플리케이션으로 구성된다.



암호화 솔루션을 적용하기 위해, 자체 개발한 50 개 이상의 핵심 어플리케이션을 수정하는 대신, ASP 어플리케이션 하나와 일부 DTS 패키지와 저장 프로시저만 수정하면 되었다. 수정한 ASP 어플리케이션은 고객 서비스 부서에서 사용하는 외부사용자를 위한 사이트로, 암호화된 데이터 컬럼을 검색할 수 있도록 수정하였다. 특정 계좌번호를 검색하는 작업을 수행하기 위해서, SQL 서버가 200백만 레코드가 넘는 고객에 대한 e-머니 관련 데이터에 포함된 모든 암호화된 계좌번호를 복호화해야 하기 때문에, 검색하는데 한 시간이상의 시간이 소용되는 문제가 발생하였다. 이 문제를 해결하기 위해, 계좌번호의 마지막 4 자리 숫자를 암호화하지 않은 상태로 저장하는 컬럼을 추가하고, 검색 쿼리에서는 새로 추가한 컬럼을 사용하여 1차 검색작업을 수행한 다음, 필터링된 결과에 대해서만 전체 계좌번호를 복호화하여 2 차 검색작업을 할 수 있도록 수정하였다.

도입효과

암호화 솔루션을 도입함으로써, 개인정보 데이터 보호에 관련 법령에서 규정하고 있는 고객에 대한 개인신용정보와 회사자산에 대한 데이터 보호 수준을 충족시킬 수 있게 되었다. 또한 데이터에 대한 잠재적인 침해와 같은 문제를 해결하기 위한 비용을 절감할 수 있었다. 캘리포니아 주 지정 데이터베이스 보호 협약(CDPA) 2003에 따르면, 암호화되지 않은 고객 데이터에 대한 침해가 발생한 경우, 회사는 반드시 “어떤 불합리한 지연없이, 동원할 수 있는 모든 수단을 통해” 모든 고객에게 해당 사실을 공지하도록 규정하고 있다. 또한, 암호화 솔루션을 자체 개발했기 때문에, 암호화된 데이터에 침입자가 침입할 수 있는 가능성을 더욱 효과적으로 차단할 수 있었다. 보안이 필요한 모든 데이터는 암호화되었기 때문에, 권한이 부여되지 않은 사용자는 해당 데이터를 사용할 수 없었다. 암호화 솔루션을 도입한 결과, 특별한 성능상의 문제는 발생하지 않았으며, 데이터베이스 기반 암호화 상용제품을 구매하기 위한 $20,000 이상의 비용을 절감할 수 있었고, 기존 어플리케이션에 대한 별도의 수정작업에 투자되어야 할 비용까지 감안할 때는 훨씬 더 많은 비용을 절감할 수 있었다.

제공 : DB포탈사이트 DBguide.net

출처명 : Windows&.NET

요약

이 문서에서는 SQL Server가 실행 중인 컴퓨터 사이에서 SQL Server 사용자 데이터베이스와 대부분의 공통 SQL Server 구성 요소를 이동하는 방법을 단계별로 설명합니다.

이 문서에서 설명하는 단계에서는 사용자가 master, model, tempdb 또는 msdb 시스템 데이터베이스를 이동하지 않는다고 가정합니다. 이 단계에서는 master 데이터베이스와 msdb 데이터베이스에 포함되어 있는 로그인과 대부분의 공통 구성 요소를 전송하기 위한 다른 옵션도 제공합니다. 시스템 데이터베이스를 이동하는 방법에 대한 자세한 내용은 이 문서의 "1단계: 사용자 데이터베이스를 이동하는 방법" 절에 나와 있는 Microsoft 기술 자료 문서를 참조하십시오. 또한, 시스템 데이터베이스를 전송한 경우에는 "2단계: 로그인 및 암호를 전송하는 방법"부터 "5단계: DTS 패키지를 이동하는 방법"까지를 수행하지 마십시오.

이 문서의 단계를 수행해도 전송되지 않는 특정 항목에 대한 자세한 내용은 이 문서의 "추가 정보" 절을 참조하십시오.

1단계: 사용자 데이터베이스를 이동하는 방법

Microsoft SQL Server 7.0에서 Microsoft SQL Server 2000으로 또는 SQL Server 7.0을 실행하는 서버와 SQL Server 2000을 실행하는 서버 사이에서 다음 중 하나를 사용하여 사용자 데이터베이스를 이동할 수 있습니다.

• 백업 및 복원
• Sp_detach_db 및 Sp_attach_db 저장 프로시저
• 데이터 가져오기 및 내보내기(SQL Server 데이터베이스 간 개체 및 데이터 복사)

참고 SQL Server 2000에서 Microsoft SQL Server 2000(64비트)으로의 데이터 마이그레이션이 지원됩니다. sp_attach_db 시스템 저장 프로시저나 sp_attach_single_file_db 시스템 저장 프로시저를 사용하거나 32비트 엔터프라이즈 관리자를 사용하여 64비트 데이터베이스에 32비트 데이터베이스를 연결할 수 있고 32비트 버전의 SQL Server와 64비트 버전의 SQL Server 간에서 데이터베이스를 이동할 수 있습니다. 같은 방법을 사용하여 SQL Server 7.0에서 데이터를 마이그레이션할 수도 있습니다. 하지만 SQL Server 2000(64비트)에서 SQL Server 7.0으로 데이터를 다운그레이드할 수는 없습니다. 각 방법에 대한 설명은 다음과 같습니다.

백업 및 복원

원본 서버에 있는 사용자 데이터베이스를 백업한 다음 대상 서버에 사용자 데이터베이스를 복원합니다.

• 백업이 진행 중인 동안 데이터베이스가 사용 중일 수 있습니다. 백업 완료 후 데이터베이스에 INSERT, UPDATE 또는 DELETE 문을 수행하면 백업에 이러한 변경 내용이 포함되지 않습니다. 모든 변경 내용을 전송해야 할 때 트랜잭션 로그 백업과 전체 데이터베이스 백업을 모두 수행하면 최소 시스템 중단 시간으로 변경 내용을 전송할 수 있습니다.
1. 대상 서버에 전체 데이터베이스 백업을 복원하고 WITH NORECOVERY 옵션을 지정합니다.

참고: 추가 데이터베이스 수정을 방지하려면 사용자에게 원본 서버에서 데이터베이스 작업을 종료하도록 지시합니다.

2. 트랜잭션 로그 백업을 수행하고 WITH RECOVERY 옵션을 사용하여 트랜잭션 로그 백업을 대상 서버에 복원합니다. 시스템 중단 시간이 트랜잭션 로그 백업 및 복원 시간으로 제한됩니다. 자세한 내용은 SQL Server 온라인 설명서의 "Transact-SQL 참조" 항목에 나와 있는 하위 항목 "RESTORE"를 참조하십시오.

• 대상 서버의 데이터베이스 크기가 원본 서버에 있는 데이터베이스 크기와 같습니다. 데이터베이스의 크기를 줄이려면 백업을 수행하기 전에 원본 데이터베이스를 축소하거나 복원을 완료한 후 대상 데이터베이스를 축소해야 합니다. 자세한 내용은 SQL Server 온라인 설명서의 "데이터베이스 만들기 및 유지 관리"에 나와 있는 "데이터베이스를 축소" 항목을 참조하십시오.

• 데이터베이스를 원본 데이터베이스와 다른 파일 위치에 복원하는 경우 WITH MOVE 옵션을 지정해야 합니다. 원본 서버에서 데이터베이스가 D:\Mssql\Data 폴더에 있지만 대상 서버에는 D 드라이브가 없어서 C:\Mssql\Data 폴더에 데이터베이스를 복원하려고 하는 경우를 예로 들 수 있습니다. 다른 위치에 데이터베이스를 복원하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
221465 INF: RESTORE 문에서 WITH MOVE 옵션 사용
304692 INF: BACKUP 및 RESTORE를 사용하여 SQL Server 7.0 데이터베이스를 새 서버로 이동

• 대상 서버에 이미 존재하는 데이터베이스를 덮어쓰려면 WITH REPLACE 옵션을 지정해야 합니다. 자세한 내용은 SQL Server 온라인 설명서의 "Transact-SQL 참조" 항목에 나와 있는 하위 항목 "RESTORE"를 참조하십시오.

• 복원할 대상 SQL Server의 버전에 따라 문자 집합, 정렬 순서 및 유니코드 데이터 정렬이 원본 서버와 대상 서버 모두에서 동일해야 합니다. 자세한 내용은 이 문서의 "데이터 정렬에 대한 참고" 절을 참조하십시오.
Sp_detach_db 및 Sp_attach_db 저장 프로시저

sp_detach_db 및 sp_attach_db 저장 프로시저를 사용하려면 다음과 같이 하십시오.

1. sp_detach_db 저장 프로시저를 사용하여 원본 서버에서 데이터베이스를 분리합니다. 해당 데이터베이스와 연관된 .mdf, .ndf 및 .ldf 파일을 대상 서버로 복사해야 합니다. 파일 형식의 설명을 보려면 아래 표를 참조하십시오.
파일 확장명 설명  
.mdf  주 데이터 파일  
.ndf  보조 데이터 파일
.ldf  트랜잭션 로그 파일  


2. sp_attach_db 저장 프로시저를 사용하여 대상 서버의 데이터베이스에 연결하고 이전 단계에서 대상 서버로 복사한 파일을 가리킵니다.이러한 방법을 수행하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
224071 INF: 분리/연결(Detach/Attach)을 사용하여 새 위치로 SQL Server 데이터베이스 이동
• 분리한 다음에는 데이터베이스에 액세스할 수 없으며 파일을 복사하는 동안에는 데이터베이스를 사용할 수 없습니다. 분리 시에 데이터베이스에 포함되어 있던 데이터가 모두 이동됩니다.

• Attach 메서드나 Detach 메서드를 사용하는 경우 양쪽 서버 모두에서 문자 집합, 정렬 순서 및 유니코드 데이터 정렬이 동일해야 할 수 있습니다. 자세한 내용은 이 문서의 "데이터 정렬에 대한 참고" 절을 참조하십시오.

데이터 정렬에 대한 참고

백업 및 복원 또는 Attach 및 Detach 메서드를 사용하여 SQL Server 7.0 서버 간에 데이터베이스를 이동할 때는 양쪽 서버에서 문자 집합, 정렬 순서 및 유니코드 데이터 정렬이 동일해야 합니다. SQL Server 7.0에서 SQL Server 2000으로 또는 SQL Server 2000 서버 간에 데이터베이스를 이동할 때는 데이터베이스가 원본 데이터베이스의 데이터 정렬을 유지합니다. 따라서 SQL Server 2000을 실행 중인 대상 서버가 원본 데이터베이스와 다른 데이터 정렬을 갖고 있으면 대상 데이터베이스가 대상 서버의 master, model, tempdb 및 msdb 데이터베이스와 다른 데이터 정렬을 갖습니다. 자세한 내용은 SQL Server 2000 온라인 설명서의 "혼합 데이터 정렬 환경" 항목을 참조하십시오.

데이터 가져오기 및 내보내기(SQL Server 데이터베이스 간에 개체 및 데이터 복사)

데이터 변환 서비스 데이터 가져오기 및 내보내기 마법사를 사용하여 원본 데이터베이스에서 대상 데이터베이스로 전체 데이터베이스를 복사하거나 개체와 데이터를 선택적으로 복사할 수 있습니다. • 전송 시 원본 데이터베이스가 사용 중일 수 있습니다. 원본 데이터베이스가 사용 중일 때는 전송 작업이 진행되는 동안 일부 차단이 발생할 수 있습니다.

• 데이터 가져오기 및 내보내기 마법사를 사용할 경우에는 문자 집합, 정렬 순서 및 데이터 정렬이 원본 서버와 대상 서버 간에 동일해야 할 필요가 없습니다.

• 원본 데이터베이스의 사용되지 않는 공간은 이동되지 않으므로 대상 데이터베이스가 원본 데이터베이스만큼 클 필요가 없습니다. 마찬가지로 일부 개체만 이동할 때도 대상 데이터베이스가 원본 데이터베이스만큼 클 필요가 없습니다.

• SQL Server 7.0 데이터 변환 서비스는 64KB보다 큰 텍스트 및 이미지 데이터를 올바르게 전송하지 못할 수 있습니다. SQL Server 2000 버전의 데이터 변환 서비스에는 이러한 문제가 적용되지 않습니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
257425 FIX: DTS 개체 전송에서 64KB보다 큰 BLOB 데이터를 전송하지 않는다
2단계: 로그인 및 암호를 전송하는 방법

원본 서버에서 대상 서버로 로그인을 전송하지 않으면 현재 SQL Server 사용자가 대상 서버로 로그온하지 못할 수 있습니다. 다음 Microsoft 기술 자료 문서에 나와 있는 지침을 사용하면 로그인과 암호를 전송할 수 있습니다.

246133 HOWTO: SQL Server 인스턴스 간에 로그인 및 암호 전송

대상 서버의 로그인용 기본 데이터베이스가 원본 서버의 로그인용 기본 데이터베이스와 다를 수 있습니다. sp_defaultdb 저장 프로시저를 사용하면 로그인용 기본 데이터베이스를 변경할 수 있습니다. 자세한 내용은 SQL Server 온라인 설명서에서 "Transact-SQL 참조" 항목에 나와 있는 하위 항목 "sp_defaultdb"를 참조하십시오.

3단계: 분리된 사용자 문제를 해결하는 방법

대상 서버로 로그인과 암호를 전송한 후에는 사용자가 데이터베이스에 액세스하지 못할 수 있습니다. 로그인은 보안 식별자(SID)를 통해 사용자와 연결되는데, 데이터베이스 이동 후 SID가 일치하지 않으면 SQL Server가 데이터베이스에 대한 사용자 액세스를 거부할 수 있습니다. 이 문제를 분리된 사용자라고 합니다. SQL Server 2000 DTS 로그인 전송 기능을 사용하여 로그인과 암호를 전송하면 대개 분리된 사용자를 갖게 됩니다. 또한, 원본 서버와 다른 도메인에 있는 대상 서버에서 액세스 권한이 부여된 통합 로그인으로 인해 분리된 사용자를 갖게 됩니다.

1. 분리된 사용자를 찾습니다. 대상 서버에서 쿼리 분석기를 연 후 이동한 사용자 데이터베이스에서 아래의 코드를 실행합니다.
exec sp_change_users_login 'Report'
이 프로시저는 로그온에 연결되지 않은 분리된 사용자를 모두 나열합니다. 사용자가 나열되지 않으면 2단계와 3단계를 건너뛰고 4단계로 이동합니다.

2. 분리된 사용자를 확인합니다. 사용자가 분리된 경우 데이터베이스 사용자는 서버에 성공적으로 로그온할 수 있지만 데이터베이스에 액세스할 수 있는 사용 권한이 없습니다. 데이터베이스에 대한 로그온 액세스 권한을 부여하려고 하면 사용자가 이미 존재하기 때문에 다음과 같은 오류 메시지가 나타납니다.

Microsoft SQL-DMO (ODBC SQLState: 42000) 오류 15023: 현재 데이터베이스에 '%s' 사용자 또는 역할이 이미 있습니다.

분리된 사용자를 확인하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

240872 INF: 데이터베이스를 SQL 서버 사이에서 이동할 때 사용 권한 문제를 해결하는 방법

이 문서에서는 로그인을 데이터베이스 사용자에 매핑하는 방법에 대한 지침을 제공하고 표준 SQL Server 로그인 및 통합 로그인에서 분리된 사용자를 확인합니다.
또는
274188 PRB: 온라인 설명서의 "분리된 사용자 문제 해결" 항목이 불완전하다

이 문서에서는 sp_change_users_login 저장 프로시저를 사용하여 분리된 사용자를 하나씩 수정하는 방법을 보여 줍니다. sp_change_users_login 저장 프로시저만 표준 SQL Server 로그인에서 분리된 사용자를 확인합니다.


3. 데이터베이스 소유자(dbo)가 분리된 것으로 나타나면 사용자 데이터베이스에서 다음 코드를 실행합니다.
exec sp_changedbowner 'sa'

저장 프로시저가 데이터베이스 소유자를 dbo로 변경하고 문제를 수정합니다. 데이터베이스 소유자를 다른 사용자로 변경하려면 원하는 사용자로 sp_changedbowner를 다시 실행합니다. 자세한 내용은 SQL Server 온라인 설명서의 "Transact-SQL 참조" 항목에 나와 있는 하위 항목 "sp_changedbowner"를 참조하십시오.

4. 대상 서버가 SQL Server 2000 서비스 팩 1을 실행할 때는 연결이나 복원(또는 둘 모두)을 수행한 후에도 데이터베이스 소유자가 엔터프라이즈 관리자의 사용자 폴더 목록에 없을 수 있습니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
305711 BUG: DBO 사용자가 엔터프라이즈 관리자에 표시되지 않는다

5. 원본 서버에서 dbo로 매핑된 로그온이 대상 서버에 없는 경우에 엔터프라이즈 관리자를 통해 시스템 관리자(sa) 암호를 변경하려고 하면 다음과 같은 내용의 오류 메시지가 나타날 수 있습니다.
오류 21776: [SQL-DMO] 'dbo' 이름을 사용자 컬렉션에서 찾을 수 없습니다. 정식 이름인 경우 []를 사용하여 이름의 각 부분을 분리한 뒤 다시 시도하십시오.

자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
218172 PRB: 엔터프라이즈 관리자에서 SA 암호를 변경할 수 없다
경고: 데이터베이스를 다시 복원하거나 연결하면 데이터베이스 사용자가 다시 분리되어 3단계를 반복해야 할 수 있습니다.

4단계: 작업, 경고 및 운영자를 이동하는 방법

4단계는 선택 사항입니다. 원본 서버의 모든 작업, 경고 및 운영자에 대한 스크립트를 만든 다음 대상 서버에서 이 스크립트를 실행할 수 있습니다.

• 작업, 경고 및 운영자를 스크립팅하려면 다음과 같이 하십시오.
263556 INF: SQL Mail 구성 방법
5단계: DTS 패키지를 이동하는 방법

5단계는 선택 사항입니다. DTS 패키지가 원본 서버에서 SQL Server나 리포지토리에 저장된 경우에는 원할 때 패키지를 이동할 수 있습니다. DTS 패키지를 서버 간에 이동하려면 다음과 같이 하십시오.

1. 원본 서버에서 DTS 패키지를 파일로 저장한 다음 대상 서버에서 이 DTS 패키지 파일을 엽니다.
2. 대상 서버에서 패키지를 SQL Server나 리포지토리로 저장합니다.참고: 각 패키지를 한 번에 하나씩 별개의 파일에 이동해야 합니다.

또는

1. DTS 디자이너에서 각 DTS 패키지를 엽니다.
2. 패키지 메뉴에서 다른 이름으로 저장을 누릅니다.
3. 대상 SQL Server를 지정합니다.

참고: 패키지가 새 서버에서 제대로 실행되지 않을 수 있습니다. 패키지를 편집하고, 이전 원본 서버에 있는 연결, 파일, 데이터 원본, 프로필 및 기타 정보에 대한 패키지의 모든 참조를 새 대상 서버를 참조하도록 변경해야 합니다. 각 패키지의 디자인에 따라 패키지 단위로 이렇게 변경해야 합니다.

추가 정보

복제, 로그 전달, 전체 텍스트 카탈로그, 명명된 백업 장치, 유지 관리 계획 및 연결된 서버 같은 기타 항목도 이동할 수 있습니다. 원본 서버에서 이러한 구성에 대해 확인하고 원할 경우 대상 서버에서 수동으로 이러한 구성을 설치하기 위한 단계를 수행하십시오.

전체 텍스트 구성 요소를 이동하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

240867 INF: SQL 7.0 전체 텍스트(Full-Text) 카탈로그 폴더와 파일을 이동, 복사 및 백업하는 방법

이 문서의 단계를 수행하는 경우 데이터베이스 다이어그램, 백업 및 복원 기록은 이동되지 않습니다. 이러한 정보를 이동해야 하는 경우에는 msdb 시스템 데이터베이스를 이동하십시오. msdb 데이터베이스를 이동하는 방법에 대한 자세한 내용은 이 문서의 "1단계: 사용자 데이터베이스를 이동하는 방법" 절에 나와 있는 Microsoft 기술 자료 문서를 참조하십시오. msdb 데이터베이스를 이동하는 경우에는 "4단계: 작업, 경고 및 운영자를 이동하는 방법" 또는 "5단계: DTS 패키지를 이동하는 방법"을 수행할 필요가 없습니다.

제공 : DB포탈사이트 DBguide.net

출처명 : 한국마이크로소프트

이 기사에서는 SQL Server 아키텍처의 내부 동작에 대해 자세히 다룹니다. 이 기사는 데이터베이스 엔진의 기능 향상에 대해 설명하며 사용 설명과 자세한 정보를 위한 지침을 제공합니다. SQL Server 엔진에 대한 자세한 정보를 이해하면 데이터베이스 관리자(데이터베이스 시스템 엔지니어)로서 데이터베이스 시스템을 디자인, 구축 또는 기능을 향상시킬 때 SQL Server의 장점을 최대한 활용할 수 있습니다. 이 자료가 데이터베이스 전문가들을 대상으로 하는 것이기는 하지만 교육이나 마케팅 측면에서도 유용하게 사용할 수 있습니다

데이터베이스 아키텍처: 저장소 엔진
Cathan Cook


요약: 이 기사에서는 SQL Server 아키텍처의 내부 동작에 대해 자세히 다룹니다. 이 기사는 데이터베이스 엔진의 기능 향상에 대해 설명하며 사용 설명과 자세한 정보를 위한 지침을 제공합니다. SQL Server 엔진에 대한 자세한 정보를 이해하면 데이터베이스 관리자(데이터베이스 시스템 엔지니어)로서 데이터베이스 시스템을 디자인, 구축 또는 기능을 향상시킬 때 SQL Server의 장점을 최대한 활용할 수 있습니다. 이 자료가 데이터베이스 전문가들을 대상으로 하는 것이기는 하지만 교육이나 마케팅 측면에서도 유용하게 사용할 수 있습니다.

목차
소개
저장소 엔진 향상
데이터와 상호 작용
테이블 및 인덱스
로깅 및 복구
관리 개선
데이터 저장소 구성 요소
결론

소개
이 기사에서는 Microsoft® SQL Server™ 2000의 새로운 저장소 엔진 기능에 대해 설명하고, 저장소 엔진 작동 방법에 대한 구체적인 설명과 함께 이러한 기능을 사용하는 데 유용한 세부 정보를 제공합니다. 저장소 엔진의 내부 동작에 대한 기본적인 사항을 이해하고 있으면 SQL Server의 성능을 최대한 활용할 수 있습니다.

높은 확장성이 주요 관심사가 되고 있는 상황에서 데이터베이스는 이제 단시일 내에 디자인되어 구현되고 있으며, 개발 요구 사항의 변화와 프로덕션 사용의 증가에 따라 끊임없이 진화하고 있습니다. 확장성, 가용성 및 사용의 용이성에 대한 요구는 민감하고도 유연한 데이터 저장소 엔진을 필요로 합니다.

SQL Server 2000의 여러 버전은 크기로는 포켓 PC용 소형 모바일 시스템에서부터 몇 테라바이트에 달하는 고성능 트랜잭션 프로세싱에 이르기까지 또는 클러스터된 Windows® 2000 Datacenter Server에서 실행되는 의사 결정 지원 시스템 등 아주 다양한 시스템을 지원합니다. 이러한 모든 시스템은 업무 지향적인 비즈니스 시스템이 요구하는 융통성, 보안성 및 안정성을 유지합니다.

저장소 엔진 작업이 지능화되고 자동화됨에 따라 다양한 용도와 규모의 프로젝트에 대해 SQL Server 2000 응용 프로그램을 구축할 수 있으며 정교한 아키텍처를 통해 성능, 가용성 및 확장성이 향상됩니다.

가용성
물리적 파일 상호 작용에 대한 새로운 알고리즘과 함께 안정성 및 동시성이 향상되었습니다. 이러한 알고리즘 덕분에 정기 점검 시 DBCC(database console command)를 실행할 필요가 없어졌습니다. 그러나, DBCC는 여전히 사용할 수 있으며 새로운 DBCC CHECK 명령은 온라인 처리가 진행 중인 과정에서도 실행될 수 있습니다.

확장성
실제 데이터베이스 파일과 디스크의 해당 레이아웃으로 구성된 저장소 하위 시스템은 소규모 데이터베이스에서 대규모 데이터베이스로의 확장을 지원합니다. SQL Server는 이제 최대 64GB의 실제 메모리(RAM)와 최대 32개의 프로세서를 지원할 수 있습니다.

사용의 용이성
데이터베이스 관리자(DBA)는 향상된 관리 기능을 사용하여 서버 관리를 자동화하고 집중화할 수 있습니다. 이렇게 하면 DBA가 모든 사이트를 방문하지 않고도 원격 서버와 응용 프로그램을 손쉽게 관리할 수 있습니다. 정교한 알고리즘으로 관리되는 서버 구성은 서버 사용 패턴에 대해 동적으로 응답하여 DBA가 데이터베이스 관리 및 최적화 작업에만 주력할 수 있도록 합니다.

저장소 엔진 향상
SQL Server 2000의 관계형 데이터베이스 서버는 관계형 엔진과 저장소 엔진의 두 부분으로 나뉘어져 있습니다. 이 두 개의 엔진은 OLE DB 같은 원시 데이터 액세스 구성 요소를 통해 상호 작용하면서 독립적으로 작동합니다. 관계형 엔진은 저장소 엔진에 인터페이스를 제공하는데, 이 저장소 엔진은 원본으로 사용하는 데이터베이스 저장소 구성 요소 및 기능과 상호 작용하는 서비스로 구성됩니다.

저장소 엔진의 주요 임무는 다음과 같습니다.

저장소 구성 요소 관리에 대한 사용의 용이성을 개선하기 위한 기능 제공
실제 파일에 대한 모든 I/O 및 데이터 버퍼 관리
동시성, 트랜잭션 관리, 잠금 및 로깅 제어
데이터를 저장하는 데 사용되는 실제 페이지 및 파일 관리
시스템 오류에서 복구

SQL Server 2000 저장소 엔진은 사소한 용량 계획 및 성능 조정에 대한 필요성을 줄이면서 개념적인 단순성과 실제 융통성을 추가하는 새로운 기능을 제공합니다. SQL Server 2000은 자신의 환경에 반응하며 데이터베이스 사용의 변화에 대해 정확하고 빠르게 동적으로 적응합니다. 이러한 기술적인 발전은 데이터베이스 관리에만 치중하던 것을 데이터를 서비스로 편리하게 이용하는 수준으로 향상시켰습니다. SQL Server 2000 DBA는 개별적인 매개 변수를 조정하는 데 시간을 보내는 대신 데이터 흐름과 사용에 민감한 시스템을 디자인하는 데 집중할 수 있습니다.

SQL Server 2000의 변경 사항은 SQL Server 7.0에 소개된 구조적 향상을 기반으로 하며 계속되는 개선과 혁신의 바탕이 되고 있습니다.저장소 엔진 팀의 주요 목표는 서버를 정기적으로 조정하는 데 드는 시간과 노력을 줄이는 것입니다. 대부분의 조정 매개 변수 설정은 데이터베이스 사용을 기반으로 하므로 엔진은 이제 적응 알고리즘에 따라 데이터베이스 환경의 상황에 맞게 동적으로 조정됩니다. 이 자동화된 융통성은 이전 버전에서 조정과 실험이 끊임없이 요구되었던 튜닝 매개 변수를 위해 구현되었습니다. 조정 기능을 수동으로 조절할 수도 있으나 SQL Server 2000을 사용하는 편이 더 낫습니다. SQL Server 고객 중 극히 일부만 튜닝 매개 변수를 조절할 필요가 있을 것입니다. 이런 유형의 조절은 공인된 데이터베이스 관리자의 감독 하에 주의 깊게 테스트해 본 후에 이루어져야 합니다.

다음은 SQL Server 2000 저장소 엔진에서 향상된 주요 기능을 요약한 테이블입니다. 이러한 기능에 대해서는 이 기사의 뒷부분에서 더 자세하게 다루겠습니다.

기능 설명 및 장점
응용 프로그램 잠금 관리자 양식과 같이 응용 프로그램이 정의된 리소스에 동시에 액세스하는 것을 제어하려면 SQL Server 응용 프로그램 잠금 관리자를 사용하여 새 저장 프로시저로 이러한 리소스를 잠글 수 있습니다.
DBCC(Database console command) 이제 업데이트 작업을 방해하지 않고도 온라인 처리가 수행되는 동안 DBCC CHECK 명령을 실행할 수 있습니다. 새로 향상된 기능을 사용하면 실제 페이지의 일관성을 확인하여 하드웨어로 인한 오류를 검색할 수 있습니다. SQL Server 2000 Enterprise Edition에서 DBCC는 이제 여러 프로세서 간에 병렬로 실행됩니다.
데이터베이스 옵션 모든 데이터베이스 옵션은 이제 ALTER DATABASE를 사용하여 수정할 수 있습니다. 이 기능은 관리 작업을 단순화합니다.
차등 백업 익스텐트 수준에서 데이터베이스 변경 내용을 추적하는 기능이 향상되었으므로 SQL Server 2000에서는 차등 백업이 더욱 빨라졌습니다.
동적 조정 서버는 동적 적응 알고리즘을 사용하여 이전의 정적 구성 설정에 자동으로 적응합니다. 관리 제어는 여전히 전체 시스템 리소스 관리에 사용할 수 있으나 일반적으로 사용할 필요가 없을 것입니다. 수동으로 설정된 매개 변수는 자신의 제한된 범위 내에서 동적으로 적응합니다.
행 텍스트 크기가 작고 자주 사용되는 텍스트 열이 포함된 테이블에서 작은 텍스트 값은 텍스트 값이 있는 페이지가 아닌 표준 데이터 행이 있는 같은 페이지에 저장될 수 있습니다. 자주 액세스되는 이런 텍스트 데이터가 있는 테이블에서 이 기능은 디스크 I/O의 많은 양을 줄일 수 있습니다.
병렬 인덱스 작성 엔터프라이즈 버전에서 인덱스 작성은 자동으로 병렬 처리를 위해 구성된 모든 프로세서를 사용하여 8개 프로세서가 있는 서버에서 6의 인수만큼 인덱스를 작성하는 데 걸리는 시간이 줄어듭니다. 또한 인덱스 작성에는 tempdb와 메모리에서 사용할 수 있는 리소스를 이용합니다.
인덱스 미리 읽기 인덱스 읽기 기능이 향상되어 인덱스 스캔의 성능이 향상되었습니다.
인덱스 재조직 DBCC SHOWCONTIG는 인덱스 조각화에 대한 보다 자세한 정보를 제공하도록 향상되었습니다. 새로운 DBCC 명령인 INDEXDEFRAG는 데이터베이스 서비스를 방해하거나 데이터베이스 일관성이나 복구에 위험을 초래하지 않고 인덱스 페이지를 온라인으로 재조직합니다.
인덱스의 내림차순 키 열 인덱스의 개별 키 열은 오름차순이나 내림차순으로 지정할 수 있습니다.
KILL 명령 이 명령은 이제 완료 진행률을 보고합니다. 이 명령이 롤백 같은 다른 프로세스를 처리 중이면 명령이 얼마나 실행되었는지 볼 수 있습니다. 이 명령은 사용자가 특정 세션과 연결되지 않은 Microsoft Distributed Transaction Coordinator(MS DTC) 트랜잭션을 중지할 수 있도록 기능이 향상되었습니다.
대용량 메모리 지원 Windows 2000 기술은 대용량 메모리를 사용하는 엔터프라이즈 버전 시스템의 성능을 향상시켰습니다. Windows 2000의 AWE 확장을 사용하여 SQL Server 2000은 실제 메모리(RAM)를 64GB까지 지원할 수 있습니다.
잠금 잠금 관리자는 스레드와 메모리 같은 추가 리소스를 통한 교착 상태를 검색하도록 기능이 향상되었습니다. 동시성이 향상되면 교착 상태는 줄어듭니다. 이것은 또한 SQL Server 2000에서의 확장성도 향상시킵니다.
논리 로그 표시 Transact-SQL 명령은 로그에 책갈피를 만들어 책갈피로 표시된 시점의 데이터베이스 상태로 복원할 수 있도록 합니다. 또한 이 기능은 같은 응용 프로그램에 사용된 여러 데이터베이스의 복원을 동기화합니다.
온라인 인덱스 재조직 DBCC SHOWCONTIG는 인덱스 조각화에 대한 보다 자세한 정보를 제공하도록 향상되었습니다. 새로운 DBCC 명령인 INDEXDEFRAG는 데이터베이스 서비스를 방해하거나 데이터베이스 일관성이나 복구에 위험을 초래하지 않고 인덱스 페이지를 온라인으로 재조직합니다.
최적화된 I/O 미리 읽기 SQL Server 2000은 스캔에 관련된 각 파일에 대해 한 번에 다중 직렬 미리 읽기를 실행합니다. 쿼리 최적화 프로그램은 테이블과 인덱스를 스캔할 때 성능 향상을 위해 직렬 미리 읽기 I/O를 사용합니다.
백업 시 암호 백업 미디어와 개별 백업은 암호를 사용하여 보호할 수 있습니다. 이렇게 하면 권한이 없는 사용자가 백업을 복원하거나 데이터베이스에 액세스할 수 없게 됩니다.
복구 모델 복구 모델을 사용하여 데이터베이스의 로깅 수준을 선택할 수 있습니다. 이렇게 하면 더욱 융통성 있게 트랜잭션 로그를 관리할 수 있습니다. 복구 모델을 온라인에서 변경하여 하루 중에 사용하는 여러 가지 데이터베이스를 보완할 수 있습니다.
공유 테이블 스캔 엔터프라이즈 버전에서 테이블의 여러 스캔은 디스크에 대한 실제 I/O를 줄이면서 해당 테이블의 다른 진행 중인 테이블의 장점을 사용할 수 있습니다.
로그 축소 로그를 축소하는 명령은 보다 많은 상황에서 즉시 실행됩니다. 로그를 즉시 축소할 수 없으면 SQL Server에서는 축소 작업을 계속하거나 완료하기 전에 실행되어야 할 작업에 대한 구조적인 피드백을 제공합니다.
스냅샷 백업 타 공급업체의 스냅샷 백업에 대한 지원이 향상되었습니다. 스냅샷 백업은 저장 기술을 활용하여 전체 데이터베이스를 몇 초 안에 백업하거나 복원합니다. 이러한 백업은 이제 기본 트랜잭션 로그 및 차등 백업과 결합하여 OLTP 데이터베이스를 완전하게 보호합니다. 이것은 가용성이 매우 중요한 중규모 또는 대규모 데이터베이스에 특히 유용합니다.
공간 효율적인 빈 테이블 및 인덱스 SQL Server 2000에서는 빈 테이블과 인덱스에 대해 디스크 페이지가 할당되지 않습니다. SQL Server 7.0에서는 빈 테이블과 인덱스에 대해 최대 세 페이지까지 할당되었습니다.
최상위 n 정렬 새로운 이 기능은 최상위 n 값의 검색을 최적화합니다(예: SELECT TOP 5 * FROM tablename).
Xlock SQL Server 2000에는 이 Transact-SQL 잠금 힌트가 새로 제공됩니다. 이것은 단독 트랜잭션 수준 페이지나 테이블 잠금을 명시적으로 호출하는 데 사용할 수 있습니다.

SQL Server 2000에는 보다 효율적으로 데이터와 상호 작용할 수 있고 보다 많은 관리상의 융통성을 허용하는 많은 기능이 추가되었습니다. 다음 절에서는 이러한 향상과 그 사용 방법에 대해 자세히 알아보겠습니다.

데이터와 상호 작용
SQL Server 2000에서는 저장소 엔진이 향상되어 데이터와 상호 작용할 때 더 많은 확장성과 성능을 제공합니다. 이러한 향상된 내용을 이해하면 SQL Server를 보다 효과적으로 사용할 수 있습니다.

데이터 교환은 사용자 인터페이스에서 시작하거나 자동화된 작업에서 시작하거나 상관 없이 쿼리로 시작됩니다. 데이터 요청은 관계형 엔진으로 전달되는데, 이 관계형 엔진은 저장소 엔진과 상호 작용하여 데이터를 구하고 사용자에게 다시 전달합니다. 사용자나 DBA의 입장에서 보면 저장소 엔진과 관계형 엔진의 기능을 구분하기가 어렵습니다.

보다 효과적으로 데이터 읽기
데이터는 일련의 트랜잭션을 통해 서버와 사용자 간을 이동합니다. 응용 프로그램이나 사용자가 작업을 시작하면 데이터베이스는 이것을 쿼리 프로세서로 전달하여 작업을 완료한 다음 최종 결과를 반환합니다. 쿼리 프로세서는 SQL 문을 받아들이고, 해석하고, 실행하여 작업을 수행합니다.

예를 들어, 사용자 세션에서 SELECT 문을 실행하면 다음 단계가 발생합니다.

관계형 엔진은 이 명령문을 실행 계획으로 컴파일하고 최적화합니다. 여기서 실행 계획이란 데이터를 가져오기 위해 필요한 일련의 단계입니다. 그런 다음 관계형 엔진은 실행 계획을 실행합니다. 실행 단계에는 저장소 엔진을 통해 테이블과 인덱스에 액세스하는 것도 포함됩니다.
관계형 엔진은 필요한 데이터를 얻기 위해 저장소 엔진으로 호출하면서 실행 계획을 해석합니다.
관계형 엔진은 저장소 엔진이 최종 결과 집합에 반환한 모든 데이터를 결합한 다음 사용자에게 돌려 보냅니다.
이 과정에서 성능 향상을 위해 두 가지 사항이 개선되었습니다. SQL Server 2000의 경우, 관계형 엔진은 정식 쿼리 서술어를 저장소 엔진으로 릴레이하여 프로세스의 초기에 적용될 수 있도록 합니다. 그 결과 저장소 엔진과 관계형 엔진 사이에는 교환이 훨씬 더 효율적으로 이루어집니다. 이렇게 하면 정식 쿼리에 대한 성능이 크게 향상됩니다.

최상위 n 향상
또 한 가지 개선된 것은 저장소 엔진이 결과 집합에서 최상위 n 레코드의 선택을 처리하는 방법입니다. SQL Server 2000의 경우 새로운 최상위 n 엔진이 다음과 같은 명령문을 수행하는 데 가장 적합한 경로를 분석합니다.

SELECT top 5 * from orders order by date_ordered desc

이 예제에서, 전체 테이블을 검색해야 하는 경우 엔진은 데이터를 분석하여 캐시의 최상위 n 값만 추적합니다. 이렇게 하면 전체 테이블이 정렬되는 대신 최상위 n 값만 정렬되므로 이런 유형의 SELECT 문에 대해 성능이 놀랄만큼 향상됩니다.

공유 스캔
SQL Server 2000 Enterprise Edition의 경우, 두 개 이상의 쿼리는 진행 중인 테이블 스캔을 공유할 수 있으며, 이것은 아주 대용량의 SQL Server 2000 데이터베이스의 성능을 향상시킬 수 있습니다. 예를 들어, 쿼리가 정렬되지 않은 스캔을 사용하여 아주 큰 테이블을 검색하는 경우 페이지는 캐시로 들어가 데이터가 들어 올 공간을 만듭니다. 다른 쿼리가 시작되면 같은 테이블의 두 번째 스캔이 디스크 I/O를 발생시켜 해당 페이지를 다시 검색합니다.테이블 스캔이 자주 발생하는 환경에서 이렇게 하면 양쪽 쿼리가 같은 데이터 페이지를 검색하게 되므로 디스크에 스레싱(thrashing)이 생길 수 있습니다.


프로세스를 최적화하면 이런 유형의 데이터 액세스 패턴에 의해 생긴 디스크 I/O 양을 줄일 수 있습니다. 테이블의 정렬되지 않은 첫 번째 스캔은 디스크의 데이터를 읽습니다. 디스크를 다시 읽는 대신에 같은 테이블의 정렬되지 않은 다음 스캔이 이미 메모리에 있는 정보를 기반으로 작성될 수 있습니다. 그림 1. 참고 같은 테이블에서 여러 스캔을 동시에 하는 동안 이 동기화 프로세스는 성능을 8배까지 증폭시킬 수 있습니다. 이러한 향상된 기능은 전체 테이블 크기가 캐시 크기보다 훨씬 큰 대용량 의사 결정 지원 쿼리에서 더욱 두드러집니다.

공유 스캔이란 사용 가능한 실행 계획이 없는 쿼리를 보조하기 위해 저장소 엔진에 의해 호출되는 기능입니다. 이 기능은 아주 큰 테이블을 자주 읽을 때 지원하기 위해 만들어졌습니다. 쿼리 프로세서가 최적의 실행 계획에 테이블 스캔이 포함되는 것으로 결정하면 이 기능이 호출됩니다. 하지만, 공유 스캔을 강제하는 데 쿼리나 인덱스 조정을 사용할 수는 있으나 제대로 유지 관리된 인덱스가 같거나 더 나은 작업을 수행할 수 있는 상황에서는 테이블 스캔을 강요해도 성능이 나아지지 않습니다.

동시성
많은 사용자가 데이터와 상호 작용하는 동안 트랜잭션 동시성을 유지하기 위해 저장소 엔진은 리소스를 잠궈 행, 페이지, 키, 키 범위, 인덱스, 테이블 및 데이터베이스에 대한 종속 관계를 관리합니다. 이러한 요소가 변경되는 동안 리소스를 잠그면 엔진은 두 명 이상의 사용자가 같은 데이터를 동시에 변경하는 것을 막을 수 있습니다. SQL Server 잠금은 트랜잭션에 필요한 가장 덜 제한적인 잠금을 선택하기 위해 여러 수준의 세분성에 동적으로 적용됩니다.

SQL Server 2000에서는 동시성이 향상되어 교착 상태와 피할 수 있는 리소스 잠금이 더욱 줄었습니다. 예를 들어, 잠금 관리자는 스레드와 메모리 같이 경합 상태일 수 있는 다른 리소스를 인식하도록 향상되었습니다. 이 새로운 기능은 데이터베이스 관리자가 더욱 다양한 디자인이나 하드웨어 제한을 식별하는 데 도움이 됩니다.

잠금 관리자에 대한 새로운 Transact-SQL 인터페이스가 소개되어 프로그래밍 코드 내에서 사용자 지정 잠금 논리를 지원합니다. 비즈니스 논리에 필요한 잠금은 사용자의 Transact-SQL 배치 내에서 sp_getapplock을 호출하여 시작됩니다. 이렇게 하면 사용자는 응용 프로그램이 정의된 잠겨질 리소스(예를 들면, 데이터 행의 잠금 대신 양식 같은 응용 프로그램 리소스의 잠금), 사용할 잠금 모델, 시간 제한 값 및 잠금의 범위가 트랜잭션인지 세션인지 여부 등을 지정할 수 있습니다. 새 응용 프로그램 잠금 관리자로 잠금이 시작되면 잠금은 저장소 엔진으로 시작된 것처럼 SQL Server의 표준 잠금 관리에 참여하므로 호출 트랜잭션이 종료되면 사용자가 응용 프로그램으로 시작한 잠금이 열려진 채 있지 않을까 걱정할 필요가 없습니다.

SQL Server 2000에서 잠금을 취득하는 프로세스는 페이지의 모든 데이터가 커밋되는지 여부를 고려합니다. 예를 들어, pubs 데이터베이스의 테이블처럼 최근에 데이터가 변경되지 않은 테이블에 대해 SELECT 문을 실행하면 활성 트랜잭션이 최근에 테이블을 업데이트하지 않았으므로 이 프로세스에서는 잠금이 생기지 않습니다. 저장소 엔진은 데이터 페이지의 로그 시퀀스 번호를 현재 활성 트랜잭션과 비교하여 이 작업을 수행합니다. 대부분의 데이터가 가장 오래된 활성 트랜잭션보다도 오래된 데이터베이스의 경우, 이렇게 하면 잠금이 크게 줄어들어 성능이 향상됩니다.

트랜잭션 동안 잠금이 데이터를 보호하는 반면, 다른 프로세스인 래치는 실제 페이지에 대한 액세스를 제어합니다. 래치는 아주 경량이면서 단기간의 동기화 개체로 트랜잭션 기간 동안 잠글 필요가 없는 작업을 보호합니다. 엔진이 페이지를 스캔하면 엔진은 페이지를 래치하고 행을 읽어 관계형 엔진에 되돌려준 다음, 다시 페이지의 래치를 풀어 다른 프로세스가 같은 데이터에 접근할 수 있도록 합니다. 지연 래치라고 부르는 프로세스 동안 저장소 엔진은 페이지가 다른 진행 중인 프로세스에서 요구되는 경우에만 래치를 해체하여 데이터 페이지에 대한 액세스를 최적화합니다. 같은 데이터 페이지를 필요로 하는 진행 중인 프로세스가 없으면 해당 페이지에서 전체 작업에 대해 한 개의 래치만 유효한 상태로 남습니다.

시스템의 동시성을 향상시키려면 데이터베이스 시스템의 디자인과 이에 접촉하는 코드 개체에 포커스를 맞춰야 합니다. SQL Server 2000은 여러 테라바이트의 데이터와 사실상 무한한 선형 확장성을 지원하도록 디자인되었습니다. DBA의 역할은 데이터베이스의 라이프 사이클(코드에서 디스크의 데이터 저장에 이르기까지 모든 데이터베이스 구성 요소의 최적화와 디자인 주기)을 관리하여 디자인이 서비스 수준 계약을 지속적으로 만족시키도록 하는 것입니다.

테이블 및 인덱스
또한 실제 데이터 구조를 개선하여 디자인과 유지 관리에 더 많은 융통성이 생겼습니다.

테이블이나 인덱스가 늘어남에 따라 SQL Server에서는 새로운 데이터 페이지를 여덟 개의 집합으로 나누었으며, 이것을 익스텐트라고 부릅니다. 한 데이터 행이 여러 페이지에 걸쳐 있을 수 없으므로 8KB의 데이터만 수용할 수 있습니다. 이 때 관련된 text, ntext 또는 image 열은 다른 페이지에 저장할 수 있습니다. 클러스터된 인덱스가 있는 테이블은 디스크에서 키 순서에 따라 실제로 저장됩니다. 힙이란 클러스터된 인덱스가 없고 저장되지 않는 테이블을 말합니다. 레코드는 삽입된 순서대로 저장됩니다.

SQL Server 2000은 인덱싱된 뷰를 지원하는데, 다른 데이터베이스 제품에서는 보통 구체화된 뷰라고 합니다. 클러스터된 인덱스가 뷰에서 만들어지는 경우, 이 뷰는 더 이상 파생 개체가 아니라 클러스터된 인덱스가 있는 테이블과 같은 구조를 가진 데이터베이스에 저장된 기본 개체가 됩니다. 유지 관리에 드는 비용이 성능 향상에 따른 이점보다 많지 않은 경우라면 인덱싱된 뷰는 미리 계산된 값이나 복합 조인의 결과를 저장하는 데 유용합니다. SQL Server 2000 Enterprise Edition의 경우, 인덱싱된 뷰가 쿼리 계획을 최적화할 수 있는 경우에는 언제든지 쿼리 프로세서에서 자동으로 인덱싱된 뷰가 사용됩니다. 인덱싱된 뷰는 거의 변하지 않지만 대개 복합 조인이나 계산 쿼리의 일부인 데이터의 쿼리 속도를 개선할 수 있습니다.

행 텍스트
행 텍스트를 사용하면 기본 페이지에 작은 텍스트 데이터를 저장할 수 있습니다. 예를 들어, text 열이 있는 테이블이 있으나 보통 텍스트 값이 다른 행이 있는 기본 페이지에 들어갈 만큼 작으면 text 열에 임계값을 설정할 수 있습니다. 임계값은 이보다 작은 데이터는 별도의 텍스트 페이지가 아닌 기본 페이지에 텍스트를 저장하도록 크기를 결정하기 위한 것입니다. 대부분의 데이터가 페이지 크기에 맞고, 데이터의 일부만 텍스트 페이지를 만들어야 할 만큼 큰 경우라면 이런 방식으로 작업하는 것이 훨씬 빠릅니다.

이 새로운 기능을 사용할 시기를 결정하려면 I/O 개선에 비해 각 데이터 페이지에 저장할 수 있는 행 수나 저장소 밀도에 균형을 맞추어야 합니다. 예를 들어 설명을 위한 text 열이 있다고 가정합시다. 테이블의 텍스트 값 중 20%는 크고, 80%는 100바이트 미만입니다. 이런 경우라면 행 텍스트 솔루션에 대한 논리적인 후보로 생각할 수 있지만 행 텍스트는 해당 열의 데이터가 자주 액세스되는 경우에만 사용해야 합니다. 사용자가 이 테이블에 자주 액세스하지만 특별한 연구를 하는 경우를 제외하고는 comments 열을 보지 않는다면 행 텍스트를 사용하는 것은 좋은 해결책이 아닐 수 있습니다. 저장소 밀도는 페이지 당 적은 수의 행이 저장되면 줄어듭니다. 또한 테이블에 더 많은 페이지가 들어 있으면 테이블 스캔의 응답 시간은 늘어납니다. 그러므로, 행 텍스트를 구현하는 가장 적절한 경우는 자주 액세스되는 text 열이 있고, 그 열에 행에 저장할 수 있는 8K보다 작은 값이 여러 개 있는 경우입니다.

새로운 데이터 형식
SQL Server 2000에서는 새로운 데이터 형식 세 가지가 소개됩니다. bigint는 8바이트 정수 형식이며, sql_variant는 다른 데이터 형식의 데이터 값 저장을 허용합니다. 세 번째 데이터 형식인 table은 성능을 최적화하는 데 유용합니다. 테이블 변수는 tempdb를 더욱 효과적으로 사용할 수 있게 하며 임시 테이블보다 빠릅니다. 다른 변수들처럼, table 변수는 자신이 선언된 배치로 제한됩니다. 임시 테이블과 거의 유사한 기능으로 테이블 변수는 임시 테이블이나 커서보다 더 빠르게 수행되며, 서버 리소스를 더욱 잘 활용할 수 있도록 합니다. 일반적으로 데이터베이스와 상호 작용할 코드를 만드는 경우 사용자의 서버에서 사용할 수 있는 리소스를 활용할 수 있는 가장 적합한 방법을 찾아보십시오.

인덱스
데이터 액세스는 인덱스 사용을 통해 최적화할 수 있습니다. 인덱싱 요구 사항은 사용에 따라 달라지므로 잘못된 인덱싱은 데이터베이스의 속도가 늦어지는 가장 일반적인 원인 중 하나가 됩니다. 표준 인덱스 관리에는 현재 인덱싱 스키마를 정기적으로 확인하고 인덱스를 적절하게 빼거나 추가하여 현재의 시스템 사용에 맞게 조절하는 것이 포함되어야 합니다.

SQL Server 2000의 몇 가지 새로운 기능 덕택에 관리에 대한 인덱스 유지 관리를 보다 쉽고 효과적으로 할 수 있게 되었습니다. 이러한 향상으로 인해 디스크 I/O는 줄고 인덱스 스캔의 성능은 향상되었습니다. 이것은 범위 스캔에 사용할 수 있는 보조 인덱스가 있는 경우 특히 유용합니다.

인덱스 작성
인덱스를 작성하는 경우 저장소 엔진은 행을 샘플링하여 인덱스를 작성하는 데 서버 리소스를 활용하는 가장 효율적인 방법을 계산합니다. 옵션을 사용하면 인덱스 작성 방법을 제어할 수 있으므로 시스템 리소스 할당 방법을 제어하도록 선택할 수 있습니다. 이러한 옵션을 사용하면 특정 데이터베이스 시스템에 대한 사용자의 지식에 따라 시스템 성능에 전반적으로 중요한 프로세스에서 리소스의 균형을 맞추어 인덱스 작성이 트랜잭션 프로세스에 최소한의 영향을 미치도록 할 수 있습니다.

리소스 명령 옵션 설명
메모리 sp_configure(고급) index create memory 모든 인덱스 작성에 사용된 메모리 양을 지정합니다.
TempDB create index sort_in_tempdb 인덱스를 작성하는 동안 정렬에 사용되는 디스크 공간이 tempdb에서 할당되도록 합니다. 이렇게 하면 tempdb가 별도의 디스크에 있는 경우 더 많은 I/O 대역폭이 생기며, 인접한 공간에서 데이터베이스가 부족하면 인덱스 페이지가 실질적으로 더욱 인접한 레이아웃이 됩니다.
CPU sp_configure(고급) max degree of parallelism 병렬 작업(서버쪽)에서 사용하는 프로세서(CPU) 수를 제한합니다.

이러한 옵션에 대한 자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오.

대용량 시스템에 대한 다른 확장성 기능은 SQL Server 2000 Enterprise Edition에서 사용할 수 있는 병렬 인덱스 작성입니다. 이 프로세스는 사용자가 단일 CREATE INDEX 문을 실행할 때 자동으로 호출됩니다. 저장소 엔진은 데이터에 대한 요구 사항을 계산한 다음 별도의 스레드를 만들며, 각 스레드는 인덱스의 구역을 작성합니다.



또한 인덱스 작성은 공유 테이블 스캔을 사용하여 프로세스를 세부적으로 최적화합니다.

인덱스 조각 모음 실행
SQL Server 2000에서는 온라인으로 인덱스를 재구성하는 것을 지원하며, 이것은 이전 버전에서 크게 발전한 것입니다. 온라인으로 인덱스를 재구성하면 트랜잭션 처리 속도에 최소한의 영향을 미치며, 작업을 손실하지 않고도 언제든지 중단하고 다시 시작할 수 있습니다. 재구성은 조금씩 증가하며 이루어지고 완전히 복구할 수 있습니다.

테이블에서 정보를 삽입하고 삭제하고 업데이트하는 데 따라 클러스터된 인덱스 페이지와 클러스터되지 않은 인덱스 페이지는 실제적으로 조각화될 수 있고 해당 데이터에 대한 범위 쿼리의 효율성은 줄어듭니다. 그러므로 정기적으로 인덱스의 조각 모음을 실행하는 것이 좋습니다. SQL Server 2000에서 개선된 DBCC SHOWCONTIG를 사용하여 조각화를 분석하고 보고할 수 있습니다. 자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오.

인덱스가 조각화되었다고 판단되면 DBCC INDEXDEFRAG를 사용하여 재구성합니다. 그러면 빈 공간을 압축하고 채우기 비율 설정에 맞도록 설정된 범위 안에서 행을 이동하여 페이지가 논리 키 순서로 재정렬됩니다. 이렇게 하면 페이지를 조밀하게 채워 읽기 성능을 향상시켜 데이터를 스캔하는 동안 더 적은 페이지가 읽혀지게 됩니다. 인덱스를 정기적으로 유지 관리해 왔고 완전히 조각화되지 않은 상태라면 DBCC INDEXDEFRAG를 실행하는 것은 인덱스를 다시 작성하는 것보다 온라인 성능에 훨씬 영향을 덜 미칩니다.

DBCC INDEXDEFRAG는 내부적으로 작은 트랜잭션을 사용하는 실행 시간이 오래 걸리는 많은 온라인 관리 작업 중 하나입니다. 이러한 작은 트랜잭션은 서버 내에서 동시성을 최대화하고, 작업 내용을 손상하지 않고도 작업을 중지할 수 있게 하며, 오류가 발생할 경우 다시 작업하지 않도록 완전히 로깅되어 있습니다.

로깅 및 복구
트랜잭션 로그란 데이터베이스가 만들어진 시점에서 현재 시점까지 데이터베이스의 변경 사항을 기록하는 레코드 스트림을 말합니다. 로깅된 모든 작업은 로그 레코드를 만듭니다. 트랜잭션에 의해 만들어진 로그 레코드는 트랜잭션이 커밋할 때 디스크에 기록됩니다. 반면에 트랜잭션에 의해 수정된 데이터 페이지는 디스크에 즉시 기록되지 않고 SQL Server 버퍼 캐시에 보존되었다가 나중에 디스크에 기록됩니다. 데이터를 디스크에 기록하는 것이 지연되면 데이터 페이지에 대한 다중 액세스 효율을 최대화하며 스캔을 방해하지 않게 됩니다. 커밋 시 디스크에 기록을 수행하면 서버가 다운되어도 커밋된 작업은 손상되지 않도록 할 수 있습니다.

복구 작업은 데이터베이스가 온라인으로 가져오기 이전과 트랜잭션 상으로 일관성 있도록 하는 것입니다. 데이터베이스가 트랜잭션 상으로 일관성을 가진다는 것은 커밋된 모든 작업은 존재하고 커밋되지 않은 작업은 실행 취소된다는 의미입니다. 로그는 항상 데이터베이스의 올바른 뷰를 정의합니다. 간단히 말해서 복구란 데이터를 특정 시점의 트랜잭션 로그와 일치하도록 만드는 프로세스입니다.

복구는 SQL Server가 시작되고, 데이터베이스가 연결되거나 백업의 데이터베이스를 복원하는 마지막 단계로 자동으로 수행됩니다. SQL Server를 시작할 때 SQL Server에 의해 수행되는 복구는 restartstartup 복구라고 합니다. 백업에서 복구하는 것은 일반적으로 디스크 오류로 인한 것입니다. 이런 유형의 복구를 미디어 복구라고 합니다.

다시 시작 복구는 자동으로 수행되며 항상 가장 최근의 상태로 복구합니다. 백업에서 복구하는 경우 DBA는 이전 시점의 상태로 복구하도록 선택할 수 있습니다. 여기에는 제한 사항이 있습니다. 자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오.

시작 복구는 SQL Server의 인스턴스를 시작할 때마다 자동으로 발생하고 인스턴스가 마지막으로 종료될 때 불완전했던 모든 트랜잭션을 롤백하는 것으로 이루어집니다. 백업에서 복구하는 경우 DBA는 이전 시점의 상태로 복구하도록 선택할 수 있습니다. 여기에는 제한 사항이 있습니다. 자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오. 위의 두 경우에 복구는 이 대상 시점에 따라 수행됩니다.

복구는 다음의 두 단계로 이루어집니다.

트랜잭션 로그에서 대상 시점을 찾을 때까지 모든 변경 내용을 다시 실행합니다.
다시 실행이 중지된 시점에 활성화되어 있었던 트랜잭션에 의해 수행된 모든 작업의 실행을 취소합니다.
SQL Server에서는 검사점을 사용하여 다시 시작 복구를 빨리 진행합니다. 검사점은 현재 버퍼 캐시에 있는 모든 수정된 데이터 페이지를 디스크에 기록합니다. 이렇게 하면 복구의 다시 실행 부분에 대한 기준이 만들어집니다. 검사점은 비용이 많이 들 수 있으므로 SQL Server는 자동으로 검사점을 관리하여 다시 시작하는 데 걸리는 시간을 최소화하면서 성능을 최대화합니다.

SQL Server 2000에서 성공적으로 완료한 쓰기 작업은 디스크에 저장되어야 합니다. 쓰기 캐싱 디스크 저장소를 사용하는 경우, 저장소 공급업체에 문의하여 캐시가 내결함성인지 확인합니다. 내결함성이란 캐시가 정전이나 운영자의 동작에 대해 영향을 받지 않는다는 것을 의미합니다. 사용 중인 캐시가 내결함성이 아니라면 사용하지 말아야 합니다.

논리 로그 표시
SQL Server 7.0에서는 모든 지정된 시점으로 복구하는 것이 가능했습니다. 하드웨어에 오류가 있는 경우 복구 프로세스는 아주 간단합니다. 그러나, 데이터베이스에 대한 다른 위험은 잘못된 데이터가 입력되거나 유효한 데이터가 사용자의 동작에 의해 파괴될 가능성이 있다는 것입니다. 이런 경우 사용자는 오류 트랜잭션을 시작할 시기를 결정해야 합니다. SQL Server 7.0의 경우, 이 작업을 하는 유일한 방법은 문제가 다시 일어날 때까지 데이터베이스 복사본으로 로그를 복원하는 것이었습니다. 그런 다음 오류가 발견된 바로 전 시점까지 프로덕션 이미지에 대한 복원을 실행할 수 있었습니다.

SQL Server 2000에서는 트랜잭션을 로그에 표시할 수 있습니다. 나중에 복원할 필요가 생기면 실제 시간이 아닌 실행 시간에 사용된 표시를 참조할 수 있습니다. 이렇게 하려면 명명된 BEGIN TRANSACTION 문과 WITH MARK [description] 절을 사용합니다. 표시는 msdb에 저장됩니다. 복구는 표시가 들어 있는 트랜잭션을 포함하거나 바로 앞에서 중지할 수 있습니다. 예를 들어, 일괄적으로 실행되고 여러 레코드를 변경하는 프로세스가 있으면 이 기능을 사용하여 잘못된 상황에서 프로세스가 실행되면 명령이 실행된 시점으로 데이터를 롤백할 수 있도록 합니다.

표시 이름은 고유하지 않아도 됩니다. 어느 트랜잭션이 필요한지 표시하려면 datetime 값을 지정합니다. 이에 대한 구문은 다음과 같습니다.

RESTORE LOG WITH [ STOPBEFOREMARK|STOPAFTERMARK ] = @TaggedTransaction AFTER @datetime

또한 분산 트랜잭션에서도 분산 표시라고 알려진 표시를 사용하여 여러 관련 데이터베이스를 트랜잭션상 일관성 있는 상태로 복구하도록 지원할 수 있습니다. 이러한 관련 데이터베이스는 SQL Server의 같거나 다른 인스턴스에 상주할 수 있습니다. 정기적으로 데이터베이스 집합에 걸쳐 분산 표시를 설정할 수 있습니다(예를 들어, 5분에 한 번씩). 데이터베이스 중 하나의 트랜잭션 로그가 손상되면 데이터베이스 집합을 이전 시점으로 복구해야 합니다. 분산 표시는 이 시점을 제공합니다. 분산 표시를 사용하면 여러 관련 데이터베이스에 대한 백업의 정확한 타이밍을 조절하려고 걱정할 필요가 없습니다. 자세한 내용은 SQL Server 2000 온라인 설명서의 "명명된 트랜잭션으로 복구"를 참조하십시오.

트랜잭션 로그 축소
SQL Server 7.0에서는 로그 축소 작업을 즉각적으로 실행할 수 없었습니다. 이 작업은 트랜잭션 로그가 다음에 백업되거나 잘릴 때까지 지연되었습니다. 이런 특성은 많은 SQL Server 7.0 사용자들을 혼란스럽게 했습니다. SQL Server 2000에서는 로그를 최대한 축소한 다음 로그 백업 후의 추가 축소가 가능한지 표시합니다. 이런 경우에는 로그 백업을 완료한 다음 축소 명령을 다시 실행합니다. 자세한 내용은 SQL Server 2000 온라인 설명서의 "트랜잭션 로그 축소"를 참조하십시오.

로그 크기는 현재 복구 모델과 응용 프로그램 디자인에 따라 달라집니다. 로그를 정기적으로 축소해야 하는 경우에는 원인에 대한 증상 이외에 다른 문제가 있는지 살펴보십시오. 축소 명령을 사용하여 정기적으로 로그를 유지 관리하는 데 주력하는 대신 로그를 채우는 원인이 무엇인지 살펴보아야 합니다.

복구 모델
SQL Server 2000에는 데이터 보호 계획을 활용하도록 복구 모델이 추가되었습니다. 이러한 복구 모델은 성능, 로그 공간 요구 사항 및 미디어(디스크) 오류에 대한 보호 사이의 보완 관계를 명확하게 합니다. 복구 모델에는 단순 복구, 전체 복구 및 대량 로그 등 세 가지가 있습니다.

복구 모델의 선택은 데이터베이스 사용과 가용성 요구 사항을 기준으로 하며, 적절한 백업과 복원 프로시저를 결정하는 데 도움이 됩니다. 복구 모델은 백업으로부터의 복구를 의미하는 미디어 복구에만 적용됩니다. 다시 시작 복구는 커밋된 모든 작업을 복구합니다. 자세한 내용은 SQL Server 2000 온라인 설명서의 "복구 모델 선택"을 참조하십시오.

사용자는 복구 모델 간에 손쉽게 전환할 수 있습니다. 예를 들어, 대용량 데이터베이스의 경우 전체 또는 대량 로그를 사용하거나 둘 다 사용할 수 있습니다. 대량 삽입과 인덱스 다시 작성으로 이루어진 데이터 로드를 처리하는 동안 낮에는 전체를 사용하고 밤에는 대량 로그를 사용할 수 있습니다. 또한 데이터 로드를 실행하고 전체 모드로 전환하는 동안 대량 로그로 전환하고, 트랜잭션 로그 백업을 실행할 수 있으며, 전체 데이터베이스 백업을 실행하지 않고도 해당 시점으로 복원할 수 있습니다. 이 기능을 사용하면 대량 처리를 보다 효과적으로 처리할 수 있습니다. 필요한 작업은 나중에 트랜잭션 로그 백업을 만드는 것뿐입니다.

복구 모델을 변경하려면 다음 구문을 사용합니다.

ALTER DATABASE SET RECOVERY RecoveryModel

자세한 내용은 SQL Server 2000 온라인 설명서의 "복구 모델 전환"을 참조하십시오.

단순 복구 모델
단순 복구 모델은 일반적으로 로그 공간을 더 적게 필요로 하지만 데이터나 로그 파일이 손상되는 경우 상당한 규모의 잠재적인 작업 손실을 초래합니다. 기본 복구에 필요한 이벤트만 기록됩니다. 단순 복구 모델을 사용하면 전체 데이터베이스와 차등 데이터베이스 백업만 사용할 수 있습니다. 오류가 발생하는 경우 마지막 백업 이후에 커밋된 모든 작업을 다시 실행해야 합니다. 이 모델은 관리하기는 가장 간단하지만 커밋된 작업의 손실을 허용하지 않는 중요한 업무를 다루는 응용 프로그램에는 적절하지 않습니다.

이 모델은 SQL Server 7.0 및 이전 버전에서의 검사점에서 로그 자름 옵션과 유사합니다.

전체 복구 모델
전체 복구 모델에서는 모든 것이 기록됩니다. 전체 복구 모델은 손상된 데이터 파일로부터의 작업 손실을 완전히 보호합니다. 트랜잭션 로그가 손상되면 가장 최근의 로그 백업 이후에 커밋된 작업이 손실되어 수동으로 다시 실행해야 합니다.

전체 복구 모델을 사용하는 경우라도 트랜잭션 로그에 대해 내결함성 디스크를 사용하여 데이터 손실을 막는 것이 중요합니다. 또한 전체 복구 모델을 사용하면 모든 특정 시점으로 복구할 수 있습니다.

대량 로그 복구 모델
대량 로그 복구 모델은 대량 작업 시 최상의 성능을 제공합니다. 또한 이러한 작업은 전체 복구 모델로 작업할 때보다 로그 공간을 덜 사용합니다. 예를 들어, 새 페이지를 할당하는 것은 기록되지만 페이지에 데이터를 삽입하는 것은 기록되지 않습니다. SQL Server 2000에서 대량 작업은 대량 로드(BCP 및 BULK INSERT, DTS 패키지 내에서 실행되는 경우 포함), SELECT INTO, CREATE INDEX, WRITETEXT, UPDATETEXT 등으로 이루어집니다.

전체 복구 모델과 비교할 때 대량 로그 복구 모델은 대량 작업에 대한 로깅을 최소화합니다. 로그가 손상되거나 가장 최근의 로그 백업 이후에 대량 작업이 발생하고, 마지막 로그 백업이 손실된 이후 데이터베이스를 변경하면 복구가 필요하다는 점을 기억하십시오.

이 모델은 특정 시점으로의 복구를 지원하지 않지만 대량 변경을 포함하는 트랜잭션 로그 백업의 끝으로 복구하는 것을 허용합니다. 대량 로그 복구 모델을 사용하여 만든 트랜잭션 로그 백업에는 대량 작업으로 수정된 익스텐트가 포함됩니다. 이 기능은 로그 전달에 대한 지원을 개선하는데, 이것은 사용자가 더 이상 대량 작업이 사용자의 백업을 무효화할 것이라고 걱정할 필요가 없기 때문입니다. SQL Server는 비트맵을 유지 관리하여 수정된 데이터 익스텐트를 추적는데, 이 데이터 익스텐트는 SQL Server가 변경 내용을 식별하는 프로세스를 최적화합니다.

개선된 백업 기능
일반적인 데이터 보호를 단순화하는 복구 모델의 도입 외에도 SQL Server 2000에서는 스냅샷 기술, 차등 백업 및 보안 같은 관리 효율성이 개선되었습니다.

트랜잭션 로그 백업 체인은 끊어지지 않습니다. SQL Server 7.0의 경우, 데이터베이스에 파일을 추가하는 것과 같은 특정 작업에서는 로그 체인이 끊어지므로 후속적인 전체 데이터베이스 백업이 요구되었습니다.
백업 작업은 응용 프로그램이나 다른 관리 작업과는 충돌하지 않습니다. 예를 들어, 백업은 인덱스 만들기와 대량 로드 같은 대량 작업과 동시에 발생할 수 있습니다.
로그 백업과 파일 백업은 동시에 발생할 수 있습니다.
SQL Server 2000에서는 시스템 동작에 상관 없이 무인 백업 작업도 지원됩니다.

SQL Server는 독립 하드웨어 및 소프트웨어 공급업체와 연계하여 스냅샷 백업과 복원 기술을 지원합니다. 스냅샷 백업은 백업을 수행하는 데 서버 리소스의 사용을 최소화하거나 배제합니다. 이것은 가용성이 매우 중요한 중간 크기에서 아주 큰 데이터베이스에 특히 유용합니다. 이 기술의 장점은 다음과 같습니다.

백업은 서버에 거의 아무런 영향을 미치지 않으면서 초단위로 측정되는 아주 짧은 시간에 만들어집니다.
데이터베이스를 빠르게 복원하는 데는 디스크 백업을 사용할 수 있습니다.
다른 호스트가 프로덕션 시스템에는 아무 영향을 주지 않고 백업을 만들 수 있습니다.
보고나 검사를 위해 프로덕션 데이터베이스의 복사본을 즉시 만들 수 있습니다.
스냅샷 백업과 복원은 이런 용도로 디자인된 SQL Server 2000의 기능을 사용하는 타사 하드웨어 및/또는 소프트웨어 공급업체와 공동으로 이루어집니다. 백업 기술을 사용하면 보통 미러링된 디스크 집합을 분할하여 백업되고 있는 데이터의 사본을 즉시 만들 수 있습니다. 복원 시에는 원본을 바로 사용할 수 있습니다. 원본으로 사용하는 디스크는 백그라운드에서 동기화되므로 거의 동시에 복원됩니다.

차등 데이터베이스 백업은 마지막 전체 백업 이후에 변경된 데이터 양과 비례하는 시간에 완성됩니다. 변경된 데이터가 적을수록 백업이 더 빨라집니다. SQL Server 2000에서는 비트맵을 사용하여 가장 최근의 데이터베이스 백업이나 파일 백업 이후에 수정된 데이터 익스텐트를 추적하여 이들의 위치를 효율적으로 찾을 수 있도록 합니다. 또한, SQL Server 2000에서는 파일 차등 백업이 지원됩니다.

백업은 복구 시와 같은 방법으로 작동하면서 가장 최근의 전체 백업 이후에 데이터베이스에서 수정된 변경 내용을 모아둡니다. 백업이 훨씬 빠르기는 하나 변경된 소량의 정보(특히 소량의 데이터만 변경된 대용량 데이터베이스의 경우)만을 기록합니다.

보안을 강화하기 위해 백업 미디어 및 백업 집합에 대해 암호 보호를 구현할 수 있습니다. 이렇게 하면 권한이 없는 사용자가 백업에 내용을 추가하거나 데이터베이스로 복원하는 것을 방지할 수 있습니다.

관리 개선
SQL Server 2000에서는 저장소 엔진의 몇 가지 관리 기능이 향상되었습니다.

데이터베이스 확인
DBCC는 데이터베이스 일관성 확인을 위한 CHECK 명령을 포함하여 다양한 관리 기능을 제공합니다.

SQL Server 7.0 및 SQL Server 2000을 사용해 본 결과 데이터베이스 불일치는 정상 작업 중에 데이터베이스 엔진이나 응용 프로그램에 의해 검색될 수도 있고 검색되지 않을 수도 있는 하드웨어 문제로 인해 생깁니다. 이것은 특히 자주 액세스되지 않는 데이터의 경우에 해당됩니다. 이러한 필요성에 부응하여 SQL Server 2000에서는 Physical_Only라는 검사 모드가 소개되는데, 이것은 대부분의 하드웨어로 인한 문제를 검색하기 위한 것입니다. 이것은 거의 디스크 스캔 속도 정도로 아주 빠르고 리소스를 많이 사용하지 않습니다.

SQL Server 7.0과 함께 시작된 SQL Server 저장소 엔진에서는 기본적인 구조적 개선으로 인해 기본 유지 관리 작업의 일부로 데이터베이스 확인을 실행할 필요가 없습니다. 그러나, Microsoft는 업무 지향 데이터 관리의 중요한 부분으로서 데이터베이스 확인 도구에 최선을 다하고 있습니다. Microsoft에서 추천하는 사항은 다음과 같습니다.

원본으로 사용하는 하드웨어(특히 디스크 하위 시스템)의 신뢰도에 따라 때때로 Physical_Only 검사를 실행합니다.
하드웨어나 소프트웨어 업그레이드 시 또는 원인에 상관 없이 문제가 있다고 느껴지는 경우 같은 중요한 시기에는 전체 데이터베이스 검사를 실행합니다.
Microsoft는 정기 점검의 일환으로 전체 검사를 실행하도록 권장하지는 않습니다.

또한 SQL Server 2000에는 데이터베이스 확인에 대한 중요한 기능 향상이 포함되어 있습니다.

기본적으로 검사는 완전히 온라인 상태에서 이루어집니다. 온라인 검사는 트랜잭션 작업 부하에 별로 영향을 미치지 않습니다. 이 영향은 시스템 로드, 하드웨어 구성 및 tempdb의 속도에 따라 달라집니다. Microsoft는 이 영향을 중간 OLTP 작업 부하(50% CPU)로 15에서 20%에서 측정했습니다. TABLOCK 옵션은 공유 테이블 잠금을 허용하도록 검사를 강제하기 위해 제공됩니다. 이 공유 테이블 잠금을 사용하면 검사를 더 빨리 실행할 수 있지만 업데이트는 할 수 없게 됩니다.
검사는 SQL Server의 인스턴스에 대해 사용자가 설정한 병렬 처리의 최대 한도로 제한된 대칭적 다중 프로세싱(SMP) 컴퓨터에서 병렬로 실행됩니다.
SQL Server 2000의 검사 명령은 SQL Server 7.0에서 소개된 복구 기능을 계속적으로 지원합니다. 어떤 경우에는 오프라인 복구가 백업에서 복원하는 것에 대한 대안을 제공하기도 합니다.

데이터베이스 상태 제어
SQL Server 2000에서는 Transact-SQL을 통해 데이터베이스 상태를 더 많이 제어할 수 있도록 하는 ALTER DATABASE 문에 대한 기능이 향상되었습니다. 이제 ALTER DATABASE 명령의 강력한 제어 기능을 통해 모든 데이터베이스 옵션을 수정할 수 있습니다. sp_dboption과 databaseproperty()는 향후 릴리스에서는 더 이상 업데이트되지 않을 것입니다. Transact-SQL 명령 sp_helpdb와 DatabasePropertyEx()는 사용자의 데이터베이스 상태에 대한 정보를 제공합니다.

다음은 데이터베이스 상태 옵션을 나열하는 테이블입니다.

옵션 종류 사용 가능한 설정
사용자 액세스 SINGLE_USER
RESTRICTED_USER
MULTI_USER
가용성 온라인
오프라인
업데이트 가능성 READ_ONLY
READ_WRITE

또한 SQL Server는 데이터베이스 내의 상태에 맞춰 복원, 복구, 주의 대상 같은 상태를 설정합니다. 데이터베이스 옵션은 ALTER DATABASE 문의 SET 절, sp_dboption 시스템 저장 프로시저 또는 경우에 따라 SQL Server 엔터프라이즈 관리자 등을 사용하여 설정할 수 있습니다.

데이터베이스 상태가 변경되면, 새로운 상태와 일치하지 않는 세션이 종료될 수 있고 해당 트랜잭션이 롤백되는 동안 데이터베이스 상태를 변경하는 세션이 연결된 채로 남아 있습니다. 세션 종료 옵션에 포함되는 것은 다음과 같습니다.

즉시 종료
지정된 시간 경과 후 종료
진행 중인 프로세스가 정상적으로 완료하도록 허용
작업을 검사하여 활성 사용자 세션이 발견되면 상태 변경 무시
다음은 구문의 두 가지 예제입니다.

alter database accting set read_only with rollback immediate alter database accting set single_user with rollback after 60 seconds

자세한 내용은 SQL Server 2000 온라인 설명서의 "데이터베이스 옵션 설정"을 참조하십시오.

시스템 프로세스 ID 및 작업 단위
프로세스를 중지하려고 할 때 도움이 되는 추가 관리 향상 한 가지는 KILL 명령입니다. KILL 명령의 기능이 향상되어 상태 피드백을 제공합니다. 현재 수행 중인 KILL 명령의 상태를 확인하려면 다음을 실행하십시오.

KILL SPID WITH STATUSONLY

다른 KILL 명령에 의해 중지되고 있는 시스템 프로세스 ID(SPID)를 중지하려고 하면 시스템에서는 같은 상태 정보를 반환합니다.

SQL Server 2000에서 MS DTC 트랜잭션은 관련된 연결이나 SPID 없이 존재할 수 있습니다. 그러므로, 연결은 완료할 작업 단위나 트랜잭션을 기다리는 동안 다른 프로세스에 사용할 수 있습니다. MS DTC 트랜잭션 관리자가 작업을 완료했다는 메시지를 보내면 사용자는 트랜잭션을 커밋하거나 롤백할 수 있습니다. 이것을 작업 단위(UOW)라고 하며, 트랜잭션을 위해 MS DTC가 사용하는 트랜잭션 식별자입니다. UOW에는 SPID가 없습니다.

자세한 내용은 SQL Server 2000 온라인 설명서를 참조하십시오.

동적 조정
SQL Server 2000에서 사용 기반 성능 조정은 동적으로 관리되며, 수동 조절을 필요로 하거나 권장하지 않습니다. 정적 매개 변수는 제거되었으나 특정 리소스를 위해 관리 제어는 유지됩니다(예: SQL Server가 사용할 수 있는 메모리 용량에 대한 상위 제한 설정). 이 방법은 평균과 예상값을 기초로 수동으로 계산된 시스템보다 훨씬 정확하고 응답이 빠릅니다. 이 방법을 사용하면 데이터베이스 관리의 디자인 측면에만 집중할 수 있습니다. 일반적인 데이터베이스 시스템에서는 많은 양의 수동 관리와 조정이 필요합니다. 예를 들어, 사용에 맞춰 시스템을 조정하려면 DBA는 시스템에 최적의 이점을 제공한다고 생각되는 정적 설정을 선택하기 위해 오랫 동안 많은 양의 통계를 기록하면서 시스템을 모니터링해야 합니다. 그런 다음 DBA는 시스템을 다시 평가하여 새로운 설정은 어떤 효과를 갖는지 판단하고, 조정 프로세스를 다시 시작합니다.

SQL Server 2000에서는 저장소 엔진에 동적 알고리즘이 도입되었는데, 이것은 서버 사용을 활발하게 모니터링하고 내부적으로 설정을 조절합니다. SQL Server 2000의 동적 피드백과 분석은 설정을 절대 최적 값의 10% 이내로 유지하여(그림 3 참고), 시스템이 보다 잘 조정되고 적응력이 뛰어나도록 합니다.



데이터 저장소 구성 요소
SQL Server 2000은 Windows 2000 운영 체제와 함께 사용할 수 있는 모든 CPU를 통한 처리의 균형을 유지합니다. SQL Server의 전용 인스턴스를 실행하고 다른 응용 프로그램이 같은 리소스에 로드를 만들지 않는 경우, 모든 프로세서를 충분히 사용할 수 있도록 프로세서 관련 설정을 기본값으로 합니다. SQL Server는 쿼리, 인덱스 작성, DBCC 및 기타 작업에 대해 여러 프로세서에 걸친 병렬 처리의 장점을 활용할 수 있습니다.병렬 처리에 대한 자세한 내용은 SQL Server 2000 온라인 설명서의 "병렬도"를 참조하십시오.

SQL Server 2000 Standard Edition은 최대 4개의 프로세서와 2GB의 실제 메모리(RAM)를 지원할 수 있습니다. 엔터프라이즈 버전은 최대 32개의 프로세서와 64GB의 실제 메모리(RAM)를 지원하도록 확장할 수 있습니다.

SQL Server 인스턴스에 대한 메모리의 주요 소스를 메모리 풀이라고 합니다. SQL Server의 인스턴스에서 메모리를 사용하는 거의 모든 데이터 구조는 메모리 풀로부터 할당됩니다. 메모리 풀에서 할당된 개체의 예로는 최근에 읽은 데이터 페이지가 저장되는 버퍼 캐시와 최근의 실행 계획이 보관되는 프로시저 캐시 등이 있습니다.

메모리 풀 안에서의 할당은 매우 동적입니다. 성능을 최적화하기 위해 SQL Server는 여러 영역에 할당된 메모리 풀의 양을 지속적으로 조절합니다. 예를 들어, 저장된 실행 계획의 수가 적으면 데이터 캐시에서 사용할 수 있는 메모리의 양을 늘려 리소스 사용을 최적화도록 메모리 풀이 조절됩니다.

SQL Server 2000은 메모리를 사용하여 가능한 한 디스크 I/O를 최소화하도록 디자인되었습니다. 이렇게 하기 위해 SQL Server는 버퍼 캐시를 사용하여 최근에 참조된 데이터를 실제 메모리(RAM)에 보관하며, 여기에 있는 데이터를 다시 사용합니다. 디스크 I/O를 줄이고 데이터베이스 시스템 속도를 높이는 한 가지 잠재적인 방법은 SQL Server에 사용할 수 있는 실제 메모리(RAM)를 추가하는 것입니다.

일반적으로 메모리 설정은 조정할 필요가 없습니다. 그러나 상황에 따라 메모리 설정을 제어해야 할 수도 있습니다. 예를 들어, 같은 서버에 SQL Server의 여러 인스턴스를 실행하는 경우(특히 장애 조치 클러스터링을 사용하는 경우) 메모리에 특별한 주의를 기울일 필요가 있습니다. 또한 같은 서버에서 SQL Server 이외에 여러 응용 프로그램을 실행하는 경우에는 메모리 사용을 모니터링해야 합니다.



SQL Server 2000은 Windows 2000의 새로운 기능을 사용하여 3GB이상의 실제 메모리(RAM)를 주소 지정할 수 있습니다. 그림 4. 참고 SQL Server 2000 Enterprise Edition은 Windows 2000 Advanced Server 또는 Windows 2000 Datacenter Server가 허용하는 만큼의 메모리를 사용할 수 있습니다.

SQL Server 2000의 대용량 메모리 지원에 대한 자세한 내용은, SQL Server 2000 온라인 설명서의 "AWE 메모리 관리"를 참조하십시오.

파일, 파일 그룹 및 디스크
SQL Server는 데이터와 로그를 디스크 파일에 저장합니다. 기본 설치 시 데이터와 로그 파일은 기본적으로 서버 구성에 지정된 기본 위치에 만들어집니다. 그러나 성능과 관리 효율성을 높이기 위해 다음과 같은 몇 가지 기본 원칙을 적용할 수 있습니다.

가능한 한 여러 디스크, 채널 및 컨트롤러에 데이터를 분산합니다.
일반적으로, 개별 크기에 상관 없이 더 많은 디스크(스핀들)를 가질수록 그리고 컨트롤러와 채널에 더 빨리 액세스할수록 저장소 엔진은 더 빨리 데이터를 읽고 쓸 수 있습니다. 시스템 사용이 많아질수록 실제 드라이브의 다른 집합에 데이터 파일을 저장하여 로그 파일과 데이터 파일을 분리하는 것이 더욱 중요해집니다. 또한 tempdb의 사용이 변경되었으므로 이제는 tempdb를 대용량 디스크 집합(예를 들면, 데이터 파일과 함께 또는 디스크 집합에) 저장해야 합니다.
파일 그룹을 사용하여 사용자의 엔터프라이즈 데이터베이스를 보다 관리하기 쉽도록 합니다.
모든 데이터베이스는 하나의 기본 파일 그룹으로 시작합니다. SQL Server 2000은 추가 파일 그룹 없이도 효과적으로 작업할 수 있으므로 많은 시스템은 사용자 지정 파일 그룹을 추가할 필요가 없습니다. 그러나, 시스템이 확장함에 따라 파일 그룹을 추가로 사용하면 자격 있는 DBA가 수행하고 유지 관리하는 경우 보다 나은 관리 효율성이 제공됩니다.
SQL Server 2000에서 데이터베이스 안의 특정 파일 그룹을 읽기 전용으로 설정하면 해당 파일 그룹의 데이터는 변경할 수 없으나 사용 권한 같은 카탈로그 정보는 여전히 관리할 수 있습니다.

참고 SQL Server 2000에서 비동기 I/O의 수는 이제 데이터베이스 엔진 안에서 동적으로 관리되며, SQL Server 7.0의 경우처럼 사용한 파일 그룹이나 파일 수에는 영향을 받지 않습니다.

데이터베이스 디자인을 구현하거나 최적화하는 경우, 데이터베이스 관리자(데이터베이스 시스템 엔지니어)는 데이터베이스 저장소 구성 요소의 구성, 특히 실제 및 논리 디스크의 레이아웃과 여러 디스크에서의 데이터베이스 파일 배열 등을 고려해야 합니다.

결론
DBA의 입장에서는 성능에 대한 제어와 융통성이 증가하여 데이터베이스 시스템 관리에 대한 통합된 접근 방법으로 자신의 데이터베이스 기술과 경험을 데이터베이스 코드, 디자인 및 저장소 구성 요소를 관리하는 데에만 집중할 수 있게 되었습니다. SQL Server 2000 데이터베이스 엔진은 다양한 데이터베이스 구현에 대해 일반적인 확장성과 융통성을 제공합니다.

추천 자료
전문가와 전문적인 프로세스로 데이터베이스 시스템을 성공적으로 운영하는 데 대한 자세한 내용은 Microsoft Certified Database Administrator 프로그램 과 Microsoft Operations Framework 를 읽어보십시오.

SQL Server 2000 온라인 설명서는 SQL Server 2000에 들어 있습니다. 이 설명서는 http://www.microsoft.com/korea/sql/tech ··· EC%84%9C 온라인으로도 볼 수 있습니다.

하드웨어 및 SQL Server에 대한 자세한 내용은 SQL Server 2000 온라인 설명서의 대용량 서버에서 SQL Server 2000을 참조하십시오.

SQL Server 아키텍처 및 저장소 엔진에 대한 자세한 내용은 SQL Server 2000 온라인 설명서와 Kalen Delaney가 지은 Microsoft SQL Server 2000 내부 를 참조하십시오.

용량 계획 기술에 대한 자세한 내용은 Microsoft SQL Server 2000 관리자 안내서를 참조하십시오.

데이터베이스 관리자용 SQL Server에 대한 자세한 내용은 Microsoft SQL Server 웹 사이트와 MSDN 및 Microsoft TechNet에 있습니다.

개발자용 SQL Server에 대한 기술 정보는 MSDN SQL Server 홈 페이지에 있습니다.

[이 자료는 MSDN 라이브러리에서 가져왔습니다]

이 기사에서는 Duwamish Books, 4단계 응용 프로그램 및 더욱 강력해진 Duwamish 온라인 응용 프로그램이 SQL Server XML 기반 솔루션 집합에 적용되었을 때 만들어지는 아키텍처 개요를 설명합니다

SQL Server XML 및 웹 응용 프로그램 아키텍처
John A. Bocharov


요약: 이 기사에서는 Duwamish Books, 4단계 응용 프로그램 및 더욱 강력해진 Duwamish 온라인 응용 프로그램이 SQL Server XML 기반 솔루션 집합에 적용되었을 때 만들어지는 아키텍처 개요를 설명합니다.


목차

소개
논리적 아키텍처
물리적 아키텍처
통합
영향
사용 권장 사항
결론

소개
Microsoft SQL Server 2000은 SQL Server XML이라는 새로운 XML 기술 계열에서 새로운 기능의 선두 주자로 시장을 석권했습니다. 이러한 기술 제품군은 향상된 기술과 새로운 기능을 바탕으로 SQL Server를 웹에 더욱 친숙한 응용 프로그램으로 만들고 Microsoft .NET 비전에 한 걸음 다가설 수 있도록 합니다.

SQL Server XML은 웹 응용 프로그램 아키텍처를 확장, 향상 및 교체하는 데 사용할 수 있습니다. 새로운 기능은 다음과 같은 두 가지 주요 구성 요소로 구분할 수 있습니다.

데이터베이스 구성 요소. 데이터베이스에서 XML을 읽고 처리하고 쓸 수 있도록 합니다.


SQL Server XML 인터넷 서버 API(ISAPI) 응용 프로그램. HTTP를 통해 데이터베이스에 액세스할 수 있도록 합니다.
이러한 구성 요소 중 하나 또는 둘 다를 사용하여 아키텍처상 흥미로운 몇 가지 가능성을 제시하게 됩니다. 새 도구의 성능과 유연성을 테스트하기 위해 우리는 이미 입증된 Duwamish 온라인의 논리적 아키텍처를 선택하여 SQL Server XML 기반 솔루션 집합에 적용시켰습니다. 연구에 완벽을 기하기 위해 좀 더 단순한 Duwamish Books, 4단계 응용 프로그램과 좀 더 강력한 Duwamish 온라인 응용 프로그램을 모두 테스트에 사용했습니다. 이렇게 해서 만들어진 아키텍처 개요는 다음과 같습니다.





논리적 아키텍처
응용 프로그램에 관계 없이, 응용 프로그램 인수화를 가능하게 하는 구성 개념인 "논리적 아키텍처"와 응용 프로그램 구현 방법을 나타내는 물리적 아키텍처 등 두 개의 아키텍처가 항상 연구 대상이 된다는 점을 염두에 두어야 합니다. 드물지만 간혹 이 두 아키텍처를 정확하게 구분해야 하는 경우가 있으므로 이러한 구분이 필요합니다. 곧 알게 되겠지만 특정한 논리적 디자인에 적합한 물리적 아키텍처는 상황에 따라 변하게 됩니다.

Duwamish 온라인 및 그 후속 버전을 만들면서 우리는 Microsoft n 계층 지침에 기반하는 논리적 아키텍처를 결정했습니다. 이 아키텍처는 웹 응용 프로그램에 의해 수행되는 일반적인 작업으로 구성되며 Duwamish 온라인에 한정된 것이 아닙니다.



응용 프로그램은 다섯 개의 논리 계층으로 구분됩니다. 클라이언트로부터 가장 먼 위치에 데이터 계층이 있으며 응용 프로그램이 필요로 하는 정보를 저장합니다. 바로 위에 데이터 액세스 계층이 있고 이는 데이터를 데이터베이스 내부 표현 방식으로부터 추상화하고 모든 데이터베이스 작업에 공통되는 루틴을 포함합니다. 데이터 액세스 계층은 업무 논리 계층에서 직접 사용합니다. 업무 논리 계층은 상위 계층으로부터 트랜잭션 세부 구현 내용 및 논리를 숨김으로써 업무 트랜잭션을 추상화합니다. 아키텍처에서 바로 다음 단계는 워크플로 계층입니다. 이것은 업무 외형을 의미하며 표시 계층에 facade로 알려진 간단한 인터페이스를 제공합니다. 내부적으로 워크플로 계층은 상태를 관리하고 업무 논리 계층에서 제공하는 원자 연산을 통해 복잡한 워크플로를 완성합니다. 마지막 단계에는 표시 계층이 있으며 워크플로 계층에서 반환한 결과 값을 사용자가 볼 수 있도록 변환합니다. 이 변환 작업은 XSL 스타일시트를 적용하여 결과를 HTML로 변환시키는 것처럼 간단할 수도 있고 또는 전화선에서 결과를 읽어들이는 음성 알고리즘처럼 복잡할 수도 있습니다.

이제 이러한 논리 아키텍처로부터 파생된 몇 가지 물리적 아키텍처를 살펴보겠습니다.


물리적 아키텍처
로드 분산
SQL Server XML을 사용하면 데이터베이스에서 단순히 데이터를 읽고 쓰는 것 이상의 일을 할 수 있습니다. XML은 저장 프로시저로 하여금 고도로 구조화된 다량의 데이터 처리를 가능하게 합니다. 관련 정보는 XML을 통해 저장 프로시저로 보내지고, 이 때 XML은 COM+ 또는 스크립트가 아닌 저장 프로시저로서 업무 논리 또는 워크플로 구현을 가능하게 합니다. 이것은 이제 사용자가 더 많은 응용 프로그램 처리를 데이터베이스 계층으로 이동시킬 수 있다는 것을 뜻합니다. 이 방법을 택할 경우 데이터베이스는 응용 프로그램에서 최소의 확장 가능 부분임을 명심하십시오.

데이터베이스와 웹 서버 사이에서 응용 프로그램이 수행해야 할 처리를 분산시키는 방법을 결정하는 것은 중요합니다. 이러한 결정은 응용 프로그램이 필요로 하는 하드웨어와 소프트웨어에 영향을 끼치며 응용 프로그램을 개발하는 데 필요한 기술 종류 및 응용 프로그램을 배포하고 업데이트하며 유지하는 절차에 영향을 끼칠 것입니다. 단순화를 목적으로, 웹 서버가 대부분의 작업을 수행하는 서버 구성을 "top-heavy"라 하고, 반대로 데이터베이스 서버가 대부분의 작업을 수행하는 서버 구성을 "bottom-heavy"라 명명하겠습니다.

다음과 같은 두 가지 요소로 인해 "top-heavy" 서버 그룹이 대부분의 응용 프로그램 모델로 선택됩니다.

비용. 데이터베이스 서버의 소프트웨어와 하드웨어는 웹 서버의 소프트웨어와 하드웨어보다 비용이 비쌉니다.


확장성. SQL Server 2000의 데이터베이스 확장성은 SQL Server 7.0에 비해 향상되었지만 새로운 하드웨어의 모든 성능을 충분히 사용하기 위해서는 세심한 계획과 효과적인 유지 관리 계획이 필요합니다.
이러한 이유로 "bottom-heavy" 서버 구성에 기반한 아키텍처에 대한 논란이 여전히 남아 있습니다.

Microsoft n 계층의 물리적 아키텍처
비교를 위해 SQL Server XML을 사용하지 않는 Duwamish 온라인(http://www.duwamishonline.com/(영문 사이트))의 물리적 아키텍처를 살펴보겠습니다. Duwamish 온라인은 방금 설명한 논리적 아키텍처를 최대한 구현하도록 설계되었습니다. 각 계층이 각기 한 종류의 논리적 작업을 수행하도록 되어 있지만, 그 범위 이상의 기능 분산이 일어납니다. 예를 들어, 어떤 업무 논리는 성능 향상을 위해 데이터베이스의 저장 프로시저에 의해 실행됩니다. Duwamish Books, 4단계에 익숙한 독자들은 아키텍처가 거의 변경되지 않았음을 곧바로 깨닫게 될 것입니다.



이 아키텍처는 작업에 가장 적합한 기술을 사용하여 각각의 구성 요소가 특정 작업 유형에 전문화될 수 있도록 합니다. 예를 들어, 캐시는 최대 성능을 위해 C++로 작성되고 표시 논리는 Active Server Pages (ASP) 및 XSL을 사용하여 처리되고 워크플로, 업무 논리 및 데이터 액세스는 Microsoft Visual Basic®에 의해 수행됩니다. 또한 구성 요소 및 데이터베이스 작업은 Transact SQL(T-SQL)에서 처리됩니다. 기술 범주로 구분되지 않은 계층은 COM+ 구성 요소별 구현으로 구분됩니다. 이러한 유연성에 대한 대가는 다양한 계층이 함께 작동할 수 있어야 한다는 것입니다. 교차 환경 디버깅은 쉬운 작업이 아니며 특정 환경에서 안전한 데이터를 대상 환경에 맞게 다시 서식 지정하는 데 주의가 필요합니다. 예를 들어, 문자열 "a < b"는 데이터베이스에는 어렵지 않게 저장이 되지만 이스케이프시키지 않고 XML 파일에 적용하면 대괄호 불일치가 발생하여 파서를 엉망으로 만듭니다.

읽기 측면의 물리적 아키텍처
Duwamish 온라인은 응용 프로그램 전반에 걸쳐 단일의 물리적 아키텍처를 사용합니다. 반대로 SQL Server XML 버전은 읽기와 쓰기에 대해 서로 다른 두 개의 보완적인 물리적 아키텍처를 사용합니다. 지금 같은 경우에는 두 가지 사용 사례가 각각 다른 처리 방법을 필요로 하기 때문에 후자가 적합합니다.

참고 http://msdn.microsoft.com/voices/news/sqlxml.asp(영문 사이트)의 전체 아키텍처 다이어그램을 참조하십시오.


SQL Server XML 기술은 최대 성능을 위해 데이터베이스 계층에서 표시 계층에 이르기까지 모든 계층에서 사용됩니다. SQL Server XML ISAPI 응용 프로그램은 웹 계층 ASP를 대치합니다. ISAPI 응용 프로그램 및 SQLOLEDB 공급자는 코드 크기와 개발 시간을 감소시켜 데이터 액세스를 자동화합니다. 이러한 성능을 달성할 경우 ASP의 견고한 개체 모델에 손실을 입고 이로 인해 유연성도 떨어지게 됩니다.

이제부터는 더욱 미묘한 아키텍처의 기능에 대해 살펴보겠습니다. 표시 계층은 이제 XSL 스타일시트에 의해 독점적으로 처리됩니다. 데이터베이스로부터 반환된 데이터는 XML이기 때문에 많은 이해가 될 것이고 XSL을 사용하면 기술 독립적이라는 이점을 추가로 얻을 수 있습니다. 즉 템플릿, ASP 페이지 및 COM+ 구성 요소에서 동일한 스타일시트를 만들 수 있습니다. 템플릿은 워크플로 계층을 구성합니다. 템플릿은 XML 명령 파일이며, 데이터 추상화와 데이터 보안 수준을 제공하는 동시에 HTTP를 통해 데이터베이스에 빠르게 액세스할 수 있도록 하여 데이터 구동 방식의 동적인 웹 페이지를 생성합니다. 템플릿은 주로 저장 프로시저를 사용하여 데이터에 액세스하지만 XDR(XML Data Reduced) 스키마를 사용할 수도 있습니다. XDR 스키마는 데이터베이스 개체를 XML 요소에 매핑하는 직관적인 구문 및 XPath(XML Path Language) 사용 기능을 제공합니다.

응용 프로그램에서 읽기 전용 작업은 데이터 액세스 루틴에 쉽게 통합되는 최소량의 업무 논리를 가지고 있기 때문에 이 아키텍처에는 별도의 업무 논리 계층이 없습니다. 이러한 응용 프로그램이 아닌 경우에 업무 논리 계층은 데이터베이스에서 저장 프로시저 집합으로 효과적으로 구현될 수 있습니다.

가장 많이 달라진 부분은 단연코 데이터베이스 계층이라고 할 수 있습니다. 새로운 기능 집합을 통해 저장 프로시저가 XML을 직접 읽고 쓸 수 있게 되었습니다. 사실 모든 데이터 검색은 XML 통해 이루어집니다.



쓰기 측면의 물리적 아키텍처

SQL Server XML 템플릿은 고도로 간소화되어 가능한 한 효율적으로 HTTP를 통해 데이터베이스에 액세스할 수 있도록 합니다. 이것을 실행하기 위해서는 기능 집합을 어느 정도 제한해야 합니다. 템플릿에서 찾을 수 없는 기능을 요구하는 경우에는, SQL Server 소유 ISAPI 응용 프로그램은 ASP, ASP와 COM+의 조합 또는 사용자 지정 ISAPI 응용 프로그램으로 대치됩니다.

이 섹션에서 설명하는 아키텍처는 사용자 페이지가 다음과 같은 사항을 실행해야 할 경우에 적합합니다.

다중 서버의 데이터베이스를 액세스하는 경우


설계 시점에서는 알려지지 않은 서식을 갖는 HTTP 요청을 처리하는 경우


COM/COM+ 개체를 호출하는 경우


COM+ 트랜잭션을 사용하는 경우


지불 공급자와 같은 응용 프로그램 또는 인터넷 웹 서비스에 연결하는 경우


웹 계층 코드는 데이터 액세스, 업무 논리, 워크플로 및 표시 계층 등 네 계층의 응용 프로그램 기능을 나타냅니다. 응용 프로그램을 개발할 때는 아키텍처에 맞춰 이 코드를 요소화하십시오. 이렇게 하면 사용자 코드의 가독성을 높일 수 있으며 결과적으로 유지 관리가 용이해 집니다. ASP만을 독점적으로 사용하는 경우에 스크립트 클래스는 매우 효율적입니다. 업무 논리 또는 워크플로 계층에서 많은 양의 복잡한 처리를 해야 하는 경우, COM+ 구성 요소를 사용하면 속도가 훨씬 더 빨라집니다. 반대로 처리할 양이 적을 때는 스크립트를 사용하는 것이 더 빠를 수 있습니다.

이 아키텍처를 새롭고 흥미롭게 하는 것은, 데이터 계층에서부터 표시 계층에 이르기까지 모든 계층이 XML을 사용하여 정보를 전송하고 저장한다는 것입니다. 데이터베이스의 저장 프로시저는 새로운 기능을 사용하여 XML을 읽고 씁니다. 데이터 액세스 계층은 데이터베이스와의 효율적인 XML 기반 통신을 위해 ADO 2.6 스트림을 사용합니다.

더욱 새로운 방법은 중간 계층의 일부를 데이터베이스 계층쪽으로 이동시키는 것입니다.



데이터베이스 중심 아키텍처
Duwamish 온라인 아키텍처는 데이터베이스가 확장성이 가장 떨어지는 부분이기 때문에 여기에 대해서는 가능한 한 적은 작업을 수행한다는 가정을 기반으로 합니다. 배포 분할 보기와 같은 새로운 기능은, 데이터베이스의 확장성을 증진시키고 개발자에게 작업 대부분을 어디에서 수행할 것인가에 대한 선택 기회를 주면서 작업 부하를 다중 서버에서 공유할 수 있도록 합니다.

SQL Server XML 아키텍처와 함께 "bottom-heavy" 서버 그룹(데이터베이스 측면에 치중)을 사용하기로 선택했다면 다른 대안은 n 계층 구성 요소의 계층화와 유사한 방식으로 데이터베이스의 저장 프로시저를 계층화하는 것입니다. 이렇게 하는 경우, 적합한 데이터 구조를 선택하고 가능한 한 중복 코드를 피하는 것과 같은 좋은 프로그래밍 습관이 요구됩니다.



이 아키텍처의 표시 계층은 데이터베이스의 저장 프로시저에 액세스하기 위한 코드도 포함합니다. 이 코드는 일반적인 데이터 액세스 계층에서 볼 수 있는 코드와 동일합니다. 그러나 이러한 루틴은 워크플로 계층에서 제공한 부분을 호출하기 때문에 이 부분을 데이터 액세스 계층이라 부르는 것은 오해의 소지가 있습니다.

저장 프로시저를 사용하여 개발할 때 주의해야 할 함정이 있습니다. 먼저 계층에 공통되는 작업을 실행한 뒤 다음 단계를 진행하려면 어떤 코드 경로를 따를 것인가를 결정하는 전환 논리를 수행하는 지능적인 저장 프로시저를 사용하는 디자인을 먼저 고려해 봅시다. 워크플로 계층에서의 "지능적인 프로시저" 호출은 몇 가지 다른 작업 중 하나에 해당합니다. 이런 프로시저는 다음과 같을 수 있습니다.

CREATE PROCEDURE /* 워크플로 작업을 수행하는 지능적인 프로시저입니다. */ DoWorkflow /* Action는 이 호출에 가능한 몇 가지 작업 중 하나를 선택하는 데 사용합니다. */ @Action nvarchar(255), /* SomeOtherParameters는 워크플로에서 사용할 수 있는 다른 입력에 대한 자리 표시자입니다. */ @SomeOtherParameters ntext AS /* 워크플로에 공통적인 작업을 수행합니다. */ Execute SomeCommonWorkflowOperations If @Action = N'Action1' BEGIN /* Action 1 수행 */ Execute BusinessLogicAction1 END Else If @Action = N'Action2' BEGIN /* Action 2 수행 */ Execute BusinessLogicAction2 END GO

처음으로 프로시저가 호출될 때, SQL Server는 가장 먼저 실행할 코드 경로에 대한 실행을 최적화합니다. 비록 더 비용이 많이 들거나 또는 더 빈번히 사용된다 할지라도 남은 코드 경로는 더 비효율적으로 실행됩니다.

모든 코드 경로가 최상의 실행을 하고 있는지 확인하려면 각 작업에 별도의 프로시저를 만들고 가능한 모든 전환 논리를 피합니다. 코드 중복을 막으려면 어떤 계층에서 여러 작업이 공유하는 기능은 별개의 프로시저에 두어야 합니다. 이렇게 설계하면 많은 수의 프로시저가 생기게 되지만 최적화 측면에서 본다면 응용 프로그램의 효율성이 향상됩니다.


통합
웹 개발에 있어 뛰어난 점은 구현의 세부 사항이 사용자로부터 숨겨진다는 것입니다. 따라서 이 기사에서 설명한 아키텍처는 탁월한 사용자 환경을 통해 단일 응용 프로그램에 쉽게 결합될 수 있습니다. 다음은 사용자 응용 프로그램의 다른 부분을 쉽게 통합할 수 있도록 하는 몇 가지 지침입니다.

응용 프로그램에서 XML 사용. XML은 모든 기술에 전반적으로 사용되며 XSL 스타일시트를 사용하여 쉽게 변환되고 어디에나 어려움 없이 저장될 수 있습니다. SQL Server XML을 사용하면 응용 프로그램에서 더욱 쉽게 XML을 사용할 수 있습니다.


가능한 모든 코드를 요소화.
XSL 스타일시트를 사용하여 XML을 변환합니다. 동일 XSL 스타일시트는 템플릿, COM+ 구성 요소 및 스크립트 간에 쉽게 공유할 수 있습니다.


사용자 스크립트로 여러 기능을 수행하는 경우 스크립트 클래스를 사용하여 코드를 요소화합니다.


데이터베이스쪽에서는 데이터 액세스에 항상 저장된 프로시저를 사용합니다. 저장 프로시저는 유지 관리하기도 쉬울 뿐 아니라 컴파일되지 않은 SQL 쿼리보다 훨씬 빨리 실행됩니다.


영향
이 섹션에서는 새로운 아키텍처를 사용하는 것이 사용자 응용 프로그램의 기능 및 성능에 어떠한 영향을 미치는지에 대해 설명합니다.

프로그램 가능성
프로그램 가능성은 응용 프로그램 코드화의 용이성을 말합니다. 이것은 종종 기능 집합과 비교할 때 응용 프로그램을 개발하는 시간으로 적용됩니다. 그 예로 Duwamish 온라인 응용 프로그램을 들 수 있습니다. 응용 프로그램의 다섯 계층은 완전히 다른 기술 집합을 기반으로 만들어집니다. 표시 계층은 캐시 구성 요소에 대해 C++를 사용하고 XML, XSL 및 ASP와 같은 웹 기술도 사용합니다. 워크플로, 업무 논리 및 데이터 액세스 계층은 Visual Basic COM+ 구성 요소이며, 데이터베이스의 저장 프로시저는 T-SQL로 작성됩니다. 많은 기술을 사용하면 개발자는 각 작업에 최상의 기술을 선택할 수 있다는 장점이 있습니다. 그러나 이 모든 구성 요소가 효과적이고 효율적으로 작동될 수 있도록 하는 것은 상당히 어려운 작업입니다. 구성 요소가 각기 다른 프로그래밍 언어를 사용하여 여러 다른 도구에서 개발되는 경우 구성 요소 간 추적 및 디버깅은 더욱 어려워집니다.

사용자 응용 프로그램에서 SQL Server XML을 사용하면 본질적으로 다른 기술로 작업하는 경우에 발생되는 문제점을 최소화할 수 있습니다(XSL은 여기서 제외됩니다. XSL은 SQL Server XML에 속해 있지는 않지만 SQL Server XML 템플릿에 쉽게 통합됩니다). 계층은 마찰을 최소화하여 함께 작동합니다. 모든 중간 데이터가 XML이기 때문에 계층 간의 디버깅은 더욱 쉬워지며 어떤 컴파일도 수반되지 않습니다. 그러나 프로그램 가능성의 가장 큰 장점은 코드 크기를 대폭 줄일 수 있다는 것입니다. Duwamish Books, 4단계의 SQL Server XML 버전은 원래 코드 크기의 1/10 정도에 해당하는 COM+ 버전과 동일한 결과를 가집니다. 이 결과는 데이터 액세스, XML 변환, XSL 변환 및 데이터 캐싱에 대한 SQL Server XML의 기본 제공 기능에 의해 촉진됩니다.

그럼에도 불구하고 XSL을 위한 효과적 디버깅 도구가 없고, 특히 Microsoft Visual Studio®에서 제공한 상호 언어 디버깅 기능을 비교해 볼 때, 다른 새로운 기술을 위한 디버깅 도구가 상대적으로 미비하기 때문에 이러한 장점이 다소 줄어듭니다.

관리 효율
SQL Server XML 응용 프로그램은 쉽게 배포할 수 있습니다. 웹 계층에서 실행되는 코드인 경우 파일은 간단히 대상 디렉터리에 복사되고 구성 유틸리티가 한 번 실행되면 적합한 가상 디렉터리를 설정할 수 있습니다. 오래된 파일을 교체하기만 하면 업데이트가 수행됩니다. SQL Server Enterprise Manager를 사용하면 데이터베이스 개체를 쉽게 관리할 수 있습니다.


성능

사용 권장 사항
모든 새로운 기술에 대한 가장 중요한 질문은 언제 사용할 것인가에 대한 것입니다. SQL Server XML이 모든 인터넷 문제에 대한 궁극적인 해결책은 아니지만 코드 크기 및 개발 시간의 대폭 감소, 성능 향상, 유지 관리의 용이성 등 몇몇 시나리오에서는 상당히 유용합니다. 다음에 요약되어 있듯이 이 새 기술의 두 가지 주요 구성 요소인 데이터베이스와 ISAPI 응용 프로그램은 다른 사용 시나리오를 가지고 있습니다.

SQL Server XML 데이터베이스 서버 구성 요소는 거의 모든 응용 프로그램에서 사용됩니다. 데이터베이스에서 XML을 사용하도록 기존 응용 프로그램을 변환하는 작업도 유용하며 다음과 같은 장점이 있습니다.

지역화를 쉽게 함(XSL 사용)


플랫폼 및 기술 독립적


XML 데이터의 캐싱을 쉽게 함


오프라인/연결이 끊긴 응용 프로그램 사용 기능


웹 서비스의 포함 또는 생성을 쉽게 함


다른 응용 프로그램과의 상호 운용성
새로운 기술의 웹 인터페이스 구성 요소는 더욱 전문화되어 있습니다. 이 구성 요소는 데이터베이스에 빠르고 효율적으로 액세스할 수 있으며 XSL 스타일시트를 통해 데이터 구동 방식의 페이지를 쉽게 만드는 기능을 제공합니다. 이러한 장점은 사소한 것이 아닙니다. 테스트에서 보면 캐시가 없는 Duwamish 온라인 SQL Server XML 카탈로그 검색이 캐시가 있는 Duwamish 온라인보다 15% 정도 성능이 뛰어납니다. 앞서 SQL Server 2000의 기술 미리보기 버전에서 행했던 실험을 보면 SQL Server XML ISAPI 캐시가 성능을 상당히 향상시킬 수 있다는 것을 알 수 있습니다. 하지만 사용자 응용 프로그램이 다음과 같은 것을 포함하고 있는 경우 ASP 중간 계층 사용을 고려해야 합니다.

"데이터 추상화와 무관한 광범위한 업무 논리 루틴". 이러한 루틴을 어디에 둘지에 대한 두 개의 옵션이 있으며 데이터베이스 내의 저장 프로시저에 두거나 XSL 내의 스크립트에 둘 수 있습니다. 스크립트는 효율성 측면이 불확실하고 구조적 쿼리 언어(SQL) 또한 이러한 루틴에 대한 최적의 언어는 아닙니다.


"특히 결과 집합에 대한 광범위한 문자열 조작". 데이터베이스에 저장된 문자열을 XML 또는 HTML을 위해 이스케이프 처리하는 것은 예외입니다. SQL Server 2000의 새로운 기능에 의해 이 작업은 자동으로 실행됩니다. 자세한 정보는 SQL Server 온라인 설명서(XML 및 인터넷 지원\ XML 데이터 검색 및 쓰기 \ FOR XML을 사용한 XML 설명서 검색 \ EXPLICIT 모드 사용 \ F. cdata 지시문 지정)를 참조하십시오.


"많은 양의 HTML 입력". 템플릿 제한으로 인해 설계 시점에서 서식이 알려진 HTTP 요청으로부터 일부 정보를 검색할 수 없습니다. ASP 페이지를 사용하는 경우에는 문제 없이 완료됩니다.
주의! SQL Server 2000은 URL을 통하여 데이터베이스에 직접 액세스할 수 있도록 합니다. 이 기능은 무엇보다도 동적인 템플릿을 허용하게 되므로 아키텍처가 가지고 있는 많은 문제를 해결하는 데 도움이 됩니다. 그러나 이 기능을 사용하면 데이터베이스를 삭제하는 쿼리도 실행되도록 하므로 이 기능을 사용하는 경우에는 데이터베이스 보안이 완벽한지 확인해야 합니다.


결론
SQL Server XML은 데이터베이스로부터 직접 XML을 검색할 수 있게 허용하여 사용자 응용 프로그램 종단 간에 XML을 사용하는 새로운 자극제가 되었습니다. 새로운 ISAPI 응용 프로그램의 성능은 뛰어나지만 사용자 응용 프로그램의 일부 사용 사례에는 적합하지 않을 수 있습니다.

[이 자료는 MSDN 라이브러리에서 가져왔습니다]