Category: SQL Server 2008/20008R2

SQL Server System Databases

SQL Server System Databases are very simple and pretty small (msdb might be huge in some cases if we are not properly maintaining it) databases at a very high level, but the most(i mean it) important databases within SQL Server.  Why am i stressing the word “the most Important”??? Because SQL Server Stores it majority of the configuration information in these Databases besides storing some in Registry  and few configuration(ini/xml) files and SQL needs them in a healthy shape to be in running state. So what are the System Databases in SQL Server?

Master, MODEL, MSDB, TEMPDB, RESOURCE Database  and Distribution(I would call it as a pseudo System Database 😉 You will see this DB only if you’ve configured replication). Let me explain what each database does in brief…Let’s start with Master Database.

Master:  The Very first Database loaded by SQL Server on Service Start. If if finds any issue bringing up this database, entire SQL Server Instance will be down. Master Database stores very critical information regarding Instance configurations such as CPU info, Memory configurations, other sp_configure settings etc. It stores the Information(Meta Data) of all other databases on the respective instance. Stores Logins information and all other Instance scoped objects(Server Objects) such as Linked Servers, ENDPOINTS etc…

Model: This little guy acts as a TEMPLATE Database. whatever settings you configure on this database, they will be reflected on any other new database you create on this Instance(Classic Example – Setting Model Database Recovery Model to SIMPLE on your DEV Server, making sure that all the databases which you are going to create from now on will be in SIMPLE Rec mode on this Intance). If you create any object(let’s say a user table) inside Model database, all the new databases which you create from now on contains the same object! So…..Basically you can use this guy for your standardization!

MSDB: Well, this guy stores all the critical information any thing related to Automation. Soo….anything which you do with SQL Agent, it gets stored or it updates MSDB Database in one or the other form. All your Agent Jobs, Maintenance Plans information, Backup/Restore Events/Alerts/DB Mail information, even SSIS packages(if you are doing MSDB Deployments) and So on…..! Very Very Important and heavily used by Agent Service.

TEMPDB: The busiestttttttttttttt System Database out of all!  Ignoring TempDB configuration will heavily impact your SQL Server performance.  As the name implies…it’s a temporary Database where all the temporary objects will be created, Cursors use TempDB, Row Versioning is done in TEMPDB(SNAPSHOT ISOLATION), ONLINE INDEX REBUILDS(choosing Store/Sort Results in TEMPDB), Memory Spills occur a lot to TEMPDB with Hash Joins and Hash Recursions and even SORTS and many more things heavily uses TEMPDB.  Interesting thing to note regarding this Database is, It’ll be “Wiped Out” entirely when we shutdown SQL Server and Brand new TEMPDB will be created at Start up of SQL Server.  So….there’s no concept of  “CRASH RECOVERY ON TEMPDB” in SQL Server/You can’t take a Backup of TEMPDB.

RESOURCE DATABASE: This guy will always be in “STEALTH” Mode hiding from us. We can not see this database from our SSMS. We’ve to actually go to our File System to look at the Data and log files of this Database. It is a Read-Only Database containing “System Objects” of SQL Server. This guy is very very important while we are upgrading our SQL(applying Hotfixes/Service Packs/ CU’s etc)which many of the DBA’s ignore to take backup before applying Patches!!!

I believe that’s enough information to understand how critical these Databases are! For more information on these Databases check BOL….MSFT has tons and tons of very useful information on these Databases.

Backups on System Databases:

Well, now let’s see how can we secure these Databases. In other Words how to design a Disaster Recovery strategy on these System Databases. I’ve seen few(TBH Many) cases where a DBA ignores Backing up the System Databases….Very Very Bad!! Trust me guys…you’ll pay the penalty someday or the other if you ignore backing up these databases!

Before proceeding any further, let me tell you something…You Should never Ignore backing up and performing frequent Consistency Checks on your System Databases. In a worst case scenario where you have to rebuild your entire server, you would need them to be restored without any issues. ( Imagine a case where you have 100+ Jobs and 300+ Logins and 20-30 Linked Servers and you don’t have your System Database Backups….It’s scary enough just to imagine right?? ) FYI I’ve shown how to Restore Master and MSDB Database here.

Note: You’ve to know your SQL Server Instance Collation Information to rebuild your SQL Server(Rebuilding Master DB). I’m saying…rebuilding master, not Restoring your master database in this collation context. I’ll show you how to rebuild entire SQL Server in later posts.

Master Database  – Take Full backup as often as you do for your User Databases. You can’t take Log backup for Master Database. Only Full backups are allowed on Master Database.

Model Database – Well, it’s your call! if you are using it, you can take backups..if not you can ignore. I personally feel taking Model backups is just a safe bet and the size of the Model Database backup will be usually in few Kilo Bytes, which is almost negligible!

MSDB Database – You’ve to take regular Backups. You can perform all types of Backups on MSDB(Full/Diff/Log). Plan accordingly!

TEMPDB – You can’t Backup TEMPDB Database. As i mentioned above, SQL will recreate tempdb on every service restart.

Resource Database –  This should be our part of Backup Strategy(whenever possible). At least once a week or bi-weekly! You should not ignore backing up this especially when you are patching/upgrading your SQL Server. So..where should we locate this hidden Database?? The Answer is “in your SQL Server Program Files(location where you installed your SQL Server Binaries) starting SQL Server 2008, not in the Data file(s) Directory anymore”.  You can see in the Below Screenshot for the default location of this Database files on one of my Instance

So, in my case the path is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Binn”.
For backing up this Database, all we need to do is, Just copy these two files and place them in your secure backups location. So…We can’t do a SQL Backup on this Database, the trick is to copy the files at File System Level.

That’s pretty much it Guys!! Hope this is useful and informational…

Setting up Email Alerts for PBM(Policy-Based Management) Policies

PBM(Policy-Based Management) – One of the most fancy terms which we(the fantastic SQL Server DBA’s) are listening starting SQL Server 2008. In this Blog post I’m not going to explain in detail what PBM is how exactly it works, instead I’m going to show how to create a Policy for checking Auto-Close, Auto-Shrink,Compatibility Level and Recovery Model of all the databases on a regular basis and how to setup email alerts if any of our databases are violating our policy.

For detailed explanation on PBM PS http://msdn.microsoft.com/en-us/library/bb510667.aspx

As i mentioned in my previous post, setting up DB Mail profile is mandatory for sending email alerts from SQL Server. Okay, enough blabbering..let’s get started!

As the first step, under “Management” Node in your OE(Obj Explorer) expand “Policy Management” and RC on “Policies” and choose create “new policy” as shown below.

Note: You can create conditions first and choose them accordingly for any future policies as well. In this post, I’m going to create everything from Create new policy Option it self, which avoids confusion for people who are new to PBM.

Name your Policy and select “create new condition” to create one as shown below.(you can pick from here if you’ve already created one in the past as mentioned in the above note)

Once, you click on “create new condition”, you’ll be welcomed with a new screen as shown below. Give any appropriate name for your condition and choose one facet from the drop down as shown below. I this case I’ve to choose Database Options Facet, because i’m creating a policy to evaluate DB Options.

select all your required options and create an expression using And/OR, Drop down Menus as shown below and Click OK. You can add your own description if you need any under “Description” tab.

Once done, now you have choose your targets(I selected against every Database) and evaluation mode(I’ve selected on schedule and created one to run/evaluate on daily basis) as shown below.

Once done..you can add your own description(optional) and click Ok. At this point we are all set with setting up a policy which runs on a daily basis to evaluate all our conditions.  Let’s try to evaluate manually and see how it looks.

Go to your policy node and RC and select Evaluate(Enable the policy if it’s disabled). You’ll be welcomed with the below screen which is very much self explanatory…

You can click on View to see more details on the violation and also you can click on Apply to fix them all from right here with a single button click.

Configuring Email Alert:

Soo…we haven’t configured email alerts yet upto this point. To achieve this , the simple trick is to know the exact Error Number(s) which PBM generates in our Error logs and to setup alerts/actions for those Error Numbers in our SQL Server Agent. As per BOL the below are the error numbers which we’ve to look for when setting up alerts for PBM

Execution mode Message number
On change: prevent

(if automatic)

 34050
On change: prevent

(if On demand)

 34051
On schedule 34052
On change 34053

To double check, Let’s see our SQL Error Log and Event Viewer what has been logged…

Okay…..a message has been logged with eventID 34052. So…now let’s configure Alert to notify operator from SQL Agent for this Error Number.

FYI..I already created an Operator as you can see below

Okayyy…it’s time to create Alert. Follow the below Screen shots in the same order

Choose Error Number as shown below

DO NOT JUST CLICK OK now….Instead go to RESPONSE tab and choose an operator whom you want to notify for this Alert based on this Error Number as shown below.

 

Again, don’t just hit OK, instead go to Options and enter a customized message which you feel appropriate for you as shown below

Now click OK:) and let’s try to evaluate our policy manually as we did earlier(Go to your policy node under Management in your Object Explorer and RC and select Evaluate) for testing our policy initially and let us see what we’ve in our Inbox.

Perfectttttttttttt………!!! See how simple it is…to make yourself a hero with all these cool automation stuff 🙂

Hope this Helps!!