Your Database Whereabouts…!

First of all, I would like to wish you all a very Happy and prosperous new year.  This will be my first post of this year 2014 and I would like to show you something interesting- How to get the Hidden facts about your Database. It’s like running a background check on your SQL Server database and accessing it’s PII 😀

Let me ask you this, in general how would you check when your database has been created? What about, where was it actually created? Is it on the same Instance or has this been migrated(or even upgraded from a previous version)? How do you get all these details?

I’ll show you how in this blog post…

I’ve a database called “salesDB” on my SQL Server 2012 Test Instance and I would like to perform a back ground check on the database. Where should I start from? From Properties in SSMS, From Error Log, or by querying sys.databases? Well, unfortunatley all of the above options won’t give me enough information on what am looking for  as you can see below 😦

1

I really can’t trust this info! So…what do we do now?

Well, each and every SQL Server database has something called “Boot Page” where it stores all the metadata related to the respective database and the boot page will be always 9th page of the 1st file in a database. So, reading the Boot Page will expose the info which I’m looking for. But how do we read a Boot page? We’ve two options…

1. DBCC DBINFO()

2. DBCC PAGE(database_name,1,9,3) — File 1, Page 9, dump style 3.

I will use DBCC DBINFO() WITH TABLERESULTS to avoid using traceflag 3604 and 3605. See below Screenshot

2

As you can see

dbi_modDate is “2006-06-04” – This is when it was actually created.

dbi_createVersion is “611” – Which is SQL Server 2005.

So, this has been originally created on SQL server 2005 and has been migrated to SQL Server 2012(dbi_version: 706). Wow…

Also, If you have the original backup file, you can simply do a RESTORE HEADER ONLY for even more info…or you can also query msdb..backupset. See below Screenshot to see what am talking about…

3

Tadaaa…as you can see in the above screenshot, this database has been originally created by Kimberly Tripp on SQLDEV01 Instance. ( Yes, this is a database which I downloaded from sqlskills.com) and I learnt about Boot Page and DBINFO from Paul Randal’s blog posts long ago.

This is how you can perform a “PostMortem” on your SQL Server Database. Hope you learnt something new…

Advertisements

How to Move a Mirrored Database MDF and LDF Files?

Couple of days ago, I had a conversation with one of my colleagues, on how to move database files from one location to another which is has DB Mirroring enabled. In this blog post, let’s see that in action..

Let’s consider the below Scenario:

1. You’ve a Large Database(let’s say 500+ GB DB) which is participating in DB Mirroring as a ‘Principal’ Database.

2. That’s the only database sitting on this Instance, a Dedicated Instance for your Application(In other words, detaching-attaching DB will cause almost the same impact as SQL Services Restart)

3. You want to Move your MDF and LDF files to another LUN (because of space constraints or you are doing SAN Expansion or something else)

4. You do not want to Break Mirroring completely, because you do not want to re-configure everything from scratch…which takes considerable amt. of time and not acceptable to your Business, running without DR for that long period of time.

5. What is the option we have in this case?

Solution: First thing to note, we can’t detach a Database while it is participating in Database Mirroring. You’ll get the below error if you try to do so..

“The operation cannot be performed on database “Mirror_tst” because it is involved in a database mirroring session.
ALTER DATABASE statement failed.”

so what to do now..? Remember, we’ve ALTER DATABASE…..MODIFY FILE….to do this trick.

Demo:

I’ve a database called ‘Mirror_tst’ which is being mirrored from PROD Instance to DR Instance as you can see below. Also, please note the current database file locations in the below screenshot(As of now, they are in default location)

Now, Let’s update the database file(s) location in system catalogs to “C:\Backs\” folder. For this I’ll issue the below commands.

ALTER DATABASE mirror_tst
MODIFY FILE(NAME= mirror_tst,FILENAME=’C:\Backs\mirror_tst.mdf’)
GO
ALTER DATABASE mirror_tst
MODIFY FILE(NAME= mirror_tst_log, FILENAME= ‘C:\Backs\Mirror_tst_log.ldf’)
GO

and i received below messages:

The file “mirror_tst” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “mirror_tst_log” has been modified in the system catalog. The new path will be used the next time the database is started.

So, the database file locations were modified in system catalog, all we have to do now is Stop SQL Service, move the physical files and start SQL Services. Let me do that!

I issued SHUTDOWN WITH NOWAIT on my PRINCIPAL Server as you can see below.( Be very cautious with this statement, understand what exactly you are doing before running this statement on your Prod Instance, believe me guys, Running this single statement unknowingly might cost you, loosing your Job!!..)

 

As you can see SQL is stopped and now, I moved the Physical files to ‘C:\Backs” folder as you can see below.

Now, I’m starting SQL Services and let’s see what happens to our database and more importantly to our Mirroring Status, keeping my fingers crossed 🙂

Once Restarted, the SQL Server was happy to bring up this Mirrored Database and our Mirroring Process is running happily without any issues as you can see below:) 🙂 Notice Physical files new location and the Mirroring Status now. Everything is pretty Normal:)

Hope this post helps,if you get into same scenario….Cheers!

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)!!!

SQL Server Encryption – Part3(GOTCHAS – A TDE Enabled Database)

In the Previous two Posts of this series, we’ve seen how to actually encrypt our Database(s). In this post, let’s focus on things to consider on Databases with TDE enabled. Let me tell you something…there are lot of GOTCHAS as a DBA for Encrypted databases you should be aware of. You should not be in a situation saying “Oops! I missed it, what should i do now?”.  Reminding again….If you loose your Master Key, You’ve lost your Database, No Ifs and buts! If you are creating a DB Encryption key with AES_128/AES_256 Algorithm and if you lost it, i believe it would be a very tough challenge even for an ethical hacker to decrypt it ;)(I might be wrong, but I would expect the same level of encryption what i’m saying!!) Okay!! Enough Scaring you all, let’s get into the actual content!

Very first thing, Encryption adds a little Overhead on your CPU. Thorough testing is mandatory!

Enabling TDE on a Database might require an Application outage, It takes some time depending on your database size(I think it acquires some locks on objects while enabling encryption for the first time, I’m not 100% sure). As a safe bet, do this after Business hours taking on a maintenance window. (Make sure that you’ve tested in your lower environments.)

If you enable TDE on any Database on your Instance, Your TEMPDB is automatically Encrypted as well. Soo…there’s a high possibility impacting other Databases which are actually not using TDE feature while you are in the process of enabling TDE!!!

Instant File Initialization won’t work on TDE Enabled databases and Log truncation won’t happen through the Encryption process(While Background Encryption scanner is in progress). So, keep a close eye on your LDF file when actual encryption process is in progress.

You’ve to backup your Service Master key, Certificates and Master key and secure it in a safest location(Far away from where you store your Database Backups. The idea is not to have access to your Keys for any intruder even though he/she got hold of your DB Backups!

How to Backup your Service Master Key??

BACKUP SERVICE MASTER KEY TO FILE = 'D:\Backs\SMK_08312011.bak'
ENCRYPTION BY PASSWORD = 'VERYVERYSTRONGPASSWORD'

How to Backup your Master Key?

BACKUP MASTER KEY TO FILE = 'D:\Backs\Master_Key.bak'
ENCRYPTION BY PASSWORD = 'DIFFERENTSTRONGPASSWORD'

How to Backup your Database Key?

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

That’s all for the day! Let’s see how to actually restore a Database which is Encrypted on a completely different server in the upcoming post of this encryption series.

SQL Server Encryption – Part2(TDE-Transparent Data Encryption)

This is the second part of the Encryption Series, In this Blog post let’s see what can be achieved within SQL Server in terms of Encrypting the Data.

In Legacy days……..Prior to SQL Server 2008(Enterprise Edition), we only had an option to encrypt at Column level/Cell level not at the Database level. Even in SQL Server 2008 if you are using standard edition of SQL Server, you don’t have an option to encrypt at Database level. So…Basically we’ve to make this possible manually on every column by making use of either of the following inbuilt functions which SQL Server offers us..

EncryptByPassphrase()

EncryptByCert()

EncryptByAsymKey()

EncryptByKey() 

So…basically you’ve to deal with all the annoying Symmetric Keys/ Asymmetric Keys/PassPhrases etc..and you’ve to make sure that the routine is followed each time you add a new column or make any change to your table…and we’ve to make use of Decrypt routine while retrieving the data, which is a huge hugeeeeeeeeee deal especially for developers!! You are dealing with tons and tons of Code changes and huge huge amount of testing is required!(Also a hugeeee…CPU hit depending on how many columns you are encrypting)

If you are on Enterprise Edition of SQL Server 2008, we’ve a brand new feature TDE(Transparent Data Encryption) which is at Database level and completely transparent to developers or anyone who is actually accessing our Database!! Nothing needs to be done from developers standpoint, basic testing is necessary though(Since it could cause 3-8% CPU hit under the hoods as per Microsoft documentation). All the Encryption and Decryption routines are completely transparent:) which is AWESOME!

