Backups

SQL Server 2005 Backups In the opening chapter of Craig Mullin's book, Database Administration, he says "In many ways, business today is data". Within most organizations the person responsible for protecting data is the database administrator€¦ you. That's right; the entire business is in your capable hands, running on that server that doesn't ever crash, with all those end users that don't ever make mistakes using applications, built by those developers who write faultless code the first time, every time, with the able assistance of that new co-op that has 'sa' privileges thanks to your boss. OK. Stop crying. There are things you can do to protect the SQL Server data under your care and one of the most important is running regular database backups. NOTE:



The source code bundle contains the SQL Backup scripts described in this article. To obtain the scripts, simply click the "CODE DOWNLOAD" link in the box to the right of the article title.

Backups Microsoft, in SQL Server Books Online, defines backups as:



A copy of data that is used to restore and recover data after a system failure

SQL Backups can be created a number of ways and can incorporate all or some of the data, as well as some part of the transaction log. While this article is focused on 2005 syntax, most of the concepts are applicable to 2000. This is a huge topic. At best, I'm going to scratch the surface and give you enough information so you won't start crying again. After reading this, you should be able to set up a reasonable set of backups for your system. Recovery Models In order to begin working on backups, the business needs define a database recovery model. In essence, a recovery model defines what you're going to do with the transaction log data. There are three recovery models: Full, Simple and Bulk Logged. These are pretty easy to define:    Simple â€― in simple recovery mode, the transaction log is not backed up so you can only recover to the most recent full or differential backup. Full â€― in full recovery mode you backup the database and the transaction log so that you can recover the database to any point in time. Bulk Logged â€― in bulk logged mode, most transactions are stored in the transaction log, but some bulk operations such as bulk loads or index creation are not logged.



The two most commonly used modes are Simple and Full. Don't necessarily assume that, of course, you always need to use Full recovery to protect your data. It is a business decision. The business is going to tell you if you need to recover to a point in time or if you simply need the last full backup. It's going to define if your data is recoverable by other means, such as manual entry, or if you have to protect as much as possible as it comes across the wire. You use Simple recovery if you can afford to lose the data stored since the last full or differential backup and/or you just don't need recovery to a point in time. In Simple mode, you must restore all secondary



1



read/write file groups when you restore the primary. You use Simple mostly on secondary databases that are not an absolute vital part of the enterprise or reporting systems, with read only access so there isn't a transaction log to worry about anyway. You use Full if every bit of the data is vital, you need to recover to a point in time or, usually in the case of very large databases (VLDB), you need to restore individual files and file groups independently of other files and file groups. With both Simple and full recovery models, you can now run a Copy-Only backup which allows you to copy the database to a backup file, but doesn't affect the log, differential backup schedules or impact recovery to a point in time. I'll try to drill down on as many of these topics as possible through the article, but not the files and file-groups. Working with Simple Recovery Enough talk. Let's get down to running backups. Let's assume that we're in Simple recovery on a small to mid-sized database. I'm going to use AdventureWorks for all the sample scripts. To set it to simple recovery: ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE Your simplest backup strategy is to run, at regular intervals, the following SQL Server backup command, which will perform a full backup of the database: BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backups\AdventureWorks.BAK' What's with all the typing you ask? Don't we have GUI tools to handle the work for us? Yes, most simple backups can be performed using SQL Server Management Studio. However, if you want to learn and understand what Management Studio is doing for you, or if you want some fine grained control over what is backed up, how and where, then you're going to have to break out the keyboard and put away the mouse. The above command will precipitate a basic backup to disk. Most DBAs I know backup to file and then scrape the files onto a tape or some other media. This is because files on disk are simple and quick to recover, whereas media can sometimes be a bit of a pain. For example, we generally have two to three days worth of backups on our file systems for immediate recovery. We only go to the tape systems if we need to run restores for older backups. What did that command do? It made a copy of all the committed data in the database. It also copied uncommitted log entries. These are used during recovery to either commit or rollback changes that occurred to the data during the backup process. Copy-only backups Normally, backing up a database affects other backup and restore processes. For example after running the previous command, any differential backups (a backup that only copies data changed since the last backup) would be using this as the starting point for data changes, not the backup you ran last night. As noted earlier, SQL 2005 introduces a new concept to backups, COPY_ONLY backups, which allow us to keep from interrupting the cycle:



2



BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backups\AdventureWorks.bak' WITH COPY_ONLY; Already we've found one of those more granular moments when the Management Studio wouldn't help you. If you want a copy only backup, you have to use the command line. Differential backups Let's assume for a moment, that we're still in simple recovery, but we're dealing with a larger database, say something above 100 GB in size. Full backups can actually start to slow down the process a bit. Instead, after consultation with the business, we've decided to do a weekly full backup and daily differential backups. Differential backups only backup the data pages that have changed since the last full backup. Following is the SQL backup command to perform a differential backup: BACKUP DATABASE AdventureWorks TO DISK = 'C:\backups\AdventureWorks.bak' WITH DIFFERENTIAL; Now, if we had to restore this database, we'd first go to the last full backup, restore that, and then restore the differential backups in order (more on that later). You might have noticed, if you're trying these commands out as you go along, that we have not been changing the backup file names each time. Yet we haven't run into any errors. When running backups as we've done, SQL Server treats the file like a backup device and simply keeps appending the backups to the file.



If you want to simply overwrite the existing file you'll need to modify your backup statements: BACKUP DATABASE Adventureworks TO DISK = 'C:\backups\AdventureWorks.bak' WITH INIT; There are a number of other backup options that I won't be detailing here. Read the books online to see details on BLOCKSIZE, EXPIREDATE, RETAINDAYS, PASSWORD, NAME, STATS, and so on. You can also run a statement that will check the integrity of a database backup. It doesn't check the integrity of the data within a backup, but it does verify that the backup is formatted correctly and accessible. RESTORE VERIFYONLY FROM DISK = 'C:\backups\Adventureworks.bak' Full recovery and log backups



3



We've primarily been working on a database that was in Simple recovery mode (this used to be called Truncate Log on Checkpoint). In this mode, we do not backup the transaction logs for later recovery. Every backup under this mechanism is a database backup. Log backups are simply not possible. However, you've only protected the data as of the last good backup, either full or differential. Let's change our assumptions. Now we're dealing with a large, mission critical application and database. We want to be able to recover this database up to the latest minute. This is a very important point. In theory, since the log entries are being stored and backed up, we're protected up to the point of any failure. However, some failures can cause corruption of the log, making recovery to a point in time impossible. So, we have to determine what the reasonable minimum time between log backups will be. In this case we can live with no more than 15 minutes worth of lost data. So, let's start by putting our database in FULL recovery mode: ALTER DATABASE AdventureWorks SET RECOVERY FULL Then, on a scheduled basis, in this case every 15 minutes, we'll run the SQL backup command for the transaction log: BACKUP LOG Adventureworks TO DISK = 'C:\backups\AdventureWorks_Log.bak'; This script will backup committed transactions from the transaction log. It has markers in the file that show the start and stop time. It will truncate the log when it successfully completes, cleaning out from the transaction log the committed transactions that have been written to the backup file. If necessary, you can use the WITH NO_TRUNCATE statement to capture data from the transaction log regardless of the state of the database, assuming it's online and not in an EMERGENCY status. This is for emergencies only. Note that we are not using the INIT statement in this case, but you can do so if you choose. When doing log backups, you've got options: 1. Run all the backups to a single file, where they'll stack and all you have to do, on restore (covered later), is cycle through them. 2. Name the backups uniquely, probably using date and time in the string. In that latter case, safety says, use INIT because you're exercising maximum control over what gets backed up where, and you'll be able to know exactly what a backup is, when it was taken and from where based on the name. This is yet another place where operating backups from the command line gives you more control than the GUI. We've used both approaches in our systems for different reasons. You can decide what is best for your technology and business requirements. Most of the options available to the database backup are included in Log backup, including COPY_ONLY. This would allow you to capture a set of transaction data without affecting the log or the next scheduled log backup. This would be handy for taking production data to another system for troubleshooting etc.



4



If you have your database set to FULL Recovery, you need to run log backups. Sometimes, people forget and the transaction log grows to the point that it fills up the disk drive. In this case, you can run: BACKUP LOG Adventureworks WITH NO_LOG; Attaching NO_LOG to the log backup, and not specifying a location for the log, causes the inactive part of the log to be removed and it does this without a log entry itself, thus defeating the full disk drive. This is absolutely not recommended because it breaks the log chain, the series of log backups from which you would recover your database to a point in time. Microsoft recommends running a full backup immediately after using this statement. Further, they're warning that this statement may be deprecated in a future release. Restoring Databases As important as SQL Server backups are, and they are vital, they are useless without the ability to restore the database. Restoring a full database backup Restoring a full database backup is as simple as it was to create: RESTORE DATABASE Adventureworks FROM DISK = 'C:\Backup\AdventureWorks.bak'; It's really that simple â€― unless, as we did originally, we are backing up everything to a file as if it were a backup device. In that case, you'll need to specify which file within the "device" you're accessing. If you don't know which file, you'll need to generate a list: RESTORE HEADERONLY FROM DISK = 'C:\Backup\Adventureworks.bak'; This will give you the same list as I showed above from Management Studio. So now, if we wanted to restore the second file in the group, the COPY_ONLY backup, you would issue the following command: RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Backup\Adventureworks.bak' WITH FILE = 2; Unfortunately, if you're following along, you may find that you just generated this error: Msg 3159, Level 16, State 1, Line 1 The tail of the log for the database "AdventureWorks" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.



