SQL Server TDE on Mirror and Log Shipping

Today in our Virtual Chapter meeting for VCDBA, I presented on SQL Server Encryption including TDE.  The question was posed as to whether or not the TDE would take affect if the mirror was established first and then you enabled TDE on the database.  I was not sure at the time only because I had not done any tests, so I did not want to say yes or no unless I could substantiate it.

I set off to do some tests.  First I created a Mirrored database using the demo code from the VC presentation.

use master
GO

CREATE DATABASE [TESTTDE] ON  PRIMARY 
( 
	NAME = N'TESTTDE', 
	FILENAME = N'C:\sqldata\S1\TESTTDE.mdf' , 
	SIZE = 10240KB , 
	FILEGROWTH = 10240KB 
)
 LOG ON 
( 
	NAME = N'TESTTDE_log', 
	FILENAME = N'C:\sqldata\S1\TESTTDE_log.ldf' , 
	SIZE = 10240KB , 
	FILEGROWTH = 10240KB 
)
GO

Now once the database is created on instance 1 I backed up the database and a log backup. Then restored it to the second instance with it’s log with no recovery. Once it was primed and ready, I established a mirror and saw the database was synchronized.  Now the trick is to use create the components to establish the Encryption Hierarchy.

USE master;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'INSTANCE1_Use1Strong2Password3Here!';
go

CREATE CERTIFICATE MyServerCert 
WITH SUBJECT = 'My DEK Certificate'
GO


Now that we have the MASTER KEY and the Certificate in place, you need to backup the certificate to a set of files and restore it to the second instance.

BACKUP CERTIFICATE MyServerCert
TO FILE='C:\SQLBACKUP\MyserverCert.cer'
WITH PRIVATE KEY (FILE='C:\SQLBACKUP\MyServerCert.pvk',
	ENCRYPTION BY PASSWORD='My1Secure2Password!')
GO

NOW CHANGE TO THE SECOND INSTANCE

-- Make sure that you change to the second instance

USE master;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'INSTANCE2_Use1Strong2Password3Here!';
GO
CREATE CERTIFICATE MyServerCert
    FROM FILE = 'C:\SQLBACKUP\MyServerCert.cer' 
    WITH PRIVATE KEY (FILE = 'C:\SQLBACKUP\MyServerCert.pvk', 
    DECRYPTION BY PASSWORD = 'My1Secure2Password!');
GO

Now that you have the Encryption Hierarchy established for both instances, let’s encrypt the database.

-- Make sure that you are in the Principal instance 
-- or this will not work because you won't be able 
-- to USE TESTTDE on the Mirror

USE TESTTDE
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO

ALTER DATABASE TESTTDE
SET ENCRYPTION ON;
GO

What will happen in the Mirror is that it will encrypt the database, even though you won’t be able to see it.  When it is fully encrypted, and you fail the database over in the Mirror, it will indicate that the database is encrypted. On the Principal you can see it with the query below.

SELECT 
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM 
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;

Now for the Log Shipping copy

To establish the test for the Log Shipping, do the same as you did for the mirror but restore the Full, then the Log with Standby so that you can see that it flags it as encrypted.  First establish the Log Shipping without encryption set up, and once you are in Standby on the Shipped side, you can use the existing Certificate to test the idea of encryption afterwards.

Steps:

  1. CREATE DATABASE
  2. SET Recovery Model to Full
  3. Backup Database
  4. Backup Log
  5. Change to second instance
  6. RESTORE DATABASE with NORECOVERY
  7. RESTORE LOG WITH STANDBY
  8. Change to first instance
  9. USE TESTTDELOGSHIPPING
  10. CREATE DATABASE ENCRYPTION KEY
  11. ALTER DATABASE TESTTDELOGSHIPPING SET ENCRYPTION ON
  12. BACKUP LOG
  13. Change to second instance
  14. RESTORE LOG TESTTDELOGSHIPPING WITH STANDBY
  15. Run the above query to see if the database is encrypted. You will see is_encrypted = 1

This shows that the Mirroring and Log Shipping processes are affected by the Database Encryption Process.

