SQLServerPedia

Welcome to part 3 in this series on a thought process around SQL Server Installation. In Part 1, we started talking about some basic steps and a thought process for a repeatable success story with your SQL Server installations. Part 2 discussed security, playing around before doing a first install and smoke testing afterwards.

Today we’ll close the discussion with some talk about maintenance, defaults and some post installation tips.

“The install is done, here you go”

Wait! You clicked finish, you reviewed the logs and tested connectivity like we talked about in Part 2. You aren’t ready to deliver yet though. Let’s cover a few things first. This, again, is not meant to be exhaustive but to help you think about the full process. Check out the documentation and blogs and explore in a dev environment.

Defaults

If you just did the installation and changed a few things like some of the directories and options, you don’t have all of the defaults but there are things to look at after the installation. There are defaults to change, or at least understand and consider. Let’s hit a few of them here.

Recovery Model

As the model database goes, so goes each new database you create (without manually changing it). The Model database is just that – a model database. Add a table to model and each new database you create will have that table in it. The same goes for recovery model. On all new installations of SQL Server the recovery model for the model database is “FULL” out of the box. This means that every new database you create will start out in FULL recovery model and the log file will continue to grow (after you take your first full backup — until you take a full backup you are not truly in full recovery mode either when first starting out or when switching to full from simple). This is important to consider because if you are only planning on ever doing full backups and your recovery plans only have you recovering to a daily full backup, for example, you will see your log file grow to a point that can get out of control. (Then you have to start playing with -ugh- shrinking)

If you don’t want this then change your recovery model. If you don’t know what it all means, start with books online.

Database Sizes

By default new database files are set to a small size (2MB for data, 1MB for log). Don’t accept the defaults here. How big will your database grow to? What kind of transaction log space do you expect to need? Plan for that growth, add an overage percentage and “right-size” your files at the beginning. This prevents file fragmentation from frequent growths each time the file needs to grow and it helps give you peace of mind in knowing your database is right sized from the beginning.

For the Log File, you may want to check out this post by Kimberly L. Tripp on some tips for right sizing the transaction log.

Database Locations

Hopefully you setup a separate drive each for data files, log files and tempdb. That is generally a best practice though in lighter workloads it may not be absolutely necessary to split out tempdb. I say for log files it is always good from a performance and recovery standpoint to split log files from data files.

Where is your tempdb file? Do you need to move it to the drive you requested and setup for tempdb? (Check out this books online article on Alter Database. There is an example there for moving tempdb, in fact)

Are your default directories for new database log and data files setup right? (Right click on the server in SQL Server Management Studio –> Go to Database Settings –> What’s specified for the default directories?)

Data and Log File Growth

The default growth options for data and log files are likely not what you want to see. While it’s important to “right-size”, I am also of the opinion that you should allow for autogrowth as an insurance policy.  I just don’t like the default growth sizes (1MB for data files and 10% for log files). Instead I like to specify an amount that allows a “healthy” growth for that particular database and workload while looking to minimize file fragmentation from small chunks grown at a time. This varies from database to database. On an environment I just configured for a database that started at 25GB data and 5GB log file I chose 500MB and 250MB respectively.

I like to setup alerts for autogrowth operations with a monitoring tool watching my SQL environments. I like the insurance policy but in my mind using the insurance means there was a problem with the right-sizing.

Backups (or lack thereof)

We just talked above about understanding your recovery model. That is the first step in the game. Step two is setting up a backup strategy that matches your recovery strategy. My philosophy is that people focus on their backups too much.  What I mean by that is – remember why we take backups – we take them to have a way of recovering our databases in a crisis. If you can remember that, your backup plan should come together and you should have backups setup and running in your new instance sooner than most other tasks.

Maintenance

There isn’t any being done yet. You can change the defaults above and deliver, but you’ll be getting to know this newly created instance at some point in production. Could it be a need to recover from failure? Maybe performance was fine and just started getting worse all of the sudden? SQL Server databases, like most other tools and applications, require regular maintenance. Let’s briefly talk about some of the quick hits I throw on most instances as a basic rule.