5



What this means is, that your database is in full recovery mode, but you haven't backed up the "tail of the log", meaning the transactions entered since the last time you ran a backup. You can override this requirement if you change the previous syntax to: RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Backups\Adventureworks.bak' WITH FILE = 2, REPLACE; That's the first time we've stacked the WITH clauses (WITH FILE=2 and WITH REPLACE is represented as WITH FILE=2, REPLACE), but it won't be the last. Read through the books online. Most of the WITH clause statements can be used in combination with the others. What happens if we want to restore to a different database than the original? For example, we want to make a copy of our database from a separate backup. Maybe we want to move it down to a production support server where we are going to do some work on it, separate from the production copy of the database. If we take the simple approach, well, try this: RESTORE DATABASE AdventureWorks_2 FROM DISK = 'C:\Backups\Adventureworks.bak' WITH FILE = 2; In this case, you should see a whole series of errors relating to files not being overwritten. You really can create new databases from backups, but if you're doing it on a server with the existing database, you'll need to change the location of the physical files using the logical names. In order to know the logical names of the files for a given database, run this prior to attempting to move the files: RESTORE FILELISTONLY FROM DISK = 'C:\Backups\Adventureworks.bak' WITH FILE = 2; This can then be used to identify the appropriate logical names in order to generate this script: RESTORE DATABASE AdventureWorks_2 FROM DISK = 'C:\Backups\Adventureworks.bak' WITH FILE = 2, MOVE 'AdventureWorks_Data' TO 'C:\backups\aw2_data.mdf', MOVE 'AdventureWorks_Log' TO 'C:\backups\aw2_log.ldf'; Restoring a differential backup The last method is to apply the differential backup. This requires two steps. First, we'll restore the database, but with a twist and then we'll apply the differential backup: RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Backups\Adventureworks.bak' WITH FILE = 1, NORECOVERY, REPLACE;



6



RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Backups\AdventureWorks.bak' WITH FILE = 3; Most of this is probably self-explanatory based on what we've already covered. The one wrinkle is the inclusion of the NORECOVERY keyword. Very simply, during a restore, transactions may have started during the backup process. Some of them complete and some don't. At the end of a restore, completed transactions are rolled forward into the database and incomplete transactions are rolled back. Setting NORECOVERY keeps transactions open. This allows for the next set of transactions to be picked up from the next backup in order. We're mainly dealing with simple backups and restores in this article, but a more advanced restore in 2005 allows secondary file groups to be restored while the database is online. Its primary file group must be online during the operation. This will be more helpful for very large database systems. Restoring SQL Server databases to a point in time Restoring logs is not much more difficult than the differential database restore that we just completed. There's just quite a bit more involved in restoring to a moment in time. Assuming you're backing up your logs to a single file or device: RESTORE HEADERONLY FROM DISK = 'C:\Backups\Adventureworks_log.bak'; Otherwise, you simply go and get the file names you need. First run the database restore, taking care to leave it in a non-recovered state. Follow this up with a series of log restores to a point in time. RESTORE DATABASE AdventureWorks FROM DISK = 'C:\Backups\Adventureworks.bak' WITH FILE = 1, NORECOVERY, REPLACE, STOPAT = 'Oct 23, 2006 14:30:29.000'; RESTORE LOG AdventureWorks FROM DISK = 'C:\Backups\Adventureworks_log.bak' WITH FILE = 1, NORECOVERY, STOPAT = 'Oct 23, 2006 14:30:29.000'; RESTORE LOG AdventureWorks FROM DISK = 'C:\Backups\Adventureworks_log.bak' WITH FILE = 2, NORECOVERY, STOPAT = 'Oct 23, 2006 14:30:29.000'; RESTORE LOG AdventureWorks FROM DISK = 'C:\Backups\Adventureworks_log.bak' WITH FILE = 3, NORECOVERY, STOPAT = 'Oct 23, 2006 14:30:29.000'; RESTORE LOG AdventureWorks



7



FROM DISK = 'C:\Backups\Adventureworks_log.bak' WITH FILE = 4, STOPAT = 'Oct 23, 2006 14:30:29.000'; Now what we have is a database that is up to the exact, last committed transaction at 14:30:29 on the 23rd of October. Remember, during multi-step restores such as this, you have to leave the database in a recovering status. That means appending NORECOVERY to each statement until you've completed the restore process. If for some reason you've added NORECOVERY to all your statements, or you simply stop in the middle, and would like to bring the database back online, you can use this statement to complete the process: RESTORE DATABASE Adventureworks WITH RECOVERY; Database snapshots SQL Server 2005 introduced the concept of a snapshot, or a read-only, static view of a database. Snapshots are primarily created in order to supply a read-only version of a database for reporting purposes. However, they do function in a similar way to backups. The one primary difference is that all uncommitted transactions are rolled back. There is no option for rolling forward, capturing logs, etc., that backups provide, nor are very many SQL Server resources used at all. Rather, disk technology is used to create a copy of the data. Because of this they are much faster than backups both to create and restore. NOTE:



For more details on SQL 2005 Snapshot, please refer to http://www.simpletalk.com/sql/database-administration/sql-server-2005-snapshots/.

A good use of snapshots, in addition to reporting, might be to create one prior to maintenance after you've already removed all the active users (and their transactions) from the system. While snapshots don't support the volatility of live backups, their speed and ease of recovery make a great tool for quick recovery from a botched rollout. Snapshots are stored on the server, so you must make sure you've got adequate storage. The syntax is different because you're not backing up a database; you're creating a new one: CREATE DATABASE Adventureworks_ss1430 ON (NAME = AdventureWorks_Data, FILENAME = 'C:\Backups\AdventureWorks_data_1430.ss') AS SNAPSHOT OF AdventureWorks; Now it will be accessible for read-only access. Since we're primarily concerned with using this as a backup mechanism, let's include the method for reverting a database to a database snapshot. First, identify the snapshot you wish to use. If there is more than one on any database that you're going to revert, you'll need to delete all except the one you are using: DROP DATABASE Adventureworks_ss1440; Then you can revert the database by running a RESTORE statement (mixed metaphors, not good):



8



RESTORE DATABASE Adventureworks FROM DATABASE_SNAPSHOT = Adventureworks_ss1430; That's it. On my system, running the database snapshots of Adventureworks took 136 ms. The full backup took 5,670 ms. The restore of the snapshot took 905ms and the database restore took 13,382ms. Incorporating this into a production rollout process could result in significant benefits Again, it's worth noting that there are some caveats to using the snapshot. You have to have enough disk space for a second copy of the database. You need to be careful dealing with snapshots since most of the syntax is similar to that used by databases themselves. Last, while there are snapshots attached to a database you can not run a restore from a database backup of that database. Best practices The manner in which you perform database backups should not be a technical decision. It should be dictated by the business. Small systems with low transaction rates and/or reporting systems that are loaded regularly will only ever need a full database backup. Medium sized systems and large systems become dependent on the type of data managed to determine what types of backup are required. For a medium sized system, a daily backup with log backups during the day would probably answer most data requirements in a timely manner. For a large database the best approach is to mix and match the backups to ensure maximum recoverability in minimum time. For example, run a weekly full backup. Twice a day during the week, run a differential backup. Every 10 minutes during the day, run a log backup. This gives you a large number of recovery mechanisms. For very large databases, you'll need to get into running filegroup and file backups because doing a full backup or even a differential backup of the full database may not be possible. A number of additional functions are available to help out in this area, but I won't be going into them here. You should take the time to develop some scripts for running your backups and restores. A naming convention so you know what database, from which server, from which date, in what specific backup and format will be very conducive to your sanity. A common location for backups, log, full or incremental, should be defined. Everyone responsible should be trained in both backup and recovery and troubleshooting the same. There are many ways of doing this, but you can find a few suggestions in Pop backs up and Pop Restores. The real test is to run your backup mechanisms and then run a restore. Then try a different type of restore, and another, and another. Be sure that, not only have you done due diligence in defining how to backup the system, but that you've done the extra step of ensuring that you can recover those backups. If you haven't practiced this and documented the practice and then tested the document, in effect, you're not ready for a disaster. Summary Backups within your enterprise should be like voting in Chicago, early and often. Setting up basic backups is quite simple. Adding on log backups and differentials is easy as well. Explore the



9



options to see how to add in file and file group backups and restores to increase the speed of your backups and restores both of which will increase system availability and up time. Keep a common naming standard. Be careful when using snapshots, but certainly employ them. Store your files in a standard location between servers. Practice your recoveries. Finally, to really make your backups sing, pick up a copy of Red Gate's SQL Backup, which speeds up backups and compresses them, using less disk space and time. A DBA's view on SQL 2005 Database Snapshots Database Snapshots are a new Enterprise Edition-only tool, introduced in SQL 2005, that provide a read-only, "virtual" copy of a database, at a given point in time. This article will discuss how, as a DBA, you might use snapshots in a production environment and why you might not consider them suitable for some of the commonly-stated use cases. These include:    Protecting your system from user or administrator error Offloading reporting Maintaining historical data



However, I'll also highlight two other areas where I believe snapshots can be a really effective tool, namely:   System upgrades Materialising data on standby servers



Creating Snapshots One of the most infuriating things I have found is that you can not use SQL Server Management Studio (SSMS) to create snapshots. I'm a big fan of scripts but I also like the convenience of using SSMS, and most junior DBA's tend to use SSMS over scripts, so I feel that this is quite a significant oversight. In any event, to create your first snapshot you will need to run something similar to the following T-SQL code: CREATE DATABASE andrew_test_snapshot_250206_1800 ON ( NAME = N'andrew_test_datafile', FILENAME = N'E:\DB_Snapshots\andrew_ test_snapshot_2502061800.snap' ) AS SNAPSHOT OF andrew_test; You will notice that the code is very similar to a script you would use to create a database but you should note that the logical device name, in this case andrew_test_datafile, has to match the logical name defined on the source database. In this example, I've created a snapshot of the database andrew_test called andrew_test_snapshot_250206_1800. Note the use of the __ naming convention. It's wise to name your snapshots clearly otherwise you may struggle unnecessarily when trying to identify when the snapshot was created. Note also that I gave the snapshot a meaningful file extension: .snap. From an organisational and administrative point of view I recommend that you store your snapshots in a separate directory from your database files. It's not such a big deal when you're working with a small number of files, but if you have hundreds of other files in the same directory you soon start wishing you had files of one type in one directory. Additionally, this



10



makes exclusions or filters even easier as you can now do them at a folder level. If you plan to use snapshots for reporting I would seriously consider putting the snap files on their own drive. This way, once they start populating, you will be keep some of the IO away from the drive that is hosting the database files, and therefore reduce disk contention. How Snaphots Work It's essential that a DBA understands at least something of what actually happens "behind-thescenes" when a snapshot is created, and of how snapshots actually work. There is a great explanation of how snapshots work on MSDN2, but I will summarize some of the particularly relevant points here. A database snapshot is a read-only static view of a database. When you create a database snapshot, as described in the previous section, the following process is invoked. 1. An empty file, called a sparse file, is created for each source database data file. 2. The database is checkpointed (all dirty pages in the buffer cache are written to disk). 3. If uncommitted transactions are in progress in the database, the snapshot will reflect the state of the database before these transactions started in other words, in the context of the snapshot, these transactions will be rolled back – but transactions in the database itself are not. 4. The snapshot is brought online and is ready for use. The empty file that is created is an NTFS sparse file. A NTFS sparse file is a file that contains no user data and has not yet been allocated space for user data. In Windows Explorer, such files are shown with a size matching that of the source database. However, when you go into the file properties you will see that the size on disk is 0KB or a multiple of 64KB (representing 1-8 data pages) if some data has been transferred:



11



NOTE: This is where the meaningful file extension (.snap) comes into its own. If I'm navigating through Explorer and come across a bunch of 10GB .snap files I will hopefully remember that, while the size is showing 10GB in Explorer, .snap files are sparse files so I need to check what space is really being used. Use of a standard extension is also very important when you are, for example, setting up anti-virus scan exclusions, or backup exclusions, across a suite of servers.

Once the snapshot is online, a copy-on-write mechanism kicks in. Just before a page is updated for the first time in the source database, since creation of the snapshot, it is copied to the snapshot datafile. Now any queries to the snapshot that access the changed pages will get those changed pages from the snapshot. If the snapshot is queried for data on any page that has not been modified in the source database, since the time the snapshot was created, then the request is simply redirected to the source database files. In this way, the snapshot presents a consistent view of the data at the point in time that the snapshot was created. The NTFS sparse files are at the heart of snapshots because only a small amount of disk space is initially required and creation is extremely fast, which means snapshots are generally created in a matter of seconds. However we must consider the disk space and ensure we have enough available for the files to grow without the drives running out of space. I discuss this in the next section. Snapshot Pros and Cons Following is a list of what I consider the main pluses and minuses associated with snapshots:



12



Pros:

1. They provide a convenient, read-only point-in-time copy of your data. 2. When you query a snapshot, you will not experience blocking due to update/insert operations that you might have to contend with when querying the source database (assuming snapshot isolation is not in use). 3. Initially the snapshot data files are small and are very quick to create. They should only become large if your database is subject to frequent modifications.



Cons:

1. You can not backup a snapshot so if you have to restore your source database your snapshots are lost. 2. Addressing database index fragmentation negates the benefit of the files being small if the sparse files are kept for a period of time. 3. Where data pages have not changed you will be accessing the source database file, which may cause contention at the file level since both the source database and the snapshot will be accessing the same MDF. 4. Every update/insert transaction on the source server potentially bears the overhead of the page copy operation to maintain the snapshot(s). 5. Because of the reliance on the source database, the snapshot has to exist on the same server as that source database. 6. You cannot grant a new user access to the data in a snapshot. Permissions are inherited form the source database as it existed at the time of snapshot creation and subsequent changes to security in the source database do not filter down to the snapshots. In the following section, I'll review these pros and cons in the specific context of a given usage scenario, and also mention other potential issues, specific to each case. However, I'll say upfront that the real snake-in-the grass here is the effect on snapshot files of defragmenting indexes. One of the most attractive benefits of using snapshots is that, because they use NTFS sparse files, they will use very little space and, unless you have a highly active database, should only ever grow to a fraction of the size of your database. However, a DBA has to maintain indexes and this involves defragmenting them from time to time. Now defragmenting an index does not change the actual data but it does move it to different pages in an attempt to make it contiguous. Since the data is on the move any snapshot(s) have to take a copy of the page so as soon as you defragment your indexes for the first time all your snapshots are going to grow to be pretty close in size to your source database. Since it's highly likely that we will be defragmenting our indexes, this virtually wipes out the benefit of sparse files. Uses for Snapshots: Winners and Losers When reviewing database snapshots I took a pessimistic approach and looked for what they might break, what might break them and, when considering other SQL technologies, where are they going to add value. Losers In this section I'll review three of the commonly stated use cases for snapshots, namely to:  Offload Reporting



13



 



Protect against user/DBA error Maintain historical data



In each case, I'll summarize the pros and cons of a snapshot solution and indicate the main reasons why, generally, I would not consider them as a solution in these scenarios. One of the frustrating things about snapshots, in my opinion, is their lack of availability in editions other than Enterprise. It seems to me that snapshots are most likely to be viable, and add value, in low throughput systems, which tend to have continuous spare capacity. Use of snapshots to offload reporting or protect from user error become much more viable in such systems, because the additional overhead of snapshots, in terms of page copying and additional query load, is easily borne. However, these same systems are the ones most likely to be using Standard edition, and therefore won't have access to snapshots in the first place! Enterprise edition servers often run high IO databases, with resources that may be further constrained in certain time windows, and so may find unacceptable the additional overhead of having snapshots on the Server. So, ironically, the installations that have access to snapshots are those that would be most likely to look towards other solutions, such as log shipping, for reporting and protection from user or administrator error. Offloading Reporting Below are what I consider the main pros and cons of using snapshots as a means of offloading reporting:



Pros:

