Category: SQL Server 2008/20008R2

When was SQL Server Restarted??

As a DBA seldom we see cases where a User reports “Hey Mr DBA, my application went down for a minute or even few minutes, Now everything appears to be Okay. Can you pls take a look what happened?” Well, our very First place should be SQL Server Error Logs( My Personal favorite to start troubleshooting any issue) and let’s assume in this case that our Instance has been restarted and we see it in our Error Logs. So this is the traditional way to find out regarding SQL Server restarts – The SQL Server Error Logs.

Let’s jump into another scenario where you want to know when was the SQL Server restarted. It would be little annoying to cycle through all the error logs in SSMS if you are recycling your Logs on a daily basis and maintaining multiple Error Logs(PS we can have upto 99 Archives of Error Logs listed in our SSMS). So what would be our easiest bet to figure out when was the Instance restarted??

Method 1:

TempDB Creation date:

Since we all know Tempdb will be created each time you restart SQL Server, Tempdb creation date will give us the answer.

Alternate Methods:

1st Session(Session_id = 1) creation time and by starting SQL Server 2008, we can query DMV sys.dm_os_sys_info.

FYI Please see the below Screenshot

Note: Notice a slight difference in Milli seconds( could be seconds) in tempdb creation time and the time when SQL created it’s first session. But that’s Okay! Choose your Best method. I use tempdb creation date(since I’m used to it since SQL 2005). May be I’ve to start using DMV on new boxes!!

Hope this helps!

SQL Server Encryption – Part5 Encrypting Data(TDE) vs Encrypting Connections

This is the final part of Encryption series where i’m going to show how to encrypt connections in SQL Server. In the previous 4 parts of this series we’ve seen how to enable TDE for your Database, what EFS is and what Bitlocker is. If you recall correctly, all we discussed was how to encrypt our Database(Physical Files on the Disk), not securing Connections. In other words the data will be encrypted as long as it stays on the disk but not encrypted while SQL is sending data over to clients as you can see below!!

Note: TDE encrypts Data on Disk, Connections are not Encrypted using TDE.

So…..how to encrypt data across Wires(in other words how to Encrypt Connections)?

We have to use 128 Bit SSL(Secure Socket Layer) within SQL Server to encrypt Connections btw SQL Server and Client(s), which leads to slower performance but your SQL Server is Very Very Secured! Basically we’ve to Install a trusted certificate in our windows Certificate Store on our Physical Server where our SQL Server Instance is living on and we’ve to let SQL know that “Hey Mr.SQL Server! we’ve a certificate for you..make use of it for every connection”.

Step 1: Install Certificate(We can work with our Server/Network Team on this)

Step2: Go to your SQL Server Config Manager and enable Encryption as shown below.(I’ve 3 Instances on this Server, I’ll show you on PROD Instance)

Navigate to network Configuration, Right click on Network Protocols and select Properties as shown below.

Now, all your Certificates will be shown in the drop down menu under “Certificates” tab as shown below(I don’t have any on my Server)

Now, on Flags tab you can choose either Force Encryption to Yes or leave to default No as shown below.

If you Choose YES – Clients who are not encrypted are not allowed to connect.( In other words, any client which doesn’t allow encryption is declined to connect)

If you choose NO – Clients will be seeing encryption as optional.

Note: SSMS(Let’s assume it as a client application now for time being) knows how to encrypt connections. We can choose an Option to Encrypt my connection while connecting to the Database Engine as shown below.

As you can see, I’m checking the Box to Encrypt my connection from SSMS client while connecting to SQL Server DB Engine.

Hope this helps for a Kick Start on Encryption!!

SSMS(SQL Server Management Studio) Standard Reports and my Personal Favs!!

If you are already working on SQL Server 2008 since decent amount of time, you might have already known the inbuilt standard reports which we can popup Just by right clicking on an object in our Object Explorer.

In this Blog post, i would like to show some interesting In Built Reports which you(we) as a DBA might(Should) be interested! Before going any further, please see the below Screenshot for what i’m actually referring to if you are new to SQL Server.

Server Dashboard: This is a very useful report especially for a DBA to understand his Instance Configurations, Settings at a very high level in a single glance. I really like this guy and would recommend you to open this fab report as frequent as you can on your Server.

As you can see, at a single glance it is giving my Server(Well, It’s actually SQL Instance) startup time, Instance name, Version, Stand alone or clustered, AWE Settings, memory settings, CPU’s visible to SQL and many more….My personal favorite in this report is “Non Default Configuration Options“. Basically this tiny section is giving me a clear picture of settings which are deviating from default SQL Server settings, which is really reallyyyyyyyyyyy useful for a DBA. Let’s assume a scenario where a DBA from your team is leaving and he/she is doing a Knowledge Transfer to you. Just by pulling this info, you can ask him/her “Hey i’m seeing the following Non Default Configuration Options on this Instance, Can you pls. explain why??…”

Transaction Log shipping Status: This is a single place where you can get all the cool information regarding all the thresholds, last backed Up t-log, copied t-log backup, restored t-log backup(with File names and LSN details as well…Huhuuuuuuuuu That’s awesome!!) Note: For accurate and more meaningful info, you’ve to pull this report from both your Prod and DR Servers, to see if your DB is out of sync.

Schema Changes History: You can see the DDL Changes being made on your Databases. For example If some one dropped a Table and if you as a DBA are looking for an option to track him/her down….Well, this is one of the places 🙂

Note: The above information will be pulled from SQL Server Default Trace. So the Data will be not stay for ever! You can see only the info what has been captured by your DEF trace. very Imp….!

Sooo…are these the only Inbuilt Reports(which you are seeing in the first Screenshot) we have in SSMS?? Nope!!  If we Right click on the Database in Object Explorer, you’ll get completely different set of reports! See the below Screenshot.

My personal favorites are

Backup and Restore Events: Please see the below Screenshot!

It gives me the Avg time it is taking for backup Operations! You can use this as a Benchmark for your backup duration (well, there is much better way to pull that info from MSDB, I’m Just talking about basic Benchmarking here ;))  If you expand Successful Backup/Restore  Operations, it tells you what was backed up/restored, the location of files, when it was performed and who(the Login) performed the operation…Well one more way to track these operations!!

Disk Usage:

You might think that it only Shows, the sizes of your Data and log files on your Disk. Well, there is one of the Most important hidden gem in this report. Auto Shrink/Auto Grow Events as you can see below. Tadaaaaaaaaaaaa!

This gives me a clear picture that my log file has auto grown several times on 9/7/2011, Which means as a DBA I’ve to consider changing my File Growth settings on my database. This is the Most important factor which might be hindering performance of your Database( For the sake of  this example I’ve changed the autogrowth options to 1 MB on data and 10% on my log file and i inserted 10000 rows. Well this is just my test lab, but imagine the same happening on your Live Production database!!!! Really Really bad).

Note: If you see any Auto Shrink Operations in the above report, ha ha you are gone. It’s time to take action immediately!! I’m not going to deal with what Shrinking does to a Database in this post, for now…Just remember, Setting Auto Shrink option turned ON on any database is the worst possible thing you could potentially do to your Database!!

I recommend to explore all these Inbuilt, completely free and most fabulous reports and get familiarize with what each report can do for you as a DBA. Hope this Post helps!  

 

The file ‘microsoft.reportviewer.winforms version 9.0.0.0’ cannot be opened Error!!

In this Blogpost, Actually I thought of showing few hidden gems in our our Fabulous SSMS standard Reports, but ran into one of the interesting issues when i was trying to open REPORTS from SSMS. You can see the error Message below in the window.

Please note, I’m running on SQL Server 2008 SP2! So it has nothing to do with the Bugs or Service Packs or even any Cumulative updates installed!

How to Fix this Issue in my case?

I remember not Installing complete Client tools on this Server while I was installing SQL Server. You can see the below Screenshot where BIDS and other Client Components(I Installed only SSMS initially) being missing on this Server.

Let’s Install BIDS(Business Intelligence Development Studio) and see what SSMS say when we try to bring up Reports!

As you can see once I Installed BIDS, I was able to open Reports Successfully 🙂 Also, you can verify a folder called “ReportViewer” Being created in Microsoft Visual Studio Folder in “program files”  Directory as seen below.

Hope this helps when you get into a similar Issue!

SQL Server Encryption – Part4(How to Restore an (TDE enabled Database) encrypted Backup?)

Until now we’ve seen what TDE is, how to enable it on a Database, what are the key considerations which we should never forget as a DBA while dealing with Encryption stuff..bla bla…!! In this post of the Encryption Series, let’s actually see how to Restore a Backup of a database which has TDE enabled on it.

As i mentioned earlier, we can’t restore a backup on a different instance without Certificates. We’ve to restore the Cert from Source Server , then we should be able to restore the Database from the Backup.

Note: Enabling TDE on a Database encrypts every single portion of the database. It includes mdf, ldf and any ndf’s as well. All your Database Backups are encrypted as well once you turn ON TDE!! Heyyy…that’s awesome!! One thing you should be aware of is, If you are using FileStreams, they are not going to be encrypted with this TDE.

My Scenario: I’ve a Encrypted Database “Adventureworks” in my PROD Instance and i took a Full backup of it(Adv_TDE.bak). Let’s try to restore on my DR Instance as a brand new Database. As you can see below it is throwing “certificate related error Messages” when i am trying to restore the database.(You’ll get the Same error message if you try to use detach/attach method or Copy DB Wizard)

To keep it real simple I’m taking all the defaults(which will fail in real case without MOVE) while restoring Database in my T-SQL Restore Syntax. Remember this blog post is not about normal DB Restores Syntax(es) 😀

Soo…..what should we do???

All We’ve to do is restore our Certificate from source(And you have to create a DMK on destination if you don’t have one), Once you are done with that piece of puzzle, the rest is just performing your normal DB restore process what you do on any other non encrypted user database(s)! So..Basically all we need to do is creating the certificates on the destination Instance from the backup of your certificates which we created in earlier post.

On DR Instance: 

Create a Master Key(You don’t have to do this if you already have DMK) and Create a certificate from the Cert Backup and providing Private Key file and Password to decrypt it as shown below

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DIFFERENTSTRONGPASSWORD'
GO
CREATE CERTIFICATE TDEDEMOCERT 
    FROM FILE = 'D:\Backs\TDEDEMOCERT.BAK' 
    WITH PRIVATE KEY (FILE = 'D:\Backs\TDEkey.bkey',
    DECRYPTION BY PASSWORD = 'VeryStrongPassword');
GO 

/*the Certificate encryption piece on PROD was
BACKUP CERTIFICATE TDEDEMOCERT /*this was the certificate which we created earlier*/
TO FILE = 'D:\Backs\TDEDEMOCERT.BAK'
WITH PRIVATE KEY ( FILE = 'D:\Backs\TDEkey.bkey',
ENCRYPTION BY PASSWORD = 'VeryStrongPassword' )
GO
*/

Now let’s do the actual restore and see how it goes….

Perfect!!! Once we created certificates based on the Cert backup from PROD Instance, we were able to do Restore!! Soo….Key thing to remember is to Backup your certificate and store it in a safe as soon as you’ve Encrypted your database. I want to stress again – If you loose it, you lost everything related to your Database(You are safe as long as you can access the source database)!!!