10 Things Every Production SQL Server Should Have

The Bare Essentials Every SQL Server Production Server Should Have. We will start with the single MOST important facet to every SQL Server DBAs job. Backups. We will cover the different kinds of backups and discuss how often we should be taking them.

1. Backups
2. Updating Statistics
3. DBCC CHECKDB
4. Rebuild Indexes
5. Change the Default Growth Settings
6. Change Error Detection On Every Database
7. Catch Critical Errors In the Error log
8. Testing Out Restores
9. Setting Up Database Mail

Backups :- 

• A SQL Server database must have at minimum 2 files.
• A data file with the extension of .mdf and a log file with the extension of .ldf.
• A database can have more than 1 data file and the extension for secondary data file .ndf.
• A database can have more than 1 log file but it's very rare when a secondary log file is necessary.
• For the vast majority of SQL Server databases 1 log file should be the default.
• There are three major types of backups.The full backup is a point it time snapshot of the data and all the objects in it.The differential is a backup of all the data and schema since the last FULL backup.The final type of backup is the transaction log and it records all transactions and the database modifications made by each transaction.
• Before you begin any type of restore sequence you must have a full backup.
• The maintenance plan wizard provides us an easy way to create our back up strategy.
• It's a good idea to have separate jobs for each action so you know exactly what failed.

Updating Statistics :- 

• The Update Statistics tasks ensures the query optimizer has up to date information about the distribution of data values in the tables. This allows the optimizer to make better judgement about data access strategies.
• The query optimizer uses statistics to estimate the number of rows returned by that query.
• Row estimates enable the optimizer to create high quality plans.
• If the optimizer has the right information it could choose a seek over a scan.
• We can use the maintenance plan wizard to update our statistics.
• We can also use custom scripts to do it.
• Statistics are meta-data.
• Don't underestimate the importance of updating statistics.

Consistency Checks :-

The overwhelming factor in how often to run consistency checks is you. How comfortable are you with the integrity of your I/O subsystem and your ability to recover from a corruption problem. If you have corruptions in your database today, you'll probably run DBCC CHECKDB on it every day for a month, right?"

In the real world, the vast number of IO subsystems aren't so healthy. The ones that are healthy have a lot going on thanks to the shared tenant model. Just throw it on the SAN... it will take it.

So, on most real world databases I would seriously recommend you run CHECKDB every day. If it simply isn't pragmatic then I would run it as often as you could.

• The vast majority of database corruption occurs at the IO subsystem level.
• There is a lot of software between the page in memory and it's home on disk.
• Ideally, you should run CHECKDB on a daily basis.
• If you can't do it daily then run it as often as you can.
• Consistency checks are run to help ensure the health of our data and database schema.
• SQL Server stores data on pages.
• These pages live in the buffer pool and on disk.
• CHECKDB in it's base form does NOT cause blocking.
• When pages are flushed to disk they have to traverse a lot of software on their way down and back from the IO subsystem.

Rebuild or Reorganize :- 

• We have two options to clean up our indexes. One is to rebuild them, the second to reogranize them.
• Both reduce the level of fragmentation in your indexes.
• An rebuild creates a fresh new index structure.
• A rebuild is often the cleanest approach to removing fragmentation.
• Rebuilds are an offline operations. Locks are held.
• At any point you decide to rollback a rebuild, it all gets rolled back. So, it's all or nothing.
• A reorganization is an online process.
• A reorganization can be stopped at any point and the most it will rollback is one or two pages.
• A reorganization is more lightweight and runs at the leaf level of an index.
• Often times a reorganization takes longer on larger tables.
• We can use the maintenance plan wizard to create jobs for both rebuilds and reorganizations.
• The best approach will be to test each approach in your environment.
• You DON'T have to update your stats on the night you rebuild your indexes. A rebuild updates stats with a full scan.

Default Growth Settings :-

• When you create a database the default growth is one megabyte.
• One megabyte growth increments can create "SAN" storms.
• A "SAN" storm happens when many databases grow in small increments during core hours. This can overload a SAN causing performance to degrade.
• One megabyte growth increments can also lead to performance tuning problems.
• Ideally, you should pre-grow data and log files to prevent any kind of growth during core hours.

Enabling Check sums for Error Detection :- 

• CHECKSUMS are enabled at the database level.
• A checkusm is calculated over the page's contents and stamped on the page.
• When the page is read back into the buffer pool from the IO stack the checksum is verified.
• If the numbers don't match up then something on the IO stack must have corrupted it.
• CHECKSUMS will also detect torn pages.
• You enable either checksums, torn pages or none but not a combination of any of them.
• There will be some CPU overhead associated with adding them.
• If you are concerned about the overhead then test it with a few databases first.
• Keep in mind, the vast majority of SQL boxes in the wild aren't CPU constrained so this overhead should have no real world impact.

SQL Agent Operator  :- 

• To use alerts you must have Database Mail and a SQL Agent Operator configured.
• You can create the Operator using SSMS or T-SQL.
• Within SSMS navigate to the SQL Server Agent, right click on Operator and chose New Operator.
• Once you have Database Mail and the Operator configured you can create the alerts and assign them to the Operator.
  
• Thankfully for us the work has been done defining the various errors and creating alerts for them. (Thanks Tim and Glenn)
• Severity 10 is informational while 19-25 are fatal and you will want to be notified when those errors arise.
• Any time one of those alerts are triggered an email will be sent to you.
• Many organizations have a distribution list so that all teams members can have insight into the errors.

Testing Restores  :- 

• Testing your restores is critical to ensuring your databases can be recovered in the event of an outage.
• Test your restores on another server.
• If you are backing up to a SAN try to restore over the network for a more realistic restore time.
• If you are backing up to a SAN you can also copy the .bak file to your dev or test server. Just remember to include this time in your restore window when discussing your actions with the business.
• Test restores completed by your maintenance plan or whatever approach you're taking to backup your databases.
• It's a good Idea to run consistency checks on that restored database.
• Keep in mind that CHECKDB is resource intensive and you should make an effort to ensure you are the only one using the box when running these checks.
• If you find errors in CHECKDB and aren't comfortable with fixing them then it may be a good opportunity to bring in a senior level resource that is.
• It's been my experience that companies will overlook minor mistakes a good DBA makes. However, losing or failing to restore their data ins't one of them.

Database Email Setup :-

• The database mail feature is not enabled by default.
• A dialog box will ask if you want to enable it. Choose Yes.
• The mail service uses SMTP to send out emails.
• You can set up database mail via the GUI or via transact.
• When you set up mail you'll need two items. An account and a profile. All can be done via the GUI.
• When you set up the profile you'll need to have the one you are using as the default.
• Database mail can be temperamental so be patient.
• When you set up database mail to email you when a job fails you'll need an operator.
• Creating an operator is simple. Just right click on operator and select new operator.
• You'll need the email address of the account your using for mail.
• When you set up mail in the enterprise you'll need to ask your Exchange (or mail admin) to provide you with an account name, your mail server and the port.