1. You have a read-only point-in-time copy of your data. 2. You will not be contending with blocking due to update/insert operations (let's assume snapshot isolation is not in use). 3. Initially the snapshot data files are small.



Cons:

1. You cannot grant a new user access to the data in a snapshot. 2. You can not backup the snapshots so if you have to restore your source database your reporting snapshots are lost. 3. Where data pages have not changed you will be accessing the source database file which may cause contention at the file level since both the source database and the snapshot will be accessing the same MDF. 4. Full text indexes are not available on snapshots so if you require full text searching for your reporting then snapshots are not an option. Additionally, you should bear in mind that snapshots have to be on the same server as the source database: you do not have a physical dedicated, reporting server. Therefore, physical resources on the database server, such as CPU and memory, still have to be shared. One other thing to note is that NTFS sparse files grow in 64kb increments and these increments are likely to occur at different times. As such, it's highly likely that a populated sparse file will not be contiguous at the NTFS level. The effect of this on reporting could lead to significant strain on your disks.



14



Ultimately, the main thing that would stop me from using snapshots for report offloading is the likelihood of increased physical resource contention. Maintaining Historical Data Below are what I consider the pros and cons.



Pros:

1. Potentially, you can maintain a significant amount of history with little disk space usage.



Cons:

1. If the drive hosting the snapshot runs out of space causing an update to fail, the snapshot is marked suspect and can not be recovered. 2. Deframenting your indexes negates the benefits associated with sparse files (if they are kept for a period of time). 3. Every update/insert transaction on the source server potentially bears the overhead of the page copy operation to maintain the snapshot(s). 4. You can not backup your snapshots. Although maintaining historical data is listed as a typical use I would never use snapshots for this simply because I can not back them up. Protecting from User or Administrator Error In theory, you could use a snapshot to restore a database to a point before a user or administrator error occurred simply by issuing: RESTORE DATABASE andrew_test from DATABASE_SNAPSHOT = 'andrew_test_snapshot_250206_1800' Below are what I consider the pros and cons.



Pros:

1. The snapshot process is very quick. 2. The data and code is available immediately for restoration.



Cons:

1. The snapshot is at a point in time so is unlikely to be suitable for data recovery. 2. If you restore a snapshot you can't then restore any transaction logs so you potentially lose a large amount of data. 3. When you restore a snapshot all other snapshots of the database have to be deleted which would be a problem if you are maintaining reporting snapshots. 4. If your administrative error involved damage to a physical file (say a drive was accidentally wiped) the snapshot will not help you. To get any real value from snapshots in this area, you would have to take several snapshots throughout the day. I feel that the benefits of snapshots in this scenario will be far outweighed



15



by the overhead of maintaining multiple snapshots, especially when considering other available options such as log shipping or even restoring from backup. It goes without saying (but here it is anyway) that snapshots should not be considered as part of or a replacement for a proper backup. To use them in this way would be disastrous when you consider their limitations, such as their dependency on the source database. Winners In this section, I move on to the two scenarios where I do believe that snapshots can provide a real benefit for enterprise systems:   System upgrades Materializing data on standby servers



Performing System Upgrades The scenario that I consider a real winner for snapshots has to be when performing a system upgrade. Typically a system upgrade involves a release of code that changes the underlying schema and or data. It may also involve updating related application code. When performing such an upgrade the system administrator will ensure that full backups are taken of everything that will change as part of the upgrade, including the database. This process can be time consuming and requires additional space to store the backups, whilst the upgrade is in progress. By creating a snapshot, the database "backup" time is reduced to seconds and you only require the amount of space required might be very small, assuming only a small amount of data is changing. Once the system upgrade is complete, and after performing system checks, if you should you find yourself in the unfortunate position where you must restore to the point prior to the upgrade the recovery is greatly speeded and simplified. To restore your database to the point prior to the upgrade you would issue the RESTORE T-SQL command shown previously. The restore time is likely to be less than a minute, depending on how much data changed, as opposed to potentially hours when restoring a large database. I have to point out though that there will still be some system upgrades where I would want a full backup prior to starting work. Upgrading the operating system is such an example – but with most upgrades a snapshot will suffice. I will also state that I am assuming the last full backup and transaction logs up until the upgrade are available should the worst happen when using the snapshot approach. Materialising Data on your Standby Servers The other winner for me is using snapshots to "materialise" (make available for querying/reporting) data on your standby servers. Due to business requirements, or to avoid connections interfering with log shipping, you may have chosen to keep your log shipped database in NORECOVERY mode. This means you cannot read from the database. By creating a snapshot of your log shipped database, you can read the data and effectively use the standby server to offload reporting requirements.



16



More importantly, this technique also works with mirrored databases and since a mirror destination can only ever be in NORECOVERY mode, it's an excellent way to access the mirrored data for the purpose of reporting. However there is always a trade off and the key point to keep in mind when using snapshots on a mirrored database is that, in synchronous mode, transactions have to commit on both servers before SQL can mark the transaction as complete. By introducing the snapshot we have introduced a third step before a transaction will commit. Why? Well below are the steps you now have to take if you have a snaphot: 1. Write transaction on server A source database. 2. Write transaction on server B destination database. 3. Copy page on server B destination database to server B snapshot. Without the snapshot you only have to do steps one and two. Of course, all this assumes that snapshots work smoothly and seamlessly with high availability solutions such as log shipping and database mirroring. So, what might you have to consider when using snapshots with these two HA technologies? Snapshots and Log Shipping    There are no restrictions on creating a snapshot on a log shipped source (or destination) and log shipping is not disrupted when creating a snapshot. You are not prevented from restoring the snapshot when log shipping is present. When you restore the snapshot you must remember that the process is similar to a database restore so it is not a logged operation and as such log shipping will then fail.



Snapshots and Database Mirroring    There are no restrictions on creating a snapshot on a mirrored source (or destination) and mirroring is not disrupted when creating a snapshot. You are prevented from restoring a snapshot whilst mirroring is active. You must first stop mirroring and once you have restored you must then restore the destination database and enable mirroring again. Although you can snapshot the source and destination databases, when you issue a snapshot restore the recovery option is ignored. This means you can not restore a snapshot to a recovering state so your destination mirror database must be restored from a full backup as mirroring requires the destination to be in a recovering state.



Conclusion Despite how it may seem I actually welcome snapshots with open arms to my DBA tool box. I do believe it's a pity that this is an Enterprise edition only feature, but I do see it as a tool I will make good use of when doing system upgrades on my Enterprise edition servers. Snapshots are in their infancy but I feel that with a little more work they will become a widely-used tool. Partitioned Tables in SQL Server 2005 Partitioned tables are a new feature available in SQL Server version 2005, aimed mainly at improving the performance of large database systems. The feature is only available for enterprise and developer edition. For other editions you can get a similar functionality with a partitioned view.



17



This article focuses on how to create a partitioned table and manipulate the partitions, rather than exploring the performance aspects. Creating the Partitioned Table First of all, we will create a partitioned table and prove that it is acting in a partitioned manner, in that the queries will only access the partitions that are required. To start with, we need to create a partition function. This will define how many partitions exist, and the values contained in partition columns within those partitions CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2) The partition function has been named MyPartitionRange. The partition column is an int. 'Range left' means that the value is the upper bound for the partition. The above code will define a partitioned table that contains 3 partitions.    Partition 1 – Partition value 1 and 2



As the partition column is an integer the partitions will actually be    Partition 1 – Partition value 2



Note: that the range left / range right determines the partition for data that matches the partition literal value. For integer data this would change the partition number for all the data . Now we create a partition scheme. CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) The partition scheme is named MyPartitionScheme and references the partition function MyPartitionRange. All of the partitions are to be held on the primary filegroup. Now we can create the table. All that is needed is to reference the partition scheme naming the partition column in an ―on‖ clause CREATE TABLE MyPartitionedTable (



18



ON MyPartitionScheme (PartCol) We can check the table structure via… SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable') …which gives… partition_id object_id index_id partition_number hobt_id rows ------------------ ----------- --------- ---------------- ------------------ ---72057594038714368 229575856 0 1 72057594038714368 72057594038779904 229575856 0 2 72057594038779904 72057594038845440 229575856 0 3 72057594038845440 Now we add some data INSERT INSERT INSERT INSERT INSERT INSERT INSERT MyPartitionedTable MyPartitionedTable MyPartitionedTable MyPartitionedTable MyPartitionedTable MyPartitionedTable MyPartitionedTable (i, (i, (i, (i, (i, (i, (i, s, s, s, s, s, s, s, PartCol) PartCol) PartCol) PartCol) PartCol) PartCol) PartCol) SELECT SELECT SELECT SELECT SELECT SELECT SELECT 1, 2, 3, 4, 5, 6, 7, 'a', 'a', 'a', 'a', 'a', 'a', 'a', 1 2 2 3 3 3 4



i INT , s CHAR(8000) , PartCol INT )



0 0 0



... and check that the rows have been added to the correct partitions SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable') … giving as expected… partition_id object_id index_id partition_number hobt_id rows ------------------- --------- ----------- ---------------- -------------------- ---72057594038714368 229575856 0 1 72057594038714368 72057594038779904 229575856 0 2 72057594038779904 72057594038845440 229575856 0 3 72057594038845440 A function, $partition, is available to give the partition number for the data. SELECT PartitionNo = $partition.MyPartitionRange(PartCol), NumRows = COUNT(*) FROM MyPartitionedTable GROUP BY $partition.MyPartitionRange(PartCol) ORDER BY $partition.MyPartitionRange(PartCol)



1 2 4



19