How to Enable TDE???

1. Very first Step would be creating a Master Key in Master Database.(Immediately Backup your Master Key)

2. Now, create a Certificate(in Master Database) based on this Master key.

3. Now, create a Database Encryption Key(In the Database which we are trying to Encrypt) and the final Step would be Turning on Encryption at Database level.

Note:

If you try directly Enabling TDE on a Database without a DB Encryption Key you’ll be yelled at with the below Error Message

Msg 33106, Level 16, State 1, Line 1

Cannot change database encryption state because no database encryption key is set.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Demo:

Please see the below Screenshot which covers all the Required Steps to Enable TDE on a given Database.

Technically, That’s all we need to do for encrypting a Database! Pretty Simple huh?

Note: You’ve to provide a very very Secure Password(I would say 20-30 Characters including all kinds of Alpha Numeric Combinations and store it in a safe) for your Master Key.

How to Verify whether your database has been enabled with Encryption?

You can Go to Database Options from SSMS as shown below or Just Querying the Sys.Databases view!

SSMS :

T-SQL:

Int the next post of this Encryption Series, let’s see what are the challenges we’ve while dealing with Encrypted Databases! There are quite a few things to keep in mind as a DBA in order to Support TDE enabled Databases.

Property Owner is Not Available for Database…

Property Owner is Not Available for Database

One of the Most annoying error Messages(which really irritates a novice DBA) when you try to look at your Database Properties/Options from SQL Server Management Studio(SSMS). You’ll get something similar to the below screenshot when you right click on the database and choose properties.

Okay…So Let’s run our legacy sp_helpdb and see the result set.

Well, it says the owner of ‘Adventureworks’ database is “UNKNOWN”…Umm Interesting!! So what can we do to fix this issue?

Resolution:  Assigning an owner for this database from T-SQL. Typically i prefer to go with ‘sa’ as Database Owner for all the databases. We can do this in either way mentioned below

Legacy(SQL 2000) way:  

Use Adventureworks
GO
sp_changedbowner ‘sa’

New way starting SQL Server 2005:

ALTER AUTHORIZATION ON DATABASE::Adventureworks TO sa.

So what is the root cause for this strange behavior of  database from SSMS? What happened exactly?? Well, consider a scenario where your coworker has created a database and he/she(Windows Account) is the current owner of the database(Just let’s assume). For some reason he/she left the organization and his/her Windows Account will be removed from the Active Directory by AD Administrator(and as a DBA you’ll drop his/her login from SQL Server). In other words his/her windows login is no longer a valid account.

Illustration:

I’ve created a windows user account called “baddba” and I’ve added him as a login in my SQL Server and made him the Database Owner for Adventureworks database as shown below.

Let’s me simulate a scenario where he/she leaves our company and our AD Admin removes his/her account and another DBA drops the login from SQL Server.(Actually i did both the steps behind the scenes).

Now let’s try to select database properties from SSMS….

Grrrrrrrrr…..same Error!!!

Conclusion: Try to avoid having individual Login as a database Owner. Definitely a very bad practice as justified in this post! Instead, make it ‘sa’ on all the databases. You can make use of Powershell to achieve this if you are really supporting tons and tons of databases and have no patience to deal with individual database(s) as i do:)

 