Backups and history maintenance

Can’t have too many reminders about backups. Make sure you are taking backups and have them running in an automated fashion.

Also make sure you are cleaning up your backup history. There is a great blog post by Jonathan Kehayias here. Rather than regurgitate or try my own attempt, check it out.

Index and Statistics Maintenance

Your databases should have indexes for performance minded querying of data. They also help in the performance of updates (which are essentially making changes to data based on a query of that data) and having a clustered index can sometimes mean an improvement to inserts and deletes as well.

They grow less useful if your indexes are heavily fragmented and more work is required when working with an index than would otherwise be necessary. Look at rebuilding or reorganizing indexes as time allows. A great script by Michelle Ufford for this is here.

Statistics also help your database performance. Without them, the optimizer has a lot less to work with. Statistics help show the optimizer how unique your data is, how many qualifying rows could be found for queries, etc. They can get out of data even with auto update statistics set on (the default behavior). Updating statistics is a good practice to get into with maintenance as well. Paul Randal blogs about the debate on what to do first (statistics update or index rebuild).

Error Log Recycles

Look up sp_cycle_errorlog and sp_cycle_agent_errorlog in books online. They are system stored procedures that do what they sounds like. They cycle the error logs in SQL Server and the SQL Server Agent. If you don’t do this from time to time the error logs can get a little too large to easily scroll through if you don’t restart your instance a lot (the error logs are reset each time your instance is restarted).

I typically recycle them weekly, especially in environments where I don’t have a monitoring tool and/or I do daily log checks on production environments ensuring no issues.

I also increase the number of error logs to a new number. If someone were to compromise a SQL environment they could theoretically run the sp_cycle_errorlog command the right number of times and some of their tracks are gone. I increase this to help alleviate that. It’s not ironclad but it is one more way to help harden SQL Server or at least provide more of a forensics ability.

DBCC CHECKDB

I don’t like database corruption. It doesn’t happen much and I take frequent backups to help alleviate potential data loss. Database corruption is rare nowadays. With each new release of SQL it seems to become rarer and rarer. But… It happens.

I like the comfort of knowing I check my databases for corruption as frequently as I can. I will typically setup a job to perform a “DBCC CHECKDB with no_infomsgs” on all of my databases as frequently as I can. While DBCC CHECKDB can be run concurrently with users in the system, I like to pick either low use or no use times to run it, especially for larger databases. I try to run it at least weekly and the more frequently, the better.

This will help my chances of detecting corruption before a user does.

Wake me up, as you go-go

(Is it wrong to paraphrase a wham song in public? I grew up in part of the 80s, but I digress…)

What kind of monitoring do you use at your company? There are a lot of great tools out there to use. There are even homegrown approaches you can find on SQLServerPedia or in articles at SQL Server Central.

There are pros and cons to many of the tools and some quick web searches will show you a lot of these. The point is – use something!

Don’t rely on the users to tell you when you have a problem or when a job fails. Before I hand an environment over to a project team or users, I like to know that I will find out if a job runs too long, a job finishes too quickly, the CPU is pegged for a substantial amount of time, the SQL Services become unavailable, etc.

It bears restating: Discover your problems before your users report them to you. (Or at least have a chance to do so!)

—–

Those are the basics I like to focus on when installing SQL Server. I am sure there are items that others look at as well. Please feel free to share them in the comments below. Thanks for reading the series and happy installations!

If you have any problems, feel free to leave a comment – I check regularly and have no problem helping you out with an issue during installation with some advice, clarification or a point in the right direction.

Some Related Thoughts

• While Talking about planning, do you ask potential or new vendors any questions about their database needs?
• Do you know about all of the SQL installations in your network? As a DBA, you should.
• Are you a new DBA? Maybe a sysadmin wearing a DBA’s hat? Here are some tips on where to start.
• Doctors, Chefs and Pilots can teach us DBAs a thing or two about Checklists, Recipes and Algorithms. Can be very useful in an installation or setup process.
• I used to hate documentation. I now see it as something to help me get help and have someone else do the next installation. Embrace documentation.
• Part one of this series – preparing for SQL Server installation by planning.
• Part two of this series – securing and testing your SQL Server instance after installation.

