Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
Windows Server

SQL Server 2008 : Post-Installation - Preproduction Tasks

6/29/2011 6:18:47 PM
Once you have configured your SQL Server instances, you will want to continue to prepare your server for production. Depending on your environment, configuring SQL Server for production will require various processes and procedures. The following sections will provide you with some of the steps that we go through prior to deployment of our SQL Server instances into production from a server and database perspective.

1. Server Tasks

Following are some things you should do, or at least consider doing, from a server level prior to placing your server into production:

  • Apply Patches: Before your new SQL Server is added to production, ensure that the patch level of the server is at its correct level. We are not recommending that you install a service pack that your environment is not ready for, but consider applying less risky patches like security patches. Applying currently available patches before going live in production should increase the uptime of your new SQL Server because you will only have to bring the server down afterward to apply new patches as they are released by Microsoft.

  • If you have not simulated a workload on your IO subsystem, then we encourage you to go out on the Web and download some free testing tools. Do not wait until SQL Server is running in production to identify problems with the setup and configuration of your IO subsystem. Spend a little time up front, learn how to effectively utilize an IO-testing tool, and make sure that your system is ready for production.

  • Run the Best Practices Analyzer: SQL Server 2000 and SQL Server 2005 both had applications published by Microsoft that compared the SQL Server and Windows configuration settings against Microsoft's recommended best practices to determine if there were potential issues. In SQL Server 2008, use some of the predefined policies of Policy Based Management  to ensure that your SQL Server instance is in line with Microsoft best practices. You can also review the options checked by SQL Server 2005's Best Practices Analyzer and ensure that you are following the best practice items that you are concerned about. Make the time to validate your server against Microsoft's best practices, even if doing so requires more work than you would prefer. The amount of time that a best practices check can potentially save you in the long run is well worth the effort.

2. Database Tasks

Following are some tasks that you should perform for each database that you plan to deploy into production. The tasks are not mandatory, but they can prove beneficial and can save you headaches down the road.

  • Size data and log files: When creating new databases, options exist to determine the initial size of the database data and log files to the size of your choice. If you have a general idea about the expected size of the database, then take advantage of this option and allocate space for the data and log files, respectively. By initializing the data and log files to a projected size, you will minimize the number of data and log file growths because of the database needing more space. Saving data and log file growths will ultimately save CPU cycles and disk IO, dedicating more resources to processing application requests.

  • Enable Autogrowth: Many database administrators argue that you should not set up your database data and log files to autogrow by a certain percentage or fixed size. We have heard many administrators say that you should monitor your files enough so that you can grow them when necessary. Our advice to you is to set up the autogrowth option in whatever way that enables you to keep production running (see Figure 1 for an example). If you manage multiple servers and are constantly fighting fires, chances are that you don't have time to monitor data and log files to grow them manually. So set up the autopilot and ensure that your production databases keep running.

Figure 1. Autogrowth options for a log file

The flip side of that coin is to periodically review the autogrowth setting that you have set up to ensure that you are not wasting large amounts of space and increasing disk defragmentation. Growing your database and log files by 10% to 20% may have made sense when your database was 10 to 20 GB. Now that your database is 100 GB, you may want to consider revising some of those percentages. Regardless of what option you select, make sure you educate yourself enough to understand what options are available to you in the event that you manage the data growth. Keep in mind, your data drive and log drive may be running out of space because of the autogrowth option selected. Modifying how your database and log files grow may buy you some more time before needing to purchase additional space.

  • Disable autoshrink: Shrinking your data or log files is a process that removes any unused free space from those files. Configuring the database to automatically shrink the data and log files is simply a bad idea. The main reason why we do not like to enable this option stems from the lack of control over when the process will start. Unfortunately, we have seen this option cause problems in our environment.

  • Set key database options: In SQL Server, options are available that allow you to configure the behavior of a database. Following are some of the options that we frequently specify.

    Auto Create Statistics:

    Automatically creates statistics on columns without indexes that are used in the where clause of queries.

    Auto Update Statistics:

    Automatically updates statistics needed by queries by the optimizer based on changes within the data.

    Cursor Default:

    Allows cursors created within a single procedure or trigger to be referenced by any process using that connection.


    Determines how queries will be parameterized via default rules for the database or all queries in the database.


    Based on your data recovery requirements, this option determines how the transaction log will be used.

    SQL Options:

    Various options that can be enabled or disabled for the entire database, depending on the requirements you have been supplied with.

3. Maintenance and Monitoring

