{"id":5191,"date":"2017-09-25T09:46:41","date_gmt":"2017-09-25T14:46:41","guid":{"rendered":"https:\/\/www.poweradmin.com\/blog\/?p=5191"},"modified":"2017-09-25T09:46:41","modified_gmt":"2017-09-25T14:46:41","slug":"best-tips-for-managing-an-ms-sql-server","status":"publish","type":"post","link":"https:\/\/www.poweradmin.com\/blog\/best-tips-for-managing-an-ms-sql-server\/","title":{"rendered":"Best Tips for Managing an MS SQL Server"},"content":{"rendered":"

\"Database<\/a>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 \u201cCatch Up.\u201d<\/span><\/p>\n

\u00a0<\/p>\n

We\u2019ve 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\u2019s archive of SQL Server Tips<\/a>.<\/span><\/p>\n

Can\u2019t Update, So Migrate<\/span><\/h2>\n

SQL Server 2000 log shipping can\u2019t be directly updated to SQL Server 2008 log shipping. So you\u2019ll need to migrate (export) your legacy shipping configuration to MS SQL Server 2008.<\/span><\/p>\n

Managing Data & Log Files<\/span><\/h2>\n

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\u2019ll need to consider factors like transaction size, and the planned frequency of log backups.<\/span><\/p>\n

Configuring Auto-Growth<\/span><\/h2>\n

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.<\/span><\/p>\n

Configuring Instant File Initialization<\/span><\/h2>\n

Enabling instant file initialization avoids the zero-initializing of files, and allows manual and Auto-growth of files to be virtually instantaneous.<\/span><\/p>\n

Disable Shrinkage<\/span><\/h2>\n

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.<\/span><\/p>\n

Use ALTER DATABASE To Your Advantage<\/span><\/h2>\n

The ALTER DATABASE set of commands may be used to disable the dreaded Auto-shrink function (ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;).<\/span><\/p>\n

It can also be used to move database files and full-text catalogs in SQL Server 2008 R2.<\/span><\/p>\n

Rebuilding An Index, To Reduce Fragmentation<\/span><\/h2>\n

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.<\/span><\/p>\n

\u00a0<\/p>\n

Rebuilding an index is an effective way to reduce or eliminate fragmentation<\/a>. With SQL Server, the process requires you to have additional storage space available that\u2019s equivalent to the index you\u2019re 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.<\/span><\/p>\n

Reorganizing An Index, To Reduce Fragmentation<\/span><\/h2>\n

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.<\/span><\/p>\n

Selecting Your Maintenance Approach<\/span><\/h2>\n

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.<\/span><\/p>\n

Updating Statistics<\/span><\/h2>\n

If you\u2019re performing defragmentation operations on a regular basis, it\u2019s 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\u2019t rebuilt, and for all non-indexed columns.<\/span><\/p>\n

Checking For Torn Pages And Checksums<\/span><\/h2>\n

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 \u2013 what\u2019s known as a \u201ctorn page.\u201d Torn-page detection should be enabled for SQL Server 2000.<\/span><\/p>\n

\u00a0<\/p>\n

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 \u2013 and this feature should be enabled. The DBCC CHECKDB command parameter will ensure that all pages are read and checked for integrity.<\/span><\/p>\n

Use SQL Server Management Studio (SSMS)<\/span><\/h2>\n

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<\/a>, such as speeding up your server connections, scripting multiple objects, and making it easier to work on large queries.<\/span><\/p>\n

Disable Features, for Security<\/span><\/h2>\n

You can reduce the attack surface your database presents to hackers and malicious insiders , by using the \u201csp_configure stored\u201d procedure to disable unnecessary system features.<\/span><\/p>\n

Back Up Comprehensively, And Often<\/span><\/h2>\n

The BACKUP DATABASE command should be a regular feature on your schedule \u2013 using the WITH CHECKSUM option to avoid the risk of corrupted pages, and to ensure that there\u2019s a clean recovery point in the event of a disaster. You should store several days of backups in a secure location, for redundancy.<\/span><\/p>\n

Monitor the Database<\/span><\/h2>\n

Use a database size monitoring product (like PA Server Monitor\u2019s Database Monitor<\/a>\u00a0) to make sure the database doesn\u2019t grow larger than expected. \u00a0Also monitor that backups are happening as expected.<\/span><\/p>\n

In Case of Emergency<\/span><\/h2>\n

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.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

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 \u201cCatch Up.\u201d \u00a0 We\u2019ve assembled some outline tips and best practices, to assist you. Full details […]<\/p>\n","protected":false},"author":10,"featured_media":5193,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,9,8],"tags":[],"class_list":["post-5191","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-how-to","category-technical","category-windows"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts\/5191","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/comments?post=5191"}],"version-history":[{"count":4,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts\/5191\/revisions"}],"predecessor-version":[{"id":5194,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/posts\/5191\/revisions\/5194"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/media\/5193"}],"wp:attachment":[{"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/media?parent=5191"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/categories?post=5191"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.poweradmin.com\/blog\/wp-json\/wp\/v2\/tags?post=5191"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}