Want more content like this? Subscribe to this blog’s feed in your favorite feed reader and stay up to date with tips and tricks. I like to share techniques and free tools I use to help me with my day job.

Welcome to this series on a thought process around SQL Server Installation. In Part 1, we started talking about some basic steps and a thought process for a repeatable success story with your SQL Server installations. Part 2 discussed security, playing around before doing a first install and smoke testing afterwards.

Today we'll close the discussion with some talk about maintenance, defaults and some post installation tips.

"The install is done, here you go"

Wait! You clicked finish, you reviewed the logs and tested connectivity like we talked about in Part 2. You aren't ready to deliver yet though. Let's cover a few things first. This, again, is not meant to be exhaustive but to help you think about the full process. Check out the documentation and blogs and explore in a dev environment.

Defaults

If you just did the installation and changed a few things like some of the directories and options, you don't have all of the defaults but there are things to look at after the installation. There are defaults to change, or at least understand and consider. Let's hit a few of them here.

Recovery Model

As model goes, so goes each new database you create (without manually changing). The Model database is just that - a model database. Add a table to model and each new database you create will have that table in it. The same goes for recovery model. On all new installations of SQL Server the recovery model for the model database is "FULL". This means that every new database you create will start out in FULL recovery model and the log file will continue to grwo (after you take your first full backup -- until you take a full backup you are not truly in full recovery mode either when first starting out or when switching to full from simple). This is important to consider because if you are only planning on ever doing full backups and your recovery plans only have you recovering to a daily full backup, for example, you will see your log file grow to a point that can get out of control. (Then you have to start playing with -ugh- shrinking)

If you don't want this then change your recovery model. If you don't know, start with books online.

Database Sizes

By default new database files are set to a small size (2MB for data, 1MB for log). Don't accept the defaults here. How big will your database grow to? What kind of transaction log space do you expect to need? Plan for that growth, add an overage percentage and "right-size" your files at the beginning. This prevents file fragmentation from frequent growths each time the file needs to grow and it helps give you peace of mind in knowing your database is right sized from the beginning.

For the Log File, you may want to check out this post by Kimberly L. Tripp on some tips for right sizing the transaction log.

Database Locations

Hopefully you setup a separate drive each for data files, log files and tempdb. That is generally a best practice though in lighter workloads it may not be absolutely necessary to split out tempdb. I say for log files it is always good from a performance and recovery standpoint to split log files from data files.

Where is your tempdb file? Do you need to move it to the drive you requested and setup for tempdb? (Check out this books online article on Alter Database. There is an example there for moving tempdb, in fact)