The following… SELECT PartitionNo = $partition.MyPartitionRange(6) …is a valid statement and shows that a row with partition value 6 would be added to partition 3. The function gives the row counts for each partition and so can be useful to find which partition holds the data. Now to test that queries only access the required partition. We make partition 2 very large (this is why we made s a char(8000)). DECLARE @i INT SELECT @i = 13 WHILE @i > 0 BEGIN SELECT @i = @i - 1 INSERT MyPartitionedTable (i, s, PartCol) SELECT i, s, PartCol FROM MyPartitionedTable WHERE PartCol = 2 END ...and we check the rowcounts ... SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable') …giving as expected /* partition_id object_id index_id partition_number hobt_id rows ------------------- ----------- --------- ---------------- -------------------- ----72057594041532416 2117582582 0 1 72057594041532416 72057594041597952 2117582582 0 2 72057594041597952 72057594041663488 2117582582 0 3 72057594041663488 */



1 16384 4



On my laptop that is enough to give an appreciable difference in query times – if the first query below is too quick to detect on your machine then increase the number of loops to add rows to the partition. Now try the queries SELECT COUNT(DISTINCT s) FROM MyPartitionedTable WHERE PartCol = 2 SELECT COUNT(DISTINCT s) FROM MyPartitionedTable WHERE PartCol = 1 You should find that the first takes a lot longer than the second because in both cases only one partition is accessed, a single read for partition 1, many reads for partition 2.



20



Adding and Removing Partitions In the previous example, we added data to the partition by inserting rows into the partitioned table. It is also possible to populate a table, and then add that table to the partitioned table as a partition. There are many restrictions on the nature of the table and data that can be added. The command to add the table as a partition is ―alter table …. Switch…‖. It actually swaps the table with a partition already existing in the partitioned table. We will now add a new partition to MyPartitionedTable for partition value 3 and then swap it for a new table MyNewPartition. To add a new partition to MyPartitionedTable, use the split range command on the partition function. ALTER PARTITION FUNCTION MyPartitionRange () split RANGE (3) This has added a new partition for partition value 3. The reverse of this is a merge range statement ALTER PARTITION FUNCTION MyPartitionRange () merge RANGE (3) SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable') /* partition_id object_id index_id partition_number hobt_id rows -------------------- ----------- --------- ---------------- -------------------- ---72057594042056704 322100188 0 1 72057594042056704 72057594042122240 322100188 0 2 72057594042122240 72057594042187776 322100188 0 4 72057594042187776 72057594042253312 322100188 0 3 72057594042253312 */



1 2 1 3



Note that the existing rows for partition value 3 have been moved to partition 3. The row with partition value 4 has been moved to partition 4. We create a new table to swap with a partition. This table must have the same structure as the partition. It must also include a check constraint to ensure that the partition column values in the table are included in the correct partition. The check constraint must be at least as restrictive as the partition range function for that partition. CREATE TABLE MyNewPartition ( i INT , s CHAR(8000) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) )



21



I normally create and populate the table and add the check constraint later. Now we add some data to the new table INSERT MyNewPartition SELECT 1, 'a', 3 To perform this operation the partition must be empty so... DELETE MyPartitionedTable WHERE PartCol = 3 And we can swap the table with the partition ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3 Viewing the partitions SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable') /* partition_id object_id index_id partition_number hobt_id rows -------------------- ----------- --------- ---------------- -------------------- ---72057594042056704 322100188 0 1 72057594042056704 72057594042122240 322100188 0 2 72057594042122240 72057594042187776 322100188 0 4 72057594042187776 72057594042318848 322100188 0 3 72057594042318848 */ We see that the partition has been swapped with the new table. The advantage of this is that the swap does not move the data – it just updates the metadata so that the table becomes the partition. This means that it is very fast. The table can be created, populated, and then added as a partition, thereby causing minimal impact on the partitioned table. Switching a populated partition Of course the partition that is being swapped out would often contain a lot of data – usually this would be used for adding another partition to the right to split up the data. Deleting the data from the partition would not be feasible – nor would the split on a populated partition. To accomplish this, you would need to first populate the two tables to replace the 'catch all' partition (in our scenario partition 3). Now create a table MyOldPartition3 – remember this must be the same structure as above. As this is a destination for a switch the check constraint must be less restrictive than that on the partitioned table. Now switch the partition out ALTER TABLE MyPartitionedTable switch PARTITION



1 2 1 1



22



3 TO MyOldPartition3 Now the partition split can be carried out on an empty partition and the two partitions switched in without moving any data. Identities in a partitioned table Remember that an identity is not guaranteed to be unique or sequential. It just allocates the next value from the current seed. With that in mind nothing that follows should come as a surprise. For this we will create a new partition function, scheme and table CREATE PARTITION FUNCTION MyIdentityPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) CREATE PARTITION SCHEME MyIdentityPartitionScheme AS PARTITION MyIdentityPartitionRange ALL TO ([PRIMARY]) CREATE TABLE MyIdentityPartitionedTable ( i INT IDENTITY (1,1) , s CHAR(10) , PartCol INT ) ON MyPartitionScheme (PartCol) --And we add some data INSERT MyIdentityPartitionedTable INSERT MyIdentityPartitionedTable INSERT MyIdentityPartitionedTable INSERT MyIdentityPartitionedTable s, PartCol) SELECT 'b', 2 (s, PartCol) SELECT 'a', 1 (s, PartCol) SELECT 'a', 2 (s, PartCol) SELECT 'b', 1 (



SELECT * FROM MyIdentityPartitionedTable i s PartCol ----------- ---------- ----------1 a 1 3 b 1 2 a 2 4 b 2 Showing that the identity is a property of the partitioned table rather than the partition. More interesting is what happens when partitions are swapped: CREATE TABLE MyIdentityPartitionedTableNew ( i INT IDENTITY (1,1) , s CHAR(10 ), PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) )



23



INSERT MyIdentityPartitionedTableNew (s, PartCol) SELECT 'c', 3 INSERT MyIdentityPartitionedTableNew (s, PartCol) SELECT 'd', 3 ALTER TABLE MyIdentityPartitionedTableNew switch TO MyIdentityPartitionedTable PARTITION 3 SELECT * FROM MyIdentityPartitionedTable i s PartCol ----------- ---------- ----------1 a 1 3 b 1 2 a 2 4 b 2 1 c 3 2 d 3 And we see duplicate identity values from the new partition. Adding a new row



INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'e', 1 SELECT * FROM MyIdentityPartitionedTable i s PartCol ----------- ---------- ----------1 a 1 3 b 1 5 e 1 2 a 2 4 b 2 1 c 3 2 d 3 We see that the partition swap has not affected the identity seed for the table. Partitioned tables and Indexes If the index contains the partitioning column then the index is referred to as being 'aligned' with the table. If the index uses the same partitioning scheme as the table and is in the same filegroup then the index must be aligned with the table. For a non-clustered non-unique index the partitioning column can be included to align the index rather than being indexed. I think it is best to always explicitly include the partitioning column in your indexes.



24



The following assumes a single filegroup and scheme. Clustered index As stated this index must be aligned with the table. If it is not then the partitioning column will be implicitly added as the last column of the index. CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE MyPartitionedTable ( i INT , j INT , s VARCHAR(MAX) , PartCol INT ) ON MyPartitionScheme (PartCol) CREATE TABLE MyNewPartition ( i INT , j INT , s VARCHAR(MAX) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) ) CREATE CLUSTERED INDEX cl_ix ON MyPartitionedTable (j) CREATE CLUSTERED INDEX cl_ix ON MyNewPartition (j) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3 …gives the error… ALTER TABLE SWITCH statement failed. There is no identical index in source table 'tempdb.dbo.MyNewPartition' for the index 'cl_ix' in target table 'tempdb.dbo.MyPartitionedTable' . whereas these all succeed. DROP INDEX MyNewPartition.cl_ix DROP INDEX MyPartitionedTable.cl_ix CREATE CLUSTERED INDEX cl_ix ON MyPartitionedTable (j, PartCol) CREATE CLUSTERED INDEX cl_ix ON MyNewPartition (j, PartCol) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3 DROP INDEX MyNewPartition.cl_ix DROP INDEX MyPartitionedTable.cl_ix CREATE CLUSTERED INDEX cl_ix ON MyPartitionedTable (PartCol, j) CREATE CLUSTERED indeex cl_ix ON MyNewPartition (PartCol, j) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3



25



This will also work DROP INDEX MyNewPartition.cl_ix DROP INDEX MyPartitionedTable.cl_ix CREATE CLUSTERED INDEX cl_ix ON MyPartitionedTable (j) CREATE CLUSTERED INDEX cl_ix ON MyNewPartition (j, PartCol)



...showing that the partitioning column has been implicitly added to the clustered index in the partitioned table. Note that this extra column will not be shown by sp_helpindex on the partitioned table nor by scripting the index but it is shown by sys.index_columns I don’t know why Microsoft decided to add the column to the index automatically. I think it would be less confusing to give an error, thereby forcing the user to add the column explicitly. Unique index Unique indexes must contain the partitioning column as an indexed column. CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE MyPartitionedTable ( i INT , j INT , s VARCHAR(MAX) , PartCol INT ) ON MyPartitionScheme (PartCol) CREATE TABLE MyNewPartition ( i INT , j INT , s VARCHAR(MAX) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) ) CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (j) ...gives the error Msg 1908, Level 16, State 1, Line 1 olumn 'PartCol' is partitioning column of the index 'cl_ix'. Partition columns for a unique index must be a subset of the index key.



26



… one of the more explanatory error messages. The Partitioning column must be part of the index so … CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (j) include (PartCol) …will also fail. Unlike clustered indexes the partitioning column must be explicitly part of the index. These will both work CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (j, PartCol) CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (j, PartCol) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3 CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (PartCol , j) CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (PartCol , j) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3



Non-unique index Non-unique indexes do not need to have the partitioning column as part of the index, it can be an INCLUDE column. If it is not explicitly included, then the column will be automatically added – again this will not appear in sp_helpindex CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE MyPartitionedTable ( i INT , j INT , s VARCHAR(MAX) , PartCol INT ) ON MyPartitionScheme (PartCol) CREATE TABLE MyNewPartition ( i INT , j INT , s VARCHAR(MAX) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) ) CREATE INDEX cl_ix ON MyPartitionedTable (j) CREATE INDEX cl_ix ON MyNewPartition (j) include (PartCol) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3



27



Is successful as are… CREATE INDEX cl_ix ON MyPartitionedTable (j, PartCol) CREATE INDEX cl_ix ON MyNewPartition (j, PartCol) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3 CREATE INDEX cl_ix ON MyPartitionedTable (PartCol, j) CREATE INDEX cl_ix ON MyNewPartition (PartCol, j) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3 Scenarios Partitioning on multiple columns Although the partitioning column must be a single column, it does not need to be numeric and it can be calculated so that the range can include multiple columns. For instance it is common to partition on datetime data by month. This will work well, because that data is usually in a single column, but what do you do if you have data for multiple companies and you also want to partition by company? For this you could use a computed column for the partitioning column. This will create a computed column using the 'company id' and 'order month' which is then used for the partitions. It will partition three companies for the first three months of 2007. CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1200701,1200702,1200703,2200701,2200702,2200703,3200701,3200702,3200703) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE CompanyOrders ( Company_id INT , OrderDate datetime , Item_id INT , Quantity INT , OrderValue decimal(19,5) , PartCol AS Company_id * 10000 + CONVERT(VARCHAR( 4),OrderDate,112) persisted ) ON MyPartitionScheme (PartCol) The computed column must be 'persisted' to form the partitioning column. We will investigate the maintaining of partitioned data in this table later. Monthly Data – the sliding range A common requirement is to partition by month. This means that new month partitions need to be added and possibly old data partitions removed. I will describe the process for the addition of a new partition for later data, to remove an old partition the process is the same except that you swap out two partitions, merge the range and swap in a single table. We create a partitioned table for data by OrderDate month



28



CREATE PARTITION FUNCTION MyPartitionRange (datetime) AS RANGE RIGHT FOR VALUES ('20070101', '20070201', '20070301', '20070401') CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE Orders ( OrderDate datetime , Item_id INT , Quantity INT , OrderValue decimal(19,5) ) ON MyPartitionScheme (OrderDate) This will give four partitions… OrderDate OrderDate OrderDate OrderDate OrderDate = '20070101' AND = '20070201' AND = '20070301' AND = '20070401'



Therefore the data will be split into partitions by month. And we insert some test data --insert Orders select '19000101', 1, 1, 1 INSERT Orders SELECT '20070101', 1, 1, INSERT Orders SELECT '20070201', 1, 1, INSERT Orders SELECT '20070301', 1, 1, INSERT Orders SELECT '20070401', 1, 1, INSERT Orders SELECT '20070402', 1, 1, INSERT Orders SELECT '20070501', 1, 1,



1 1 1 1 1 1



To add the next month's partition it is possible to just split the range and let the system take care of the data. This though would mean that the data would be off-line for the duration of the operation. It is better to use the experience we have gained in switching partitions to create the new data in separate tables then switch them in. This means that the table would be off-line for a very short time – just while the switch operations are taking place. If data is being continually added to the partitioned table then a snapshot can be taken, the new tables prepared on this and the switch-in operation will need to take the table off-line, merge the new data with the prepared snapshot data then perform the switch-in. An identity column on the table would help to identify new data added since the snapshot. This would mean longer downtime than for static data but still a lot less than splitting a populated range. To add a new months partition of static data 1. Create the table containing the new months data. 2. Create the table containing the data after the new month



29



3. 4. 5. 6. 7.



Swap out the last month from the partitioned table Split the (empty) range in the partitioned table Swap in the new months data Swap in the table containing the data after the new month Check the result



1. Create the table containing the new months data. In the initial discussion we created a table for this but you might find it easier to create a partitioned table for the operation. Note that if you script the existing table and indexes make sure that you remember that not all indexed columns may appear in the index script. Also if you take this route you will have to use a new partition function and scheme as you will need to split the range. I prefer to use non-partitioned tables for flexibility. To population of the table will depend on where the data resides but wil only affect the production table if you need to read the data from that table. CREATE TABLE Orders_200704 ( OrderDate datetime CHECK (OrderDate >= '20070401' AND OrderDate = '20070401 AND OrderDate = '20070501' AND OrderDate IS NOT NULL) , Item_id INT , Quantity INT , OrderValue decimal(19,5) ) INSERT Orders_200705 SELECT * FROM Orders WHERE OrderDate >= '20070501' At this point take the production table off-line for the swap. 3. Swap out the last month from the partitioned table



30



For this you will need an empty table to swap the data into. It is tempting again to use a partitioned table for this – if so you will need to create a new partition function and scheme as you would not want to lose the data until after splitting the range on the production table. CREATE TABLE Orders_200704_Old ( OrderDate datetime CHECK (OrderDate >= '20070401' AND OrderDate IS NOT NULL) , Item_id INT , Quantity INT , OrderValue decimal(19,5) ) ALTER TABLE Orders switch PARTITION 5 TO Orders_200704_Old 4 Split the (empty) range in the partitioned table This is done by adding a new value to the partition function ALTER PARTITION FUNCTION MyPartitionRange () split RANGE ('20070501') As the partition is empty this should be quick as it just means creating a new empty partition. 5 Swap in the new months data ALTER TABLE Orders_200704 switch TO Orders PARTITION 5 6 Swap in the table containing the data after the new month ALTER TABLE Orders_200705 switch TO Orders PARTITION 6 7 Check the result SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Orders') Gives the expected result partition_id object_id index_id partition_number hobt_id rows -------------------- ----------- --------- ---------------- -------------------- ---72057594038845440 213575799 0 1 72057594038845440 72057594038910976 213575799 0 2 72057594038910976 72057594038976512 213575799 0 3 72057594038976512 72057594039042048 213575799 0 4 72057594039042048 72057594039173120 213575799 0 5 72057594039173120 72057594039238656 213575799 0 6 72057594039238656 Now the table Orders_200704_Old can be dropped at your leisure. Adding a new partition with a computed partition function We return to the table partitioned by company and month.



1 1 1 1 2 1



31



CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1200701,1200702,1200703,2200701,2200702,2200703,3200701,3200702,3200703) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE CompanyOrders ( Company_id INT , OrderDate datetime , Item_id INT , Quantity INT , OrderValue decimal(19,5) , PartCol AS Company_id * 10000 + CONVERT(VARCHAR(4),OrderDate,112) persisted ) ON MyPartitionScheme (PartCol)



In order too add a new month to this table, you will need to split each company's range for that month. To add a new company partition means splitting all months for that company. There is no need to add all the partitions in one process, these operations can be performed one partition at a time. This should not affect the results of queries on the table. The process of adding the new company or month is the same as for the sliding month data, only with many splits and swaps. Other uses of partitioned tables Usually, partitioned tables are used to horizontally, or vertically, partition the data. However, the partitions are sometimes used for different purposes – not partitioning the data at all. I recently came across a reporting system that was querying a single flat table for aggregated results. The table was about 15Gb in size and could be filtered and grouped on any combination of columns. Indexing by date meant that a report for a year would take about 20 minutes – far too long (performance checked just before the release date of course), in fact anything more than 3 months was unacceptable. Normalizing and using a view helped with the retrieval of the filter column data, but the actual report was limited by the amount of data it needed to aggregate. Due to time constraints and policy, we were not allowed to create a cube and the report could not call a stored procedure. Oddly, there was a lot of flexibility in the query used to extract data but it had to access the single table. The solution was to create a partitioned table. The first partition (partition value = 1) contained the old data table and would still be slow to access. The second partition contained aggregated data, aggregated by filter columns that kept the table size less than 200K rows. All partitions have the same structure so those filter columns that were excluded were set to null. Accessing this partition was quick enough for any report.



32



The report application was then changed to check if the filter/grouping columns were all included in the second partition – if so it appended ―and PartCol = 2‖ to the query otherwise it appended ―and PartCol = 1‖. The reporting application could warn the users if they were about to do something that would take a long time. We then checked how the reports were being used, and selected combinations of columns that could be used for other partitions. The partitions were added to the table (not affecting the system) and then, at a later time, the reporting application changed to use the new partitions. This would have been easier with a stored procedure as the partitions could have been left as separate tables and the stored procedure could choose which to query, but a stored procedure call was not allowed by the reporting application



Defragmenting Indexes in SQL Server 2005 and 2008

When data is inserted into, deleted from, or updated in a SQL Server table, the indexes defined on that table are automatically updated to reflect those changes. As the indexes are modified, the information stored in them becomes fragmented, resulting in the information being scattered across the data files. When this occurs, the logical ordering of the data no longer matches the physical ordering, which can lead to a deterioration of query performance. To fix this problem, indexes must be periodically reorganized or rebuilt (defragmented) so the physical order of the leaf-level pages matches the logical order of the leaf nodes. This means that you should analyze your indexes periodically to determine whether they’ve become fragmented and the extent of that fragmentation. From there, you can either reorganize or rebuild the affected indexes, depending on the results of your analysis. In this article, I explain how to analyze SQL Server indexes and how reorganize and rebuild them. Note: Some sources distinguish between internal and external fragmentation. Internal fragmentation indicates that there is too much free space on the index page. External fragmentation indicates that the logical ordering and physical ordering do not match. Microsoft documentation doesn’t make this distinction and simply provides guidelines for when to defragment indexes and which type of defragmentation to perform. For this article, I follow the Microsoft guidelines. Analyzing Fragmentation To analyze SQL Server 2005 or 2008 indexes, you use the system function sys.dm_db_index_physical_stats to determine which indexes are fragmented and the extent of that fragmentation. You can use the function to analyze all the indexes in an instance of SQL Server 2005 or 2008, all indexes in a database, all indexes defined on a table, or a specific index. You can also analyze an index based on the partition number of the indexed object. The sys.dm_db_index_physical_stats function takes the following parameters (in the order specified):  Database ID: A smallint value that represents the ID number of a database. If null is specified, the function retrieves index-related data from all databases on a SQL Server



33











 



instance. If you specify null, you must also specify null for the object ID, index ID, and partition number. Object ID: An int value that represents the ID number of a table or view. If null is specified, the function retrieves index-related data for all tables and views in a specific database or SQL Server instance. If you specify null, you must also specify null for the index ID and partition number. Index ID: An int value that represents the ID number of an index. If null is specified, the function retrieves index-related data for all indexes defined on the specified table or view. If you specify null, you must also specify null for the partition number. Also, if the object ID refers to a heap, use 0 as the index ID. Partition number: An int value that represents the partition number of an index or heap. If null is specified, the function retrieves index-related information for all partitions related to a specific object. Mode: The scan level used to obtain index-related information. Valid inputs include NULL, DEFAULT, or one of the following three modes:    LIMITED: Scans the smallest number of pages, which means this is the fastest mode. The LIMITED mode is equivalent to NULL and DEFAULT. SAMPLED: Scans 1% of all pages. If an index contains fewer than 10,000 pages, then DETAILED mode is used. DETAILED: Scans all index pages, which means this is the slowest mode, but most accurate.



You must specify all five parameters, even if their values are null. The sys.dm_db_index_physical_stats function returns a number of values that provide details about the indexes you specify. The topic ―sys.dm_db_index_physical_stats‖ in SQL Server Books Online provides details about each of these values. However, several values are worth noting when analyzing an index:     avg_fragmentation_in_percent: Percentage of the logical index that is fragmented. fragment_count: Number of fragments in the leaf level. avg_fragment_size_in_pages: Average number of pages in a leaf-level fragment. page_count: Number of index or data pages.



An index always has at least one fragment (fragment_count). The maximum number of fragments that an index can have is equal to the number of pages (page_count). For example, an index that is made up of five pages can at the most have five fragments. The larger the fragment, the less disk I/O that is required. So a five-page index with one fragment requires less disk I/O than the index with five fragments. Ideally, the avg_fragmentation_in_percent value should be as close to zero as possible, and the avg_fragment_size_in_pages should be as high as possible. Based on your index analysis, you can determine what action to take. Microsoft recommends that you reorganize your index if the avg_fragmentation_in_percent value is less than or equal to 30% and rebuild the index if the value is greater than 30%. (Reorganizing and rebuilding indexes are described in the following sections.) Note: The topic ―sys.dm_db_index_physical_stats‖ in SQL Server Books Online includes a script that automatically analyzes and then, based on that analysis, reorganizes or rebuilds your indexes according to the Microsoft guidelines. You can modify the script as necessary to meet your administrative needs.



34



Keep in mind that these recommendations are guidelines only. A fragmented index (especially a low percentage) is not always enough of a reason to reorganize or rebuild your index. If your queries do not regularly involve table scans as a result of singleton lookups, defragmenting the index might have no effect on performance. In addition, for smaller indexes with relatively few pages and small amounts of data, you might see little to no improvement when you defragment the index. FILLFACTOR settings can also affect the types of improvements you see. That said, you should still analyze your indexes regularly, and the sys.dm_db_index_physical_stats function is the best tool to use. So let’s take a look at an example of how to use the function to retrieve index-related statistics. In the following SELECT statement, I retrieve index data from the AdventureWorks database: SELECT object_id AS ObjectID, index_id AS IndexID, avg_fragmentation_in_percent AS PercentFragment, fragment_count AS TotalFrags, avg_fragment_size_in_pages AS PagesPerFrag, page_count AS NumPages FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), NULL, NULL, NULL , 'DETAILED') WHERE avg_fragmentation_in_percent > 0 ORDER BY ObjectID, IndexID As you can see, I use the sys.dm_db_index_physical_stats function to retrieve the four values described above, plus the object_id and index_id values. Notice that I pull back only those indexes with an avg_fragmentation_in_percent value greater than zero and I order the results by object ID and index ID. That way, the indexes for each table and view are grouped together. Notice also that I use the DB_ID() function to retrieve the ID for the AdventureWorks database. Table 1 shows a partial list of the results that are returned from my local instance of SQL Server 2005. (I also ran the SELECT statement against an instance of SQL Server 2008 and received similar results.) ObjectID 18099105 30623152 30623152 30623152 30623152 53575229 66099276 78623323 IndexID 2 1 2 3 4 4 1 1 PercentFragment 50 20 66.6666666666667 50 50 7.40740740740741 50 0.380710659898477 TotalFrags 2 2 3 2 2 3 2 29 PagesPerFrag 1 2.5 1 1 1 9 1 27.1724137931034 NumPages 2 5 3 2 2 27 2 788



35



78623323 78623323 158623608 158623608 158623608 181575685 181575685 181575685 270624007 279672044 306100131 309576141



2 3 1 2 3 1 2 3 1 1 2 1



1.91082802547771 1.41509433962264 0.483870967741936 7.31707317073171 1.78571428571429 15 33.3333333333333 30 50 98.5714285714286 17.6470588235294 0.536672629695885



8 13 31 14 10 4 5 4 2 70 4 14



19.625 16.3076923076923 20 8.78571428571429 16.8 5 1.8 2.5 1 1 4.25 39.9285714285714



157 212 620 123 168 20 9 10 2 70 17 559



Table 1: Partial List of Index Statistics for the AdventureWorks Database Each row in Table 1 represents a specific index whose avg_fragmentation_in_percent value is greater than zero. As you can see, the statistics can vary greatly, but every index is fragmented to a certain degree. As I mentioned, Table 1 shows only a partial list of indexes, but the number of indexes can grow quite large. As a result, you might want to narrow down the result set. In the following example, I specify the Sales.StoreContact table in the AdventureWorks database so that information related only to that table’s indexes is returned: SELECT b.name AS IndexName, a.avg_fragmentation_in_percent AS PercentFragment, a.fragment_count AS TotalFrags, a.avg_fragment_size_in_pages AS PagesPerFrag, a.page_count AS NumPages FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), OBJECT_ID('Sales.StoreContact'), NULL, NULL , 'DETAILED') AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE a.avg_fragmentation_in_percent > 0 ORDER BY IndexName Notice that I use the OBJECT_ID() function to return the object ID of the StoreContact table. Also notice that I join the sys.dm_db_index_physical_stats function to the sys.indexes table to



36



retrieve the name of the indexes. Now the results are far easier to read, as you can see in Table 2. IndexName AK_StoreContact_rowguid IX_StoreContact_ContactID IX_StoreContact_ContactTypeID PK_StoreContact_CustomerID_ContactID PercentFragment 66.6666666666667 50 50 20 TotalFrags 3 2 2 2 PagesPerFrag 1 1 1 2.5 NumPages 3 2 2 5



Table 2: List of Index Statistics for the Sales.StoreContact Table in the AdventureWorks Database As Table 2 indicates, the StoreContact table contains four indexes whose avg_fragmentation_in_percent value is greater than zero. Based on Microsoft’s guidelines, I might choose to reorganize the PK_StoreContact_CustomerID_ContactID index and rebuild the others. However, before we proceed to that process, let’s look at one more example. In the following SELECT statement, I return statistics on a specific index in the StoreContact table: DECLARE @IndexName VARCHAR(100) SET @IndexName = 'PK_StoreContact_CustomerID_ContactID' DECLARE @IndexID SMALLINT SET @IndexID = (SELECT index_id FROM sys.indexes WHERE name = @IndexName) SELECT @IndexName AS IndexName, avg_fragmentation_in_percent AS PercentFragment, fragment_count AS TotalFrags, avg_fragment_size_in_pages AS PagesPerFrag, page_count AS NumPages FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), OBJECT_ID('Sales.StoreContact'), @IndexID, NULL , 'DETAILED') WHERE avg_fragmentation_in_percent > 0



37



In this case, I declare variables to pass in the index name and to retrieve the index ID. I then use those variables in the SELECT statement to retrieve the necessary data. Table 3 shows the information returned by this statement: IndexName PK_StoreContact_CustomerID_ContactID PercentFragment 20 TotalFrags 2 PagesPerFrag 2.5 NumPages 5



Table 3: Statistics on the PK_StoreContact_CustomerID_ContactID Index As you would expect, the data matches the data shown in Table 2 for this index. From this information, you can see that 20% of the logical index is fragmented (avg_fragmentation_in_percent = 20), the index contains two fragments (fragment_count = 2) that are spread across five pages (page_count = 5), for an average of 2.5 pages per fragment (avg_fragment_size_in_pages = 2.5). Based on the avg_fragmentation_in_percent value of 20%, the index should be reorganized, rather the rebuilt. Reorganizing Indexes When you reorganize an index, SQL Server physically reorders the leaf-level pages to match the logical order of the leaf nodes. The process uses the existing pages only and does not allocate new ones, but it does compact the index pages. In addition, reorganization uses minimal resources and is automatically performed online, without blocking queries or updates. You should reorganize indexes only if they’re lightly fragmented, otherwise, you should rebuild them. To reorganize an index, run an ALTER INDEX statement and include the keyword REORGANIZE, as shown in the following example: ALTER INDEX PK_StoreContact_CustomerID_ContactID ON AdventureWorks.Sales.StoreContact REORGANIZE Notice that I specify the index name and the table. After I run this statement, I then use the sys.dm_db_index_physical_stats function to retrieve index-related statistics. The results are shown in Table 4. IndexName PK_StoreContact_CustomerID_ContactID PercentFragment 20 TotalFrags 2 PagesPerFrag 2.5 NumPages 5



Table 4: Statistics on the PK_StoreContact_CustomerID_ContactID Index after Reorganization If you compare these results to those shown Table 3, you’ll see that nothing has changed. When you try to reorganize or rebuild an index with few pages, you’ll often see no benefit. However, when I reorganized the PK_Contact_ContactID index on the Person.Contact table, I reduced the number of fragments on 559 pages from 14 to 9 and lowered the percentage of fragmentation by about one-third.



38



Rebuilding Indexes Rebuilding an index is generally more effective than reorganizing it because it drops the original index and builds a new one. As a result, the index starts clean with minimal fragmentation and the pages are compacted, and new pages are allocated as needed. In addition, you can choose to rebuild an index offline (the default) or online. Rebuilding an index can be as simple as reorganizing one. Once again, use the ALTER INDEX statement, but specify the REBUILD keyword, rather than REORGANIZE, as shown in the following example: ALTER INDEX IX_StoreContact_ContactID ON AdventureWorks.Sales.StoreContact REBUILD In this case, I’m rebuilding the IX_StoreContact_ContactID index. When I pull statistics on the rebuilt index, I get the results shown Table 5. IndexName IX_StoreContact_ContactID PercentFragment 50 TotalFrags 2 PagesPerFrag 1 NumPages 2



Table 5: Statistics on the IX_StoreContact_ContactID Index after Rebuilding If you compare the results shown in Table 5 to Table 2, you’ll see that once again, nothing has changed. As with reorganizing an index, you’ll often find that rebuilding an index with few pages will result in little to no benefit. When I rebuilt the PK_Individual_CustomerID index on the Sales.Individual table, I reduced the number of pages from 3082 to 3081 and the number of fragments from 71 to 18. I also reduced the percentage of fragmentation by about 80%. Note, however, that the reason I didn’t use this index originally is because the percentage of fragmentation was already very low. As I mentioned above, the benefit you’ll see from rebuilding (or reorganizing) an index depends on factors such as page count, index size, and Fill Factor. When you rebuild an index, you can include a WITH clause after the REBUILD keyword. In the WITH clause, you can specify one or more options that define how the index will be rebuilt. For example, you can define a Fill Factor, specify whether to recompute the distribution statistics, or specify whether row locks are allowed. In the following ALTER INDEX statement, I include a WITH clause that defines the FILLFACTOR and ONLINE options: ALTER INDEX IX_StoreContact_ContactTypeID ON AdventureWorks.Sales.StoreContact REBUILD WITH ( FILLFACTOR = 70, ONLINE = ON



39



) In this case, the FILLFACTOR option is set to 70, which determines the percentage of how much the leaf level of each index page will be filled during index creation or alteration. As a result, the leaf level will be filled to only 70%. The ONLINE option is set to ON, which means that the StoreContact table and IX_StoreContact_ContactTypeID index will be available for queries and data modification during the index operation. For a complete list of all the options available to the WITH clause, see the topic ―ALTER INDEX (Transact-SQL)‖ in SQL Server Books Online. After I ran the statement above and rebuilt the IX_StoreContact_ContactTypeID index, I retrieved the statistics on the index, as shown in Table 6. IndexName IX_StoreContact_ContactTypeID PercentFragment 33.3333333333333 TotalFrags 2 PagesPerFrag 1.5 NumPages 3



Table 6: Statistics on the IX_StoreContact_ContactTypeID Index after Rebuilding If you compare these results to those in Table 2, you’ll see that the percentage of fragmentation dropped from 50% to 33.3% and the number of pages increased from 2 to 3 as a result of changing the FILLFACTOR. (The default Fill Factor is 100%.) There are still two fragments, but now the average is 1.5 pages per fragment, rather than 1. Another method you can use to rebuild an index is the CREATE INDEX statement. To use this statement, you must create an index by the same name as the original and include the DROP_EXISTING option in the WITH clause, as shown in the following example: CREATE UNIQUE NONCLUSTERED INDEX AK_StoreContact_rowguid ON AdventureWorks.Sales.StoreContact (rowguid ASC) WITH ( DROP_EXISTING = ON, FILLFACTOR = 70, ONLINE = ON ) ON [PRIMARY] In this case, I’m rebuilding the AK_StoreContact_rowguid index, which is a unique, nonclustered index. Notice that the DROP_EXISTING option is set to ON, which means that the original index will be dropped and a new one created. Notice also that I’ve included the FILLFACTOR and ONLINE options in the WITH clause. For a complete list of all the options available to the WITH clause, see the topic ―CREATE INDEX (Transact-SQL)‖ in SQL Server Books Online. Now take a look at Table 7, which shows the index statistics that I retrieved after I rebuilt the index. IndexName AK_StoreContact_rowguid PercentFragment 25 TotalFrags 2 PagesPerFrag 2 NumPages 4



Table 7: Statistics on the AK_StoreContact_rowguid Index after Re-Creating Index



40



Once again, if you compare these results to Table 2, you’ll see that I reduced the percentage of fragmentation from 66.7% to 25%, reduced the number of fragments from 3 to 2, and increased the pages per fragment from 1 to 2 (in part because I reduced the Fill Factor to 70%). Using the CREATE INDEX statement rather than ALTER INDEX statement to rebuild an index has several advantages. The CREATE INDEX statement lets you add or remove key columns, change column order, change the sort order, repartition a partitioned index, and move the index to a different filegroup. However, the advantage to using the ALTER INDEX statement is that you can rebuild more than one index in a single transaction. Note: You can also drop an index and then use the CREATE INDEX statement to re-create that index. However, Microsoft recommends against this approach because the index is offline during the drop and re-creation operations. Also, if the transaction fails, the index is not re-created. Regardless of the approach you use to rebuild an index—or whether you rebuild or reorganize an index—the important point to remember is that you must first properly analyze your indexes, and that’s where the sys.dm_db_index_physical_stats function comes in. The function provides valuable details about all your indexes, and then, from this information, you can determine how to proceed. The actual reorganizing and rebuilding are very straightforward. You can even automate these processes based on the statistics returned by the function. Be sure to refer to SQL Server Books Online as necessary for details about all these operations.



41