DMVs with Database Compatibility Level 80??…

Let me give a brief background on why i would like to discuss regarding this interesting and most often ignored topic of Dynamic Management Views(DMVs) and their relation with Database Compatibility Mode.

Note: When i say a DMV, i actually mean DMO(Dynamic Management Object) which Includes both DMV’s and DMF’s(Dynamic Management Functions). To avoid the confusion, people generalized and started calling both DMV’s and DMF’s with a single term called DMV and i love it:)

Why am i sharing this today?? Well, we(our Team) recently started a Small Internal Project something called as “Performance Tuning Initiative”. As you all know, a DMV can help a DBA than any other free tool(Sometimes a paid tool too) out there in market!! So…we started to Pull all the Cool information with proper benchmarks and started doing trend analysis and monitoring using few fabulous DMVs which are available to all of us.
Note: We’ve few Databases with Compatibility Mode 80 because of Vendor Restrictions even running on SQL Server 2005 SP4. So…as you imagined by this time, today in the morning myself and one of the DBA’s in my team had a discussion regarding how DMV’s behave if run against a database with Compatibility Level 80. Interestingly, he was in a misconception – “Most of the DMV’s are of no use if the DB Compatibility Level is 80” and i was arguing exactly the other way.- “Most(Almost every) of the DMV’s will have no issues even the Compatibility level is 80”

Quick Demo:
I’ve a Database “AdventureWorksDW” set to Compatibility Level 80 as you can see below.


Let’s run few typical DMV’s which we’ll be using on a regular basis against this database.

In the above Screenshot you can see it had no issues with the “AdventureWorksDW” Database.

Let’s see what happens if we run our sys.dm_db_index_physical_stats against this Database.

Ummm…it is screaming that there’s a syntax error. Actually it’s not. There’s a Simple trick to crack this thing.

Crack – Execute the DMV in context of any other database which has 90/100 compatibility mode. In my case I’m running on SQL Server 2008R2 and my master database has 100 Compatibility level.
Syntax 1 where we had issues:

USE AdventureWorksDW
GO
SELECT *
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
GO

Syntax 2 where we are having no issues:

use master
GO
SELECT * 
FROM sys.dm_db_index_physical_stats(
DB_ID(‘AdventureWorksDW’)
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
GO
The results are as shown below in the screenshot.





















If you observe the syntax carefully, I’ve just changed the database context of execution and I’m passing my Database which is in Compatibility level 80 to the DB_ID() Function.
What’s the Reason behind this??
Very Very Important……
Because the physical structure(Version) of the database is not going to change when you change compatibility level.In other words you are not downgrading a Database at it’s underlying physical architectural level by just reducing the compatibility level to an older version. You are changing only few Queries execution behaviors! That’s all basically what you are achieving here by reducing compatibility level to 80 in this case. Once the Database is on SQL Server 2005 by any means(Create from Scratch/Restored/Detached-attached), that’s it…the Database Version is set to 611/612.
Note: 
SQL Server Denali Database Version – 700
SQL Server 2008R2 Database Version – 661
SQL Server 2008 Database Version – 655
SQL Server 2005 Database Version – 611/612
SQL Server 2000 Database Version – 539.

That’s the reason why you can’t restore a Database from SQL Server 2005(2008) onto SQL Server 2000(2005) even though you changed the compatibility level to 80(90). SQL Server won’t allow to downgrade any database version. No exceptions here!! You’ve to choose the difficult path of moving object by object and data using SSIS!!

Let’s do a Quick Demo what I’m talking about:
SQL Server Database stores the Database version information in it’s Boot Page( Page 9 of 1st File in the database). I’ve to turn on traceflag 3604 to dump the boot page to our results pane. For More Information on this please see my previous blog post http://sql-buzz.blogspot.com/2011/06/checkdb-in-errorlogs-on-startup.html




In the above screenshot, you can see the database has 80 Compatibility level but the DB version is 661(SQL Server 2008R2).

Hope this information is useful….!