Are you default directories for new database log and data files setup right? (Right click on the server in SQL Server Management Studio --> Go to Database Settings --> What's specified for the default directories?)

Data and Log File Growth

The default growth options for data and log files are likely not what you want to see. While it's important to "right-size", I am also of the opinion that you should allow for autogrowth as an insurance policy.  I just don't like the default growth sizes (1MB for data files and 10% for log files). Instead I like to specify an amount that allows a "healthy" growth for that particular database and workload while looking to minimize file fragmentation from small chunks grown at a time. This varies from database to database. On an environment I just configured for a database that started at 25GB data and 5GB log file I chose 500MB and 250MB respectively.

I like to setup alerts for autogrowth operations with a monitoring tool watching my SQL environments. I like the insurance policy but in my mind using the insurance means there was a problem with the right-sizing.

Backups (or lack thereof)

We just talked above about understanding your recovery model. That is the first step in the game. Step two is setting up a backup strategy that matches your recovery strategy. My philosophy is that people focus on their backups too much. I blogged about it here. What I mean by that is - remember why we take backups - we take them to have a way of recovering our databases in a crisis. If you can remember that, your backup plan should come together and you should have backups setup and running in your new instance sooner than most other tasks.

Maintenance

There isn't any being done yet. You can change the defaults above and deliver, but you'll be getting to know this newly created instance at some point in production. Could it be a need to recover from failure? Maybe performance was fine and just started getting worse all of the sudden? SQL Server databases, like most other tools and applications, require regular maintenance. Let's briefly talk about some of the quick hits I throw on most instances as a basic rule.

Backups and history maintenance

Can't have too many reminders about backups. Make sure you are taking backups and have them running in an automated fashion.

Also make sure you are cleaning up your backup history. There is a great blog post by Jonathan Kehayias here. Rather than regurgitate or try my own attempt, check it out.

Index and Statistics Maintenance

Your databases should have indexes for performance minded querying of data. They also help in the performance of updates (which are essentially making changes to data based on a query of that data) and having a clustered index can sometimes mean an improvement to inserts and deletes as well.

They grow less useful if your indexes are heavily fragmented and more work is required when working with an index than would otherwise be necessary. Look at rebuilding or reorganizing indexes as time allows. A great script by Michelle Ufford for this is here.

Statistics also help your database performance. Without them, the optimizer has a lot less to work with. Statistics help show the optimizer how unique your data is, how many qualifying rows could be found for queries, etc. They can get out of data even with auto update statistics set on (the default behavior). Updating statistics is a good practice to get into with maintenance as well. Paul Randal blogs about the debate on what to do first (statistics update or index rebuild).

Error Log Recycles

Look up sp_cycle_errorlog and sp_cycle_agent_errorlog in books online. They are system stored procedures that do what it sounds like. They cycle the error logs in SQL Server and the SQL Server Agent. If you don't do this from time to time the error logs can get a little too large to easily scroll through if you don't restart your instance a lot (the error logs are reset each time your instance is restarted).

I typically recycle them weekly, especially in environments where I don't have a monitoring tool and/or I do daily log checks on production environments ensuring no issues.

I also increase the number of error logs to a new number. If someone were to compromise a SQL environment they could theoretically run the sp_cycle_errorlog command the right number of times and some of their tracks are gone. I increase this to help alleviate that. It's not ironclad but it is one more way to help harden SQL Server or at least provide more of a forensics ability.

DBCC CHECKDB

I don't like database corruption. It doesn't happen much and I take frequent backups to help alleviate that. Database corruption is rare nowadays. With each new release of SQL it seems to become rarer and rarer.

Even still, I like the comfort of knowing I check my databases for corruption as frequently as I can. I will typically setup a job to perform a "DBCC CHECKDB with no_infomsgs" on all of my databases as frequently as I can. While DBCC CHECKDB can be run concurrently with users in the system, I like to pick either low use or no use times to run it, especially for larger databases. I try to run it at least weekly and the more frequently, the better.

This will help my chances of detecting corruption before a user does.

Wake me up, as you go-go

(Is it wrong to paraphrase a wham song in public? I grew up in part of the 80s, but I digress...)

What kind of monitoring do you use at your company? There are a lot of great tools out there to use. There are even homegrown approaches you can find on SQLServerPedia or in articles at SQL Server Central.

There are pros and cons to many of the tools and some quick web searches will show you a lot of these. The point is - use one.

Don't rely on the users to tell you when you have a problem or when a job fails. Before I hand an environment over to a project team or users, I like to know that I will find out if a job runs too long, a job finishes too quickly, the CPU is pegged for a substantial amount of time, the SQL Services become unavailable, etc.

It bears restating: Discover your problems before your users report them to you. (Or at least have a chance to do so!)

-----

Those are the basics I like to focus on when installing SQL Server. I am sure there are items that others look at as well. Please feel free to share them in the comments below. Thanks for reading the series and happy installations!

If you have any problems, feel free to leave a comment - I check regularly and have no problem helping you out with an issue during installation with some advice, clarification or a point in the right direction.

While Playing around with the UCP inside of SQL 2008 R2 I found some videos to help you get started. The November CTP has six videos listed but only five of the links are currently working.

Here is the splash screen you will see to help you get started:

The link to “Enroll instances…” does not seem to be working. The other five are working just fine. Since I am not able to play the videos directly from my server, so I had to save the files to a network share and then play them from my desktop.

Here are the actual links:

Create a Utility Control Point (UCP)

Connect to an existing UCP

Create a data-tier application

Set Resource Health Policies

Modify UCP Users

Now, maybe it is just me, but I think there is some inconsistencies with the use of capitalization. But this is only a CTP, so I will refrain from filing a Connect item for now. But I may file one for the broken link.

The videos are hosted by Buck Woody (blog | @buckwoody), who is all business. I kept hoping to start making a list of people or things but he was professional throughout. In other words, the videos have little entertainment value, but are a wonderful resource to help you get started using UCP.

While Playing around with the UCP inside of SQL 2008 R2 I found some videos to help you get started. The November CTP has six videos listed but only five of the links are currently working.

Here is the splash screen you will see to help you get started:

The link to “Enroll instances…” does not seem to be working. The other five are working just fine. Since I am not able to play the videos directly from my server, so I had to save the files to a network share and then play them from my desktop.

Here are the actual links:

Create a Utility Control Point (UCP)

Connect to an existing UCP

Create a data-tier application

Set Resource Health Policies

Modify UCP Users

Now, maybe it is just me, but I think there is some inconsistencies with the use of capitalization. But this is only a CTP, so I will refrain from filing a Connect item for now. But I may file one for the broken link.

The videos are hosted by Buck Woody (blog | @buckwoody), who is all business. I kept hoping to start making a list of people or things but he was professional throughout. In other words, the videos have little entertainment value, but are a wonderful resource to help you get started using UCP.

Next Monday, I’m presenting a TechNet WebCast for Microsoft Thrive Live! The subject: SQL Server Locking & Blocking Made Simple. Here are the details:

Event Overview

A good working knowledge of how Microsoft SQL Server makes use of locking and transaction isolation levels can help you greatly improve an application’s performance. In this webcast, we explore the SQL Server locking methodology and share techniques for enhancing query response times.

Language(s): English
Product(s): Microsoft SQL Server
Audience(s): IT Generalist
Duration: 60 Minutes
Start Date: Monday, December 07, 2009 9:00 AM Pacific Time (US & Canada)
Event ID: 1032432983

I’d love to have you join me for this free session. Click here to register.

Our application includes some search functionality which is pretty complex to deal with.  In a nutshell the user can select multiple values from a couple of lists on the website and use those listed to filter down the rows which are being searched.  These lists are passed into the SQL Server as a couple of XML documents.  We recently had a larger customer call and complain that the search was slow.  I fired up profiler and grabbed the query.  They were right, 6 minutes is a long time for a query to take.

There's some full text searching going on so it's never going to really scream as there are 70 million records in one table, 57 million records in another table (with a one to many between them), and another 90 million records in the third table (this is a one to many to the table with 70 million records, and this table has the full text index on it).

Needless to say there was some tuning that I had to do.  The basic jist of the query was...

After getting no where working on indexes and tweaking things here and there (and actually making the query take 16 minutes to run for this customer's data) I put a couple of table variables in the procedure and loaded those table variables up with the values from the XML Documents.

And I changed the WHERE clause to use the table variables instead.

This got my query run time down to about 1 minute with the execution plan showing that ~90% of the time spent is being spent on the full text search. So while I wouldn't normally consider a query run time on 1 minute to be good, in this case it is. (This particular part of the application also goes out to the file server and uses Microsoft Search service to search millions of files for text string matches so this is now the fastest part of the search process.

Now don't take this post the wrong way. I love OPENXML, it's a great tool and I use it all over the place so that we can pass in multiple values in a single variable (all our code has to be able to run on SQL 2005 so table input parameters aren't an option for me). OPENXML just wasn't the write tool here, sort of.

I wish we could have found this performance problem in QA, but we just have no way to generate enough data to find these kinds of performance problems. But the problem is fixed and the customer is hopefully happy (for now).

Denny

EDMPASS WANTS BUCK WOODY!!!

When talking to Summit attendees the one name that comes up constantly or the one question that comes up is…

“Did you get a chance to see Buck Woody present?”

I did… and it was my first time.  It was the end of the day I believe on the Thursday… I would tell you EXACTLY when but….

Anyways… back to the story… so I go into Buck Woody’s session on Policy Based Management, Data Collector, and Central management Servers and I was pretty tired as it was and the room was cooking but as soon as I walked in the door it was entertainment from start to finish…. and not just entertainment but VERY informative.

I remember back in the days of junior high and high school the teachers that stand out in my mind are the ones that were able to educate the class while adding a bit of fun and their own flavor to the material.  This is Buck Woody. 

We’re all at the Summit, we’re all there to learn, and we’re all excited about SQL Server BUT if you had the pleasure of seeing a Buck Woody session you should know exactly what I mean when I say this guy takes presentations to the next level. 

I heard a rumor on Twitter that if the Summit was NOT in Seattle that there is a chance that Buck Woody might not be able to attend and speak due to the travel costs:

… and this is exactly why I for one DO NOT want to move the Summit out of Seattle.  PASS has come right out and said that if we move the Summit from Seattle that we will NOT be able to take advantage of the all the local Microsoft folks (like Buck Woody).   Now some might say I’m a little biased in my opinion because Seattle is only an hour and 45 minute flight for me… but I would feel the same way if it was in Chicago, New York, or Boston. 

Anyways the goal of this post was 2 things:

1) To spread the good word about a great speaker – Buck Woody

2) To get Buck Woody to speak at EDMPASS

I’ve accomplished #1…. let’s see how I do with #2

Enjoy!!

The nightly process in our production system collects data from our real time systems (JD Edwards) and then processes that data.  Many of these process manipulate the data and supply a modified data set for use by the dozens of client processes and applications.  All of the client applications have their own databases (tables, views, procedures, etc), but due to the nature of the 'raw' data we collect (we have over 350 million rows of historical and current data) sometimes the client apps need to directly access this.  It would be too costly to replicate this data for each individual application that would need it as well as maintaining dozens of copies would be impossible.  This posed a unique problem:How do you grant access to the raw data in limited format?

The reason for the limited format is some of the data is for the ETL process only and if used by an application would provide false or erroneous data (one of our tables has over 250 columns).  After a bit of research we came up with the following mechanism to allow specific access to specific tables in specific databases, with the added benefit of not having to add every new id to the permission roster.

First thing that needs to be done is to make sure that the same owner owns the files of the databases you are looking to chain.  To simplify our servers, all files are owned by SA.  This prevents the situation where a user id was used to create or restore a database, but they are no longer with us.  Here is where you set that up:

Second thing we need to do is turn on 'database cross-chaining'.  This enables permissions to be transferred when you have statements that cross databases, such as being in database A and doing the following: select * from B.dbo.sometable.  This causes sql server to validate the user who is in database A to have permission to select from the table 'sometable' in database B.  You turn on database cross chaining as follows: sp_dboption <database_name>,'db chain','ON'.  This should be done in both databases, that is the database you are calling from and the database you are looking to access.  In the above scenario, you would turn it on in A and B.

Third thing to be done is: grant connect to guest in both databases.  This is the mechanism that SQL Server uses to pass credentials.

The next step is we create views in the 'source' database, which in the above example would be in the B database.  Then you create a corresponding view in the application database, A.  This does two things.  First as you add users to the application databases you need not add them to any of the source databases, since they will be calling local views.  Second, it provides a way to seal off the applications and as we add new sources of information, we simply create local views and hence isolate all applications from the Common data, keeping it pristine and from prying eyes.

I do realize the applications have to go through two views, the local view and then the view in the source database.  Some have asked if this has a noticeable performance impact and I cannot say it does.  The security flexibility we get from the above design far out ways any performance detriment as the apps run well within the time allotted (many sub-second).