With compatibility issues arising between various versions of the platform, and the emergence and evolution of new features and tools, managing a Microsoft (MS) SQL Server and its associated database functions may sometimes feel like playing a game of “Catch Up.”
We’ve assembled some outline tips and best practices, to assist you. Full details on the syntax and procedures required for implementing these various recommendations may be found on Microsoft’s archive of SQL Server Tips
.
Can’t Update, So Migrate
SQL Server 2000 log shipping can’t be directly updated to SQL Server 2008 log shipping. So you’ll need to migrate (export) your legacy shipping configuration to MS SQL Server 2008.
Managing Data & Log Files
To reduce the risk of fragmentation, data and log files should be created with an initial size which takes into account their anticipated size as your database grows. This may be a simple enough projection for data files, but for log files you’ll need to consider factors like transaction size, and the planned frequency of log backups.
Configuring Auto-Growth
File sizes should be periodically monitored, using manual intervention to grow them at a set time each day. Auto-grow should be enabled as a fall-back option, in case files need to adjust their size to cater for abnormal events. It should be set to a specific value (file size), rather than a percentage. Using Auto-grow exclusively may lead to file fragmentation, and the unanticipated slowing of application workloads.
Configuring Instant File Initialization
Enabling instant file initialization avoids the zero-initializing of files, and allows manual and Auto-growth of files to be virtually instantaneous.
Disable Shrinkage
Auto-shrink should be disabled, and using shrinkage to reduce the size of data or log files (which drains system resources, induces logical scan fragmentation in data files, and can hinder overall performance) should not figure in your maintenance plan.
Use ALTER DATABASE To Your Advantage
The ALTER DATABASE set of commands may be used to disable the dreaded Auto-shrink function (ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;).
It can also be used to move database files and full-text catalogs in SQL Server 2008 R2.
Rebuilding An Index, To Reduce Fragmentation
Internal fragmentation resulting from data modifications (inserts, updates, deletes, etc.) can leave empty space on a page which, accumulated over the entire database, may potentially leave significant volumes of storage space unused. Logical scan and extent scan fragmentation within index or table structures composed of pages may occur when a page split operation leaves portions of a record in non-contiguous sections of your data storage.
Rebuilding an index is an effective way to reduce or eliminate fragmentation. With SQL Server, the process requires you to have additional storage space available that’s equivalent to the index you’re rebuilding, as the platform creates a new index before discarding the old one. As from MS SQL Server 2005 Enterprise Edition onward, index rebuilding may be done online, with some restrictions.
Reorganizing An Index, To Reduce Fragmentation
Reorganizing compacts and defragments an index, using an in-place algorithm to reduce fragmentation. The process needs only 8KB of additional space to run, and takes place online on all versions of the platform since SQL Server 2000.
Selecting Your Maintenance Approach
The DMV sys.dm_db_index_physical_stats (DBCC SHOWCONTIG in SQL Server 2000) may be invoked periodically to determine which of your indexes are fragmented, and which method (rebuild or reorganize) is most appropriate for a fix.
Updating Statistics
If you’re performing defragmentation operations on a regular basis, it’s important to maintain accurate statistics (descriptors for the distribution of data values for columns within a table or index) for each new index created. These statistics should be updated manually, for each index that wasn’t rebuilt, and for all non-indexed columns.
Checking For Torn Pages And Checksums
Power outages and other unforeseen events may interrupt the data input from a disk drive, leaving partially written sectors of a new page on an existing page image – what’s known as a “torn page.” Torn-page detection should be enabled for SQL Server 2000.
SQL Server 2005 and later versions have a feature which creates a characteristic checksum for each page written, which is compared to the current page image to test its integrity – and this feature should be enabled. The DBCC CHECKDB command parameter will ensure that all pages are read and checked for integrity.
Use SQL Server Management Studio (SSMS)
SQL Server Management Studio (SSMS) enables administrators to navigate through database objects, or write and run queries. But there are other functions it can perform, such as speeding up your server connections, scripting multiple objects, and making it easier to work on large queries.
Disable Features, for Security
You can reduce the attack surface your database presents to hackers and malicious insiders , by using the “sp_configure stored” procedure to disable unnecessary system features.
Back Up Comprehensively, And Often
The BACKUP DATABASE command should be a regular feature on your schedule – using the WITH CHECKSUM option to avoid the risk of corrupted pages, and to ensure that there’s a clean recovery point in the event of a disaster. You should store several days of backups in a secure location, for redundancy.
Monitor the Database
Use a database size monitoring product (like PA Server Monitor’s Database Monitor ) to make sure the database doesn’t grow larger than expected. Also monitor that backups are happening as expected.
In Case of Emergency
If the transaction log for your database sustains damage, and there are no backups available to restore from, EMERGENCY Mode is your best option for effecting repairs.
Des Nnorchiri
Des Nnochiri has a Master’s Degree (MEng) in Civil Engineering with Architecture, and spent several years at the Architectural Association, in London. He views technology with a designer’s eye, and is very keen on software and solutions which put a new wrinkle on established ideas and practices. He now writes for markITwrite across the full spectrum of corporate tech and design. In previous lives, he has served as a Web designer, and an IT consultant to The Learning Paper, a UK-based charity extending educational resources to underprivileged youngsters in West Africa. A film buff and crime fiction aficionado, Des moonlights as a novelist and screenwriter. His short thriller, “Trick” was filmed in 2011 by Shooting Incident Productions, who do location work on “Emmerdale”.