SQL Server 2008/20008R2 – Page 5

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 – Part1

This is the First part of the Series Which Covers how to handle the Encryption piece in SQL Server. The Term “Encryption” is one of the Most Interesting and annoying things which we as DBA’s see, especially if we never dealt with Database Encryption,Certificates, Keys in the Past and all of a sudden we got a request from the Application team saying “Hey My Dear DBA! Our Database for this Application is going to contain PII(Personally Identifiable Information) Such as SSN, Address etc and should be SOX (Sarbanes-Oxley Act) Complaint, and we need the Data to be Encrypted. What are the Options we’ve in SQL Server?…” So what are the Options we really have in SQL Server?

Well, In this part of the Encryption Series let’s try to explore what are the Options we’ve to Encrypt Data from Windows Server Perspective. In the future Posts I’ll be showing you what are the options from SQL Server stand point. For now..Let’s look at basic Windows Encryption techniques.

Encrypting File System(EFS):

EFS basically is at File(s)/Folder(s) Level. It Encrypts the Files or Folder(s) based on the User accounts. It doesn’t Encrypt all the contents of the Drive independent of any user. let’s say…you’ve multiple User accounts or groups, they can individually encrypt their own Files and Folders Independently. You need not be even an Administrator on the Server to implement EFS and encrypt your Files!! No Special Softwares, no special Hardware pieces required to be attached to your motherboard! It’s pretty Basic level of encryption, in which all your encryption keys are stored in your OS Drive(typically C$). So, what if hacker is too smart and got into your C$?? yes, He/she can decrypt all your encrypted stuff in no time!!

Bitlocker Encryption:

Bitlocker is at Drive/Volume level. It encrypts all the contents(files/folders) on all the Drives Including System Files in Operating System Drive and Removable Drive(s) as well! It’s independent on User accounts. The Contents will be encrypted irrespective of the User account(s). i.e, Once Bitlocker Encryption is turned ON, all the Files/Folders will be Encrypted for all the Users. You have to be a a member of Local Admins Group at minimum to turn on Bitlocker Encryption and it requires a special hardware piece called Trusted Platform Module(TPM) to encrypt the contents at Drive level. Since the OS Drive itself is encrypted, for a hacker even if he/she got hold of C$, they really can’t get into Encryption Keys.(So..It’s quite possible, we can use EFS to let Users to encrypt individual Files and Windows Server Admin Encrypts the OS Drive using Bitlocker. Few Companies use this Technique as their Security practice)

As per the Microsoft Documentation – Enabling EFS for SQL Server Database Files is not a good idea and they both together doesn’t work well in terms of performance. They’ve(EFS and TDE – Transparent Data Encryption, this is something which we enable at SQL Database level which I’ll be exploring in future posts) concurrency Issues working together! Bitlocker for SQL Server(Bitlocker and TDE) has no noticeable concurrency issues working together. If you reallyyyy…need that level of Encryption, Yes you can definitely think Bitlocker/TDE combination as an efficient solution.

Uninstalling SQL Server 2008/2008R2 Service Pack(s)

Uninstalling Just the Service Pack/CU without uninstalling the entire SQL Server Instance….!! Yes, A definite improvement, Starting SQL Server 2008. In SQL Server 2005 days if you want to Uninstall a Service Pack, basically you’ve to uninstall the entire SQL Server Instance. In other words…we didn’t had Roll Back Capability on any Updates we install over our SQL Instance. Let me explain with a simple Scenario…

You are running on SQL Server 2005 SP3 and because of “End of Support” announced by Microsoft, you decided to apply SP4 over it. You were able to successfully patch the prod Instance with SP4, but all of a sudden your application breaks and users are screaming and you identified the reason – “patching”. Well, you decided to fail over to your DR(BCP) site and meanwhile your plan is “rolling back your production Instance to SP3” so that users and you as a DBA can take a peaceful nap! How do you achieve that??? Unfortunately you’ve to Uninstall your SQL Server and Install your SQL Server 2005 RTM and apply SP3 and restore all of your Databases…and all that hectic work, which really is a very painful process.

Fortunately, starting SQL Server 2008 MSFT offered us a great enhancement – we can Just Uninstall whatever Update we’ve Installed:) Yeyyyyy. So let’s see how to UnInstall Just your Service Pack without loosing your SQL Server Instance!

In this Post, I’m going to show you Uninstalling SQL Server 2008R2 SP1 and basically rolling back my Instance to RTM. As of now I’m running SQL Server 2008R2 SP1 as you can see in the below screenshot.

FYI…I’m on Win7 X64 machine. To Uninstall we’ve to go to “Control Panel” and “Programs and Features” and you Will be seeing SQL Server 2008R2 SP1 as any other typical Program which you can Uninstall from Control Panel.

Note: We’ve to select View Installed Updates in order to see this item under your Installed Programs(You can see that highlighted in the below Screenshot.)

As you can see in the above screenshot, there is no SP1 listed. See the below Screenshot once I clicked on View Installed Updates.

All we need to do is just Select that and Click on Uninstall Button or Just Right Click on it and Choose Uninstall. You’ll be automatically welcomed with SQL Server Installation Center as shown below.

Click Next and As you can see below, I’ve two Instances of SQL server 2008R2 SP1(PROD and DR) on this Machine. I’ll Choose only Prod Instance( you can see below)

Once you click on Next..It’ll do a file check for any outstanding issues which might prevent un installation, Once you passed the test, It’s matter of Just clicking “NEXT” and “REMOVE”….That’s it guys:). After 4 minutes or so, I got a message “Uninstalled Succesfully” as shown below.

See the below Screenshot, my PROD Instance is back to RTM where as my DR Instance is still on SP1

Prestooooooooooo! Pretty Simple huh??

Hope this is Useful information!

Installing SQL Server from Command line(CMD)

How often do you Install SQL Server in your Environment?? Monthly Once? In that case, I prefer just double clicking setup.exp in my installation media and follow the instructions from GUI. How about Weekly Once? Well, i would go with GUI again…considering the time i’ve for completing the task..What if you got a new SQL Farm where you are assigned with a task to Install SQL Server on all your Non Prod environments in a single day or two..Assuming you’ve DEV,SIT and UAT as your Non-Production environments and you’ve to install 8-10 Instances on a single day, i strongly prefer installing SQL Server from command line. It might be little annoying initially, but believe me you’ll be saving 65-70% of your precious time just by providing few parameters to your setup.exe from cmd line. Basically you’ll be skipping all the screens in GUI where you are supposed to provide all the required information through out the installation process. Let me show you what exactly I’m saying….

I’ve inserted my Installation Media(actually mounted my ISO using Deamon Tools) in my DVD drive and navigated to setup.exe from cmd(Run as Administrator). You can enter SETUP.EXE /? if you want to know all the available Options and Switches which you can make use of in this Installation as shown below.

Before proceeding any further, this is my game plan for Installation:

SQL Server 2008 DB Engine, No SSRS, no SSAS, no SSIS, no Tools(SSMS/BIDS). FYI…My Laptop already has 2 SQL 2008R2 Instances and 1 DENALI Instance….So I’m limiting my installtion with just SQL Server 2008 DB Engine.

InstanceName: SQL2008

SQLSVCACCOUNT (Service Account for DB engine): NT Authority\System

AGTSVCACCOUNT (Service account for SQL Agent): NT Authority\System

SQLSYSADMINACCOUNTS: Sysadmins on SQL Server. I’ll add myself here.

Features: SQL(This implies Just DB Engine, If you want to Install SSIS, SSRS, SSAS as well on the same server – which you should never do; you can enter IS, RS and AS as well)

SECURITYMODE: Windows Authentication/SQL Authentication – I’ll choose SQL in this demo.

SQLUSERDBDIR, SQLUSERDBLOGDIR, SQLTEMPDBDIR are pretty much self explanatory.

So, combining all together my syntax would become

All i did was just navigating to G:\(MY DVD Drive) and typed(ctrl+c and Ctrl+v 😉 from my scripts toolbox) the below and just hit ENTER on my Keyboard!!

setup.exe /QUIETSIMPLE /ACTION=install /FEATURES=SQL /INSTANCENAME=SQL2008 /SQLSVCACCOUNT=”NT Authority\System” /SQLSYSADMINACCOUNTS=”sreekanthpc\sreekanth” /AGTSVCACCOUNT=”NT Authority\System” /SECURITYMODE=SQL /SAPWD=”Pa$$w0rd” /SQLTEMPDBDIR=”D:\SQL2008\TempDB\\” /SQLUSERDBDIR=”D:\SQL2008\SQLData\\” /SQLUSERDBLOGDIR=”D:\SQL2008\SQLLog\\”

I’m welcomed with the installation progress(you can see screenshot below)

After 4 minutes I was returned to the main command prompt without any issues…and you can also see my Config manager with our new Instance(SQL2008) being successfully installed in the below Screenshots

Perfecttttttttt……..:) We avoided all the PAUSE and halts which we usually face while Installing from GUI. At this point, even if you want to change any service accounts or even Data/Log file locations, It’s Just matter of few minutes…. from our config manager!! So..all you need to do is Just save the above Syntax somewhere in your documentation and you can Just Copy Paste by changing the required parameters such as Instance name, Service accounts…etc as needed. Having a standard Drive(s) naming conventions and standard Drive Letters across your enterprise on your SQL Servers makes this process much simple as you can see!

Hope this is useful and you’ll start exploring this if you haven’t yet!!

How to Rebuild Master Database?? AKA Rebuilding SQL Server 2008R2.

Have you ever been in a situation where you lost the complete Drive where your System Databases are residing and even SAN Admins were not able to bring the Drive back?? Fortunately I’ve never been in that situation:) What would be our available options if that disaster strikes?? In this scenario restoring System Databases won’t work because SQL Server Service itself is completely down and your System Databases mdf and ldf files are gone!!..So what should we do?? REBUILD YOUR MASTER. Rebuilding Master will actually create brand new System databases from scratch as if you just Installed your SQL Server. There’s a misconception that we need the Actual SQL Server media and it’s mandatory to rebuild your Master. Those days are gone..That was the case with SQL Server 2005 and earlier. Starting SQL Server 2008 it’ll rebuild based upon the TEMPLATES which it creates in your BINN Directory. (This is the one of the main Reasons,you should place your SQL Binaries and actual System Databases on completely separate Physical Drives. Let me show you what I’m talking about…..You can see the templates folder which SQL Server created while initial Installation of this Instance on my machine.

These are not the actual system database files….I repeat, these are Just templates which SQL Server will create behind the scenes during Installation Process. My actual System Databases are located in DATA folder as shown below

Note: On My laptop I Installed every thing Just on C Drive. You should never ever do this in a real environment. There’s no single valid/good reason to do so…

Once rebuild process is complete, You’ve to Restore all the User Databases from your Backups and Restore Master and Master Databases in case if you want to get back all your Logins, Jobs, Server objects etc….Yes! I agree that sounds like a very painful process….But if you’ve all your recent healthy backups and few other dependent objects…you are the champion:) So..if you don’t have a proper Backup/Restore Strategy in place…you are gone!!..You might even loose your Job for this single most important valid reason. In this post, let’s try to rebuild our entire SQL Server/Master Database.

I’ll simulate scenario by Shutting down SQL Server Services on this Instance and physically deleting the mdf and ldf files of system databases and let’s see how it looks and what are the errors you’ll be getting in this case.

Step1: Now my Data folder looks as shown below

Step 2: I tried to Start SQL Server Service from our Configuration Manager and the error messages which i’m getting are (see below Screenshots).

In the Event Viewer I’m seeing the below

Okay…! In this case we know that the issue is not with Invalid Startup Option since we deliberately deleted our Master.mdf file.

Step 3: Let’s see how to rebuild using SETUP.EXE

As i already mentioned above we no longer need installation media to rebuild SQL Server(you can use it as well if you want to). All you need to know is where did you choose the “Setup Bootstrap/program files” while Installing your SQL Server initially. ( standardizing all these Drives, locations and paths across the Servers in your organization greatly reduces the pain to maintaining all these important details individually at Server level). In my case it’s “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release” as you can see below

Step 4: Open Command Prompt(Run as Admin) and navigate to this folder where you can find SETUP.EXE and we can do the “QUIET Installation” passing the required parameters. In My case the Syntax was

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=DR /SQLSYSADMINACCOUNTS=”sreekanthPC\sreekanth” /SAPWD=”Pa$$w0rd”

If you want to explore all other options and parameters available to us….you can issue “setup.exe/?” for help from windows as shown below.

In the above syntax every switch is Space Seperated. Here we are using Quiet Mode and the action is Rebuild master Database, Instance name is DR(For Default Instance: INSTANCENAME will be “MSSQLSERVER”), adding myself as a Local Admin and providing sa password(since mine is a mixed mode).

Very Very Important: Actually It failed for me and got below error Messages!!!! The error Messages were “The following is an exception stack listing the exceptions in outermost to innermost order Inner exceptions are being indented Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException Message: The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.” I’ve no idea what it is trying to convey here…Looks like a Bug( may be may be not) to me in SQL Server 2008R2( I’m using 2008R2 for this demo).
Work Around: I’ve selected “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe” instead of “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe“

Even if this doesn’t work for you, please try to make use of setup.exe from Installation media. This should work. I hope this strange behavior has been fixed in DENALI.

Sooo…..the actual Syntax and Path which worked for my case is as shown below

it took around 7-10 minutes in my case and got a command prompt without any errors as you can see in the above screenshot 🙂

FYI Failed path(in my case) as per Microsoft documentation is as shown below

Please keep this Bug in your mind and don’t just PANIC if you encounter the same error message.

Well, with all this hard work…what did we achieved?? Did SQL Server Instance “DR” got rebuilt?? Yesssssssssss as you can see below, all the System database files are back.

Also..I was able to Start SQL Services from the Configuration manager and as you can see below….everything appears to be brand new…. Tadaaaaaaaaaaaaaaaaaaaaaaa!! We did it guys 🙂 🙂

Now it’t time to (patch if applicable) and restore your Databases from your healthy Backups accordingly. You can see how to restore System Databases here.

That’s it Guys!!! You should be aware of this process and these challenges and work arounds as a production DBA. I would recommend to test this in your lab at least once or twice before actual disaster strikes your Production Servers…(I hope you never need this….But you should not be surprised when it hits). Hope this helps. Cheers!