SQL Server Discovery Report – Know What SQL components you’ve got Installed at a glance

Let’s assume you’ve got a new SQL Server under your support as a primary DBA and you’ve decided to investigate and document all the SQL Server components/features installed on this Server. What are your options? Will you go to Config manager and look at Services for Installed Services. Well, what about Client components? You can pull build levels/Version(SP/CU) easily for your database engine using T-SQL, what about other services? What about your client tool(s) version…Chances are they will be same as your DB Engine(but they could be different based on how and who patched the SQL Servers)? What if you’ve got multiple Instances of SQL Server with different versions…?

Well, we’ve something called “Installed SQL Server features Discovery Report” under Tools in your Installation Center which provides all this information in a single place 🙂 .  Go to the Installation Center from your start->All Programs->Microsoft SQL serverXXXX->Configuration tools->SQL Server Installation Center. Go to Tools and click on discovery report as shown below.

4

After few seconds, I got below report which gives me basic information on what all I’ve got Installed related to SQL Server on this Server with the build numbers, Edition, clustered/StandAlone etc all at single shot (You can see I’ve got 2 Named Instances, 2008 and 2012) which is pretty useful IMO.

5

Hope you guys find this nifty tool useful! Cheers…

NATIVE BACKUP ENCRYPTION – SQL SERVER 2014

Microsoft SQL Server 2014 for sure is bringing a lot of cool features/enhancements to aid DBAs and Database Developers….and one of my favorite features it has to offer for a DBA is “Native Database Backup Encryption”.

Till SQL Server 2012:
Let’s say you need to send a backup to Vendor and you want it to be encrypted as per your company security policies. Well, there is no way you can create an encrypted SQL Server database backup natively without help of third party backup tools from vendors like RedGate, Dell, Idera etc. If we don’t have any of those backup tools in our shop, our choice will be pretty much restricted to enabling TDE on the database and take a backup which will be automatically encrypted as well(Unfortunately TDE is Enterprise Edition only feature). What if you are running on standard edition?

Starting SQL Server 2014:
All you’ve to do is select a checkbox while taking a backup(if you are using SSMS GUI) or providing few additional switches in your T-SQL backup statement 🙂
Note: We should create Master Key and Certificate to be able to Encrypt the database backup.

Is this Enterprise Only Feature?
Nope 🙂 You can take Encrypted backups in Enterprise,Business Intelligence and Standard Editions.
Do we need to Turn on TDE at database level?
Nope 🙂
What are the supported Encryption algorithms?
AES_128, AES_192, AES_256, and Triple DES.

So, Now you can Encrypt your database backup in Enterprise,Standard,BI,Developer Editions and an encrypted backup can be restored on any edition(as long as you’ve got certificates 😀 )

Okay folks, now It’s Demo time……I’ve SQL Server 2014 CTP2 Installed in my lab and I created a test database “Tst” for this demo purpose. As of now, it’s a brand new Instance and a brand new database without any keys and certificates.

Step 1:
We’ve to create a Master Key and a Certificate in MASTER Database.(Shown Below)

3

Step 2: (Am using SSMS for now).
Go to Database Tasks from object Explorer and select backup.  In “General” tab , select your database Backup Path and now navigate to “Media Options” tab. This is important…“Backup to existing Media set” is not supported for Encrypted Backups as of SSMS 2014 CTP2. You should select “Backup to a new media set“(Shown below). Am not sure if this intended or Just a bug which will be fixed in RTM.

1

If not, your encryption section under “Backup Options” tab will be greyed out as you can see in the below screenshot 😦

2

Step 3:
After selecting your backup path and selecting Backup to new media set, check the Encrypt Backup option in the “Backup Options” tab and select your desired Algorithm and choose the Certificate which we’ve created in Step 1.

4

Click “OK”. Tadaaaaaaaaaaa……That’s it guys, we’ve created an Encrypted SQL Server Database Backup 🙂

Btw, if you are interested in T-SQL Syntax, here it is…

BACKUP DATABASE [Tst] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\tst_Encrypt.bak' 
WITH FORMAT, INIT, MEDIADESCRIPTION = N'tst Bak', MEDIANAME = N'Tst bac', 
NAME = N'Tst-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 
ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [Bcks_Cert]), STATS = 10, CHECKSUM
GO

In the next post let’s see how to restore this encrypted backup.

Delayed Durability in SQL Server 2014 – A Hidden Gem.

Till date, a relational database is all about being obedient to ACID rules . But starting SQL Server 2014, this is no longer a Gospel truth(in SQL Server world)! Yes, Microsoft has introduced something called “Delayed Durability” in SQL Server 2014, which is truly an outstanding feature to have in our wallet. Basically, we have flexibility to choose a Database being either ACID or ACIdD(Atomic, consistent, Isolated and durable with some delay). This is one hidden gem which really needs to be marketed besides “In-Memory” for SQL Server 2014 my MSFT IMHO.

Oh, common…there is no such thing as “ACIdD”. I came up with this stupid Abbreviation 😀

When this feature will be useful to you? Does it really matter to you?

Let me ask you this, how many times you’ve seen a wait type “WRITELOG” in your Monitoring tool(s)? Or how many times you’ve seen something is waiting on “COMMIT”? If your answer is “a lot” to either of these, probably you should evaluate this hidden gem.(This may or may not be useful to you, depending on how much data loss is tolerated by your business). You really need to understand what you are doing to your database by turning ON this feature.

How this works?

Delayed Durable transaction(s) Commits are asynchronous and reports to the user(or session) a COMMIT as successful before the log records of the respective transaction are written to disk!!!…Don’t be scared, SQL Server is storing those log records in Log buffer and will be pushing them to T-log on the disk with a delay(Hence, the name delayed durable…They will be durable, but with a delay 🙂 )

What happens if something bad happens before the transactions are really committed(written to the T-log on Physical Disk)?

You will loose those transactions. That’s where “sp_flush_log” comes handy. May be you can schedule to run this against your database(s) every N seconds/minutes depending on the acceptable data loss.

How Much delay?

You have this control…, By default SQL Server will flush the log records from it’s buffer to T-Log on the Disk when the buffer fills or you can issue “sp_flush_log” to flush the log to disk manually.

Note: Transaction durability is guaranteed by SQL Server only after following a flush of the in-memory transaction log to physical disk.

How to Enable this feature?

ALTER DATABASE  DB_Name SET DELAYED_DURABILITY = {Disabled|Allowed|Forced}

Delayed Durability = Disabled:

This is the default, your database will behave normally as it is till SQL 2012. Nothing Fancy…

Delayed Durability = Allowed:

With this setting turned on, each transaction durability is decided at the transaction level by issuing {DELAYED DURABILITY = ON|OFF}. So not all the transactions within your database are subject to delayed durable in this case.

Delayed Durability = Forced:

With this setting turned on, all the transactions are delayed durable within the scope of your database.

Or from GUI(SSMS), just go to your database properties and you can see options as shown below.

1

Very Nifty right…?

Note: This feature works on any normal user database, No need to be an In Memory Database.

So, now it’s your choice, whether to make use of this feature or not. But this will come to use in a very great manner if your T-Log disks are slow and/or your Writes are being committed with long waits and/or you see WRITELOG waits and/or your users are screaming “Man…., these DML queries are taking for ever to complete” and you know it’s because of your poor T-Log performance. So…there’s a trade off btw Speed and Data being truly available to you if disaster strikes. I bet, we all are supporting lot of databases which are really “Internal Only” type of deal and has no true impact on business with few hours worth of data loss(data staying in other sources internally within company which can be pulled easily if needed) which are hitting T-Logs crazily and transactions just waiting on “COMMIT” to complete. These type of databases are perfect candidates for enabling this awesome feature “Delayed Durability”…

I think this was not available in 2014 CTP1(I don’t have CTP1 to confirm now, I had to Uninstall 2014 CTP1 and Install CTP2 from scratch as Side by Side Installation of 2014 CTP1 and CTP2 is unsupported), So…download SQL Server 2014 CTP2 today and explore this awesome feature and make your self comfortable with this.

Fore More Information :

http://msdn.microsoft.com/en-us/library/dn449490(v=sql.120).aspx

Bottom Line : SQL Server Rocks 🙂

New features of SQL Server Management Studio 2014 (SSMS 2014)

Hello Folks! Last week I got chance to Install SQL Server 2014 CTP2 and I would like to share few things I’ve noticed with SSMS 2014.

Below is the screenshot of SSMS 2014 CTP2 Properties.

1

First of all, I noticed it is little faster to load initially when compared to SSMS 2012. Not sure if this the same with all of you, but I notice SSMS 2012 is little sluggish while loading up initially when compared to SSMS 2008R2.

If you are Azure Customer, there is something which you should be excited about SSMS 2014. You can directly backup your database to Cloud(URL) and restore from URL. Using SSMS 2014, now you can choose either disk or URL as backup destination as you can see in the below Screenshot…

2

Once you choose URL, you basically get options to provide your Azure Storage Container details…

3

Same with the restores, you can choose URL as your Source…and provide the details accordingly as shown below.

4

Is this really an amazing feature we should be excited about SSMS 2014? yes and No, depending on whether you use Azure or not. If you are not into Cloud yet, you can ignore this new feature anyways 🙂

If you notice any other enhancements in SSMS 2014(Not with actual SQL Server Engine itself) besides this, please post it in comments.