Happy Encrypting….!

Read More

sys.dm_db_index_physical_stats Easter Egg

I was reading through documentation on the DMV sys.dm_db_index_physical_stats and found that there are a couple of things you need to be aware of when using this DMV.  First, we know that there are parameters for the DMV.

Parameters

  • Database Id
  • Object ID
  • Index Id
  • Partition Id
  • Level of interrogation (DEFAULT, LIMITED, DETAILED, SAMPLED, NULL = LIMITED).

The interesting thing is that when any of the first 4 parameters are NULL, the default is to evaluate ALL of the items in that parameter.  For example, if you specify NULL in the Database ID then it will evaluate ALL databases in the server. If Object ID is NULL then it will evaluate all objects.  So I knew all that, but here is what I did not realize, quoting from the documentation:

passing values that are not valid to these functions may cause unintended results. For example, if the database or object name cannot be found because they do not exist or are spelled incorrectly, both functions will return NULL. The sys.dm_db_index_physical_stats function interprets NULL as a wildcard value specifying all databases or all objects.”

Additionally, the OBJECT_ID function is processed before the sys.dm_db_index_physical_stats function is called and is therefore evaluated in the context of the current database, not the database specified in database_id. This behavior may cause the OBJECT_ID function to return a NULL value; or, if the object name exists in both the current database context and the specified database, an error message may be returned. The following examples demonstrate these unintended results.”

Now that is nice, but here is what it means. If you are specifying a database id for use in this function and the database id is not the “current” database, then if the OBJECT_ID() function is used, it will evaluate it in the “current” database and NOT the database that is represented by the database id in the statement. If the object does not exist in the database, then it will result in NULL and will then proceed to do an evaluation on all objects in the database represented by the database id.

The other caveat is that if in the current database there is an Object by that name that results in an object id being returned, when the function begins, it will not be able to find the object id returned and will actually error. Take a look at the examples below and notice that you will want to be in the database that you intend to use if you are specifying anything beyond the database id. Don’t get caught with NULL in the statement without you knowing about it.

 

use master
GO
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2012'), OBJECT_ID('Person.Person'), NULL, NULL, 'LIMITED')

-- Results were for 190 rows 
/*
** What happens here is that it evaluates OBJECT_ID('Person.Person') to NULL 
** because it does not exist in master as shown below.
*/

use master
GO
SELECT OBJECT_ID('Person.Person')
-- Result is NULL
GO
use AdventureWorks2012
GO
SELECT OBJECT_ID('Person.Person')
-- Result is 1765581328

/*
** Let's create a table in master of the same name
** as in the AdventureWorks2012 database.
*/

use master
GO
CREATE SCHEMA Person AUTHORIZATION dbo
GO
CREATE TABLE Person.Person (id int)
GO
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2012'), OBJECT_ID('Person.Person'), NULL, NULL, 'LIMITED')
GO
-- You get the error below
/*
Msg 2501, Level 16, State 40, Line 3
Cannot find a table or object with the name '.StateProvinceCountryRegion'. Check the system catalog.
*/
Read More

Precon: IndyPASS 2015 – PowerShell and SQL Server

I am fortunate to be able to present a precon at IndyPASS this year. The link to register is PowerShell for the DBA from 0-60 in a day.

We will go through PowerShell from a DBA point of view and there will be scripts, techniques and lots of information on how you too can be empowered and free up some time using PowerShell and SQL Server SMO. I hope you will join me and others this year at IndyPASS 2015

See you there.

Read More

T-SQL Tuesday #61–Giving Back

TSQL2sDay150x150

I like this topic because I think about it all the time.  I have learned so much from others and I am trying each year to ensure that I give back by speaking at SQL Saturday’s across the country.  No one pays for my trips (most of the time) but it is the least I can do for those that have helped me in my career.

Giving back is so much more than just speaking or giving a gift, it is about helping someone replace you in the Community. Mentoring, speaking, teaching and leading.  I lead my SQL Server User Group locally with another awesome guy and I speak at local events thanks to another great guy. In our town we have Utah Geek Events run by Pat Wright who keeps us full of opportunities to give back. I just cannot get enough.

So this year will be full of speaking, leading the group and attending the other User Group locally

Technorati Tags: ,

to ensure that I am available. Not for love given to me but to ensure that if I have experience that someone needs, that I am there to share with no strings, no ego and no agenda. That is how I will give back this year.

I hope to blog more and get more articles written and to support just a little bit more than last year so that people looking to learn will have yet another place to go to get some help. Thanks to all those that have helped me along the way, most of you know who you are and the list would be way too long to list here and I would for sure leave someone out and I would never do that to my #SQLFamily.

Happy Holidays and see you around.

Read More

PASS Summit 2014 – What to expect

When thinking about attending PASS Summit each year I don’t spend much time deciding. I have not missed a Summit yet and hope I won’t miss one in the future. This post is meant to help those that may be deciding whether or not to go or for those that have decided to go but really don’t know what to expect.

At PASS Summit there are many things to do. The list below resonates in my mind every year:

  • Networking
  • Learning from others
  • Finding those you have not seen and catch up with them
  • Meet new people, your favorite MVP or MCM or just a great figure to finally put a face to the name
  • Learn new things

Some of my favorite Summit experiences include meeting people like (not a definitive list) Kalen Delaney, Paul Randall, Kimberly Tripp, Brent Ozar, and the list could go on. Meeting these people for me was a treat and has made my PASS trips great to reconnect and chat with them and so many others to name off. The PASS Attendee parties are also one of the highlights because it was a chance to talk with peers and some of the famous ones in a setting that was not about database. You learn things like Kalen loves air hockey and some DBAs are really funny, but more than that, you can chat with the leaders of the PASS organization and board members.  These are times I will remember for a very long time.

First Timers

For you first timers, you are in for a treat. This is the first Summit for you and hopefully won’t be the last. For you there is finding and meeting people and speakers that you have seen for many years, maybe, and now they are there to shake hands with and chat with you. They are not untouchable, they are real. Friendly SQL Server Professionals that will actually be willing to chat with you.  There is the Expo where you can see products you have heard about for a while and actually see how they work.  It is NOT all sales and hype, there are actually some very good demos there.  You will always get some sales but this is your chance to get to know the vendors better.  Here are a list of things I would do as a first timer having been through many Summits and I could actually be a great first timer now. 🙂

  • Find out who is presenting and mark the ones that you MUST see and then those that you would like to if the opportunity presents itself
  • BUY THE RECORDINGS, no matter how good of a time manager you are, you will inevitably get overwhelmed with the amount of stuff that is out there and the people who are there, this will give you the opportunity to watch the recorded sessions you missed because you are networking.
  • Did I already mention buying the recordings before the show?
  • Make time for the Community Lounges, SQLCAT Support Center and anything that you cannot get online or in a presentation. Get in front of people and BRING BUSINESS CARDS to give out so that people get to know who you are and they may give you their contact information. BUILD A NETWORK.
  • Go to the MUST see presentations since education is still a huge part of the Summit
  • Attend the Keynotes or watch them, sometimes they surprise you with announcements you won’t get anywhere else.
  • Get to know the Board of Directors of PASS. Hold them accountable as they lead the organization that you are a member of.
  • Investigate opportunities to volunteer for PASS or one day run for the Board.  Passionate people is what makes PASS great, remembering that passion begins with PASS. 🙂
  • Attend the online First Timers presentations that are out there.  I believe there are many but these are great events. I believe that there is even one now onsite at the PASS conference. Get to know what is there for that year.
  • Find the SQL MVPs, they are out there.  These volunteers in the SQL Community have passion towards SQL Server and are always willing to share.
  • Find out who the Microsoft Players are and their role and network with them.
  • Many more things that you can do to enjoy and always want to come back for another Summit.

Preparing for your next experience today

If you begin with the next year in mind, set up the relationships that you will renew each year at the Summit and you will find yourself refreshed and renewed each Summit you attend. Do things to keep in touch with people you meet, like getting their contact information, giving yours out to set up collaboration during the year you are away. I keep all the cards I receive in a place in my backpack and it is amazing how many I have at the end of Summit. Obviously this is not an opportunity for free consulting, but to get a quick opinion or a quick answer to a question, your network is your best friend. The next thing you should do to complete your experience is when you leave Summit, look for the email where registration opens for the next Summit and most often it is a very significant discount to register before the year ends than to wait until the next year. Save some money.  Finally, get to know the players and you will be playing for the rest of your career.

Valuable Lessons Learned

The most valuable lessons I have learned at my PASS Summits I have attended is that people at PASS Summits are real people. They have lives outside of their career and they have failings just like me. I don’t know everything and they don’t know everything, that is why we get together and chat with each other because they share what they have learned and experienced and I do the same, and we come away better for it. The people you meet at PASS and the leaders that lead PASS are just like you and me, they are not better than you or me and we are not better than them, we have just had different experiences and that is what makes a PASS Summit FUN. Swapping stories, learning new things and renewing the PASSion that you have gained for SQL Server and the technologies that surround it.  Chatting with the Microsoft folks and the MCM and MVP crowd can enlighten you in an environment that cannot be replicated at work, take advantage of this opportunity, you won’t regret the time you spend.  But most of all Unwind, HAVE FUN and take it all in, you will be exhausted when you get done, but it will be an experience second to none.

I hope to see you there and hope your experience will be well worth the time. Here’s to all the SQL Community or #SQLFamily out there that will be attending, can’t wait to see you there.

Read More

Speaking at Portland Maine #SQLSAT293

SQL Saturday

I am excited to speak at the SQL Saturday 293 in Maine this weekend. Hope to see you there.  There is a great lineup in the agenda. My talks will be SMO Internals for High Performace PowerShell and SQL Server TDE.  The links will take you to the session where I will upload my slide decks and scripts.

It will be a great time and I look forward to seeing the likes of Adam Machanic (blog | twitter), Wayne Sheffield (blog| twitter), Grant Fritchey (blog | twitter), David Klee (blog | twitter), Andrew Kelly (blog | twitter) and all the other great speakers that will be there.

See you in Maine.

SQLSAT293_SPEAKING

Read More

MCM SQL Server 2008 Achieved

Another step in my career as I have passed the Lab Exam and have become an MCM in SQL Server 2008.  I believed I could achieve it after passing my knowledge exam the first time.

The first attempt at the lab was back in October and it was disappointing to find out that I did not pass the first time. I thought about what I could have done better and determined that I let time get away from me.  I had the experience I thought I needed to do well on the lab exam, but could not get to the point of finishing all the objectives and then there were those that I had not had experience with.  I found myself rehashing my experience to find that important part of getting prepared.  I did not think that study would help enough, so I started with my own lab environment (VM with SQL Server on it) and I ensured that I knew how to work with most of the engine features in BOL and thought of my strategy for the second attempt.

As I reflect on my attempt and prior to that day, I am grateful for the time I took to teach the concepts I was learning in my lab environment, to others. I also love the User Groups in Utah for allowing me to solidify some concepts I felt were important to know and be very familiar with by presenting at the Group meetings.  I may have taken it sooner had I had a study buddy to bounce things off of, but I found that the post from Rob Farley I got a new lease on life for the test.  I was confident in what I knew, but I am not as great in managing my time in scenarios of stress.  It helped to have the test administered via Lync at my house, which was much better than where I took it last time.

The day of the test, the proctors were awesome and took good care of me and explained the process.  They were very understanding and that set my day off to a good start. During the test, I employed some techniques that I learned in preparing, that were all about time management to allow me to get through the Lab and demonstrate what I knew how to do. I think that my time management really helped me the most as I already had the experience I believed I needed to pass the test.

Thanks to all the other MCMs that shared their expertise in presentations, emails and all those that were totally encouraging me to attempt my MCM experience.  But most of all to a supportive manager and employer (www.healthequity.com) that stood by me in the expense and time to take this exam. I am honored to be among the MCMs in SQL Server and looking forward to the road ahead.

Read More
Menu