Database administrators must ensure that their databases are available to respond to application queries at the required response rate and that data can be recovered in the event of server failure. To that end, you should at least do the following before going into production with a new server and database:

  • Create a backup and recovery strategy: Fortunately, Microsoft provides methods for backing up and maintaining your databases. However, at a minimum, before going into production, you definitely want to create a backup strategy that meets the business requirements.

  • Create a plan to maintain indexes and integrity: Commands and features exist to maintain indexes and check the integrity of your databases. You should put together a plan to accomplish these.

  • Create processes to monitor your server: Setting up processes to monitor and capture SQL Server performance is critical to supporting and base-lining the application. In a perfect world, organizations would purchase software to aid in the monitoring of your server. Unfortunately, purchasing software is not always an option, but expectations still exist for you to proactively monitor your system to identify problems.

Other -----------------
- Microsoft PowerPoint 2010 : Expanding PowerPoint Functionality - Simplifying Tasks with Macros
- Microsoft PowerPoint 2010 : Enhancing a Presentation with VBA & Setting Developer Options
- Windows Server 2008 R2 : Manage Disk Storage - Manage Disk Storage Quotas
- Windows Server 2008 R2 : Work with RAID Volumes - Understand RAID Levels & Implement RAID
- Exchange Server 2010 : Perform Essential Public Folder Management (part 3) - Configure Client Connectivity
- Exchange Server 2010 : Perform Essential Public Folder Management (part 2) - Define Public Folder Permissions
- Exchange Server 2010 : Perform Essential Public Folder Management (part 1) - Manage Public Folder Content
- Microsoft Dynamics CRM 2011 : Using Microsoft Dynamics CRM for Outlook - Configuring Synchronization Filters
- Microsoft Dynamics CRM 2011 : Going Offline with Microsoft Dynamics CRM for Outlook
- Microsoft Dynamics CRM 2011 : Deleting Records in Microsoft Dynamics CRM for Outlook
- SharePoint 2010 : Securing a Web Application (part 2) - Managing Web Part Security & Self-Service Site Creation
- SharePoint 2010 : Securing a Web Application (part 1) - How Zones, Web Applications, and Security Work Together to Provide Secure Solutions
- BizTalk 2009 : WCF LOB Adapter SDK (part 6)
- BizTalk 2009 : WCF LOB Adapter SDK (part 5) - Implementing the Message Exchange Handlers
- BizTalk 2009 : WCF LOB Adapter SDK (part 4) - Implementing the Metadata Handlers
- BizTalk 2009 : WCF LOB Adapter SDK (part 3) - Implementing the Connection
- BizTalk 2009 : WCF LOB Adapter SDK (part 2) - UI Logical Grouping
- BizTalk 2009 : WCF LOB Adapter SDK (part 1) - Generating Adapter Skeleton Code
- SQL Server 2008 : Configuring the Instance (part 3)
- SQL Server 2008 : Configuring the Instance (part 2) - Specifying the Backup Compression Default & Enabling Login Failure Auditing
Most view of day
- Workflow in Dynamics AX : Dynamics AX 2009 Workflow Infrastructure
- Adobe Dreamweaver CS5 : Using Library Items and Server-side Includes (part 3) - Using the Library Assets Panel - Deleting an item from the Library , Renaming a Library item
- Microsoft Dynamics GP 2010 : Network requirements, The Terminal Server only approach, Shared files, Data backups
- Windows Server 2012 Administration : Configuring Sites (part 3) - Establishing Site Links, Delegating Control at the Site Level
- Microsoft Excel 2010 : Using Formulas - Entering a Formula
- Windows Phone 8 : Orientation and the PhoneApplicationPage Class (part 4) - Animating Page Elements When the Page Orientation Changes
- Configuring Startup and Troubleshooting Startup Issues : What’s New with Windows Vista Startup
- CorelDRAW X5 : Font Etiquette - Using Fonts with Style and Appropriateness
- Windows Server 2008 R2 : Hyper-V feature focus - Introduction to Virtualization and Hyper-V, Hyper-V Changes
- Sharepoint 2013 : Service Application Fundamentals (part 2) - The Connection Structure - Tying It Up with an Example
Top 10
- Windows Phone 8 : Scheduled Tasks - Scheduled Task API Limitations
- Windows Phone 8 : Scheduled Tasks - Updating Tiles Using a Scheduled Task Agent
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 5) - Editing an Existing To-Do Item
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 4) - Creating the To-Do Item Shell Tile, Saving a To-Do Item
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 3) - Debugging Scheduled Tasks
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 2) - TodoService, TodoItemViewModel
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 1) - TodoItem,TodoDataContext
- Windows Phone 8 : Scheduled Tasks - Using Scheduled Tasks
- Windows Phone 8 : Scheduled Tasks - Background Agent Types
- Windows Phone 8 : Windows Phone Toolkit Animated Page Transitions - Reusing the Transition Attached Properties
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro