FreeCon in Seattle, October 27th before PASS Summit 2015

SQL Solutions Group is getting ready to host a day of training. This will be a FREECON, which means that it is free and before a conference. Seattle is the place for PASS Summit 2015 and that is where the FreeCon will be held. Registration is here.
More information is here. This training is not a part of the Summit. Below is a little detail of what I will be speaking on.

Practical PowerShell for the DBA
Think of all the tools you use in managing your SQL Servers. All those SQL Servers being managed by tools and man that is a lot of clicks. We will show practical scripts and techniques to help you get a handle on all those clicks. Whether you are gathering data or statistics from your SQL Servers or deploying an object to all of them. Configuration items are not excluded from the need for good tools. PowerShell is that tool that will let you get away from all those clicks. Reusable scripts that let you manage all those instances with ease. This session will give you a great start on how to think about admin tasks using PowerShell scripts or modules. Many items are already out there to help you and we will take a good look.

I hope you will join us for a great FreeCon before the Summit starts.

Happy PowerShelling!

Read More

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
Menu