Monday, March 7, 2011

T-SQL Tuesday 16: Aggregation




So it’s that time again, T-SQL Tuesday.  This week our event is Sponsored by Jes Borland (Blog|Twitter).  So Jes what’s the subject?

I want to hear how you solved business problems with aggregate functions. I want to see your cool T-SQL tricks. How are aggregates used in SSRS, SSAS, or SSIS? What have you learned about aggregate functions?

So Dear Reader, Aggregation it is!

SOME THINGS JUST GO TOGETHER




We’ve all probably done our fair share of fancy aggregates, I have to say that most of the code I’ve used aggregates for is for business functionality and sitting with clients that I’ve worked for in the past.  I think we all have a nightmare report, script, or task that we accomplished using aggregates and while those things make for interesting stories, they aren’t that useful.  I’d like to give you something that you can use, (if you don’t already have a script to do what I’ve done here). 

Sometimes the most useful things are simple, like PB & J (simple and delicious!).  Here are a couple of queries that I use that have the SUM aggregate function in them.  Simple but effective.

The first is a quick script that extracts the information from DBCC SHOWFILESTATS and uses the "undocumented" sp_msforeachdb in order to gather total space used by all datafiles on an instance, total space reserved for all data files on an instance, then a breakdown by datafile per database, lastly we take the info from DBCC SQLPERF(LOGSPACE)and sort by the log file size.

The second script is of the same concept.  You can use the built-in reports “Disk Usage by Tables” to get the same info, but sometimes it’s just nice to build a script to work for you.  This script uses sp_spaceused and the "undocumented" sp_msforeachtable in order to build a list of tables by row number and the Reserved size for Data. (I use the Second script on my Compression demos to help you find an appropriate table (size wise there are multiple steps J) while examining compression).  Then I do a SUM function on each of the fields to show an aggregate of your database by user table.

SCRIPT1 – Get Database Size Stats

/*============================================================
--@SQLBalls: SQLBalls@gmail.com
--http://www.SQLBalls.com
--T-SQL TUESDAY 16 Aggregation
--
--This Sample Code is provided for the purpose of illustration only
--and -is not intended to be used in a production environment. 
--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"
--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING
--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
--FITNESS FOR A PARTICULAR PURPOSE.
==============================================================*/

IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable%' and name not like'#myTable2%')
BEGIN
    DROP TABLE #myTable;
END
CREATE TABLE #myTable (
                      [myID] [int] IDENTITY(1,1) NOT NULL,
                      fileid varchar(4),
                      fileGroup varchar(4),
                      TotalExtents int,
                      usedExtents int,
                      DBName sysname,
                      filename1 varchar(8000));
GO
                     
IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable2%')
BEGIN
    DROP TABLE #myTable2;
END
CREATE TABLE #myTable2(
                      dbName sysname,
                      logSize float,
                      spaceUsed float,
                      dbStatus int
                      );                   
GO

INSERT INTO #myTable
EXEC ('sp_msforeachdb @command1="use [?]; dbcc showfilestats"');
GO
                     
INSERT INTO #mytable2
EXEC( 'dbcc sqlperf(logspace)');
GO

SELECT
((SUM(usedExtents)*64)/1024) AS DatabaseUsedSpaceinMB
,((SUM(totalExtents)*64)/1024) AS DatabaseSizeinMB
FROM #myTable;
GO

SELECT
    DBName
    ,usedExtents
    ,TotalExtents
    ,((usedExtents*64)/1024) AS DatabaseUsedSpaceinMB
    ,((totalExtents*64)/1024) AS DatabaseSizeinMB
    ,(((totalExtents*64)/1024)-((usedExtents*64)/1024)) as DataFileFreeSpace
 FROM
    #mytable
 ORDER BY
    ((totalExtents*64)/1024) DESC;
 GO


SELECT
    *
FROM
    #myTable2
ORDER BY
    logsize DESC;
GO

DROP TABLE #myTable;

DROP TABLE #myTable2;
GO




SCRIPT2-Get Table Size Stats

/*============================================================
--@SQLBalls: SQLBalls@gmail.com
--http://www.SQLBalls.com
--T-SQL TUESDAY 16 Aggregation
--
--This Sample Code is provided for the purpose of illustration only
--and -is not intended to be used in a production environment. 
--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"
--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING
--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
--FITNESS FOR A PARTICULAR PURPOSE.
==============================================================*/

IF EXISTS(SELECT * FROM TEMPDB.sys.tables WHERE name like '#myTable%')
BEGIN
    DROP TABLE #myTable
END

CREATE TABLE #myTable(
                               [Name] sysname,
                               [Rows] int,
                               [Reserved] varchar(500),
                               [Data] varchar(500),
                               [Index_Size] varchar(500),
                               [Unused] varchar(500)
                               );

INSERT INTO #myTable
EXEC ('sp_msforeachtable @command1="sp_spaceused [?]"');
GO

SELECT * FROM #myTable
ORDER BY
    [rows] DESC,
    LEFT([reserved],(LEN([reserved])-3)) DESC;

SELECT
    SUM(CAST(LEFT([reserved],(LEN([reserved])-3)) as int)) as [Total_Reserved(KB)]
    ,SUM(CAST(LEFT([data],(LEN([data])-3)) as int)) as [Total_Data(KB)]
    ,SUM(CAST(LEFT([Index_Size],(LEN([Index_Size])-3)) as int)) as [Total_IndexData(KB)]
    ,SUM(CAST(LEFT([Unused],(LEN([Unused])-3)) as int)) as [Total_UnusedSpace(KB)]
FROM  #myTable

GO

DROP TABLE #myTable;

  
Happy T-SQL Tuesday I hope you enjoy the scripts!

Thanks,
Brad

Sunday, March 6, 2011

OPASS: Transparent Data Encryption the Lightning Round!



I’ll be presenting at OPASS on March 8th at 6:00 pm CLICK HERE to register to attend the meeting.

This is just a preview presentation so I won’t be deep diving the topic, we’ll just be scratching the surface.  So I’ll start by talking about Transparent Data Encryption, what is it?

WHAT IS IT?

Let’s break down the name into its three parts.

Transparent-The process is Transparent to everything going on in the server.  When you turn this on you do not need to make any changes to the way an application access the data.  As far as the Application knows this is the same database it was before it was encrypted.

Data-The data itself is affected. “So Balls,” you ask “’The Data’ is a pretty wide open term, what do you mean by ‘The Data’?”  Great question!  Any way in which SQL Stores Data is encrypted when Transparent Data Encryption is turned on, for the Database that it is turned on for.  This is a Database Level Technology similar to how Mirroring is a Database Level Technology.  Your Datafiles are encrypted and your Log File is encrypted  (VLF’s are encrypted starting when encryption is turned on, VLF’s previous to that will get encrypted when they are overwritten).

Encryption-There is some flexibility in the type of encryption Algorithm that you can select, during my demo’s I’ll be using AES 256 for my encryption level.

Need a little more?  Okay on to the next analogy.

PHYSICAL DATA SOLUTION

Seeing as how I’m a comic book kind of guy, the best analogy I can give is the X-MAN Colossus.  Transparent Data Encryption is a Physical solution, your .MDF, .NDF, .LDF, .BAK, .TRN, and .DS (Snapshot files) will be encrypted on the disk while at rest.

So on the outside your armor is on, inside (when data is in motion) however your flesh and blood.

DATA IN MOTION

“So Balls,” you say “What is this Data in Motion?”  Well Data in Motion just means that the data has been fetched from Disk by the Storage Engine, and is cached in Memory.  So while this is Transparent to all the applications that point to your database, you must keep in mind that SQL Server is an application as well.  And if you have access to read the data before you encrypted it, you have access after.  If you are looking for Transparent Data Encryption, TDE, to provide complete row level encryption after the data is fetched, then you need to be looking at Column level encryption.  TDE will only encrypt data at rest on the disk.

WHY USE IT

This is the Fifty Million Dollar question, and I’m going to give you a preview of my slides.


When Data is stolen it effect’s not just you the DBA, Your Customer's Identities, your Businesses reputation, and the financial toll to your Business as well.

Nobody wants to be the DBA in any of those headlines.  I’ve been in the situation where there have been security breaches, and every single gap you can cover is one that you are glad you did.  If your server is breached  you could find yourself in a room with a CIO, VP’s, Directors, Civilian Leadership, a General, Base Commander, Congressional Committee,  your Project Manager, or anyone one else in a position of authority.  In that moment what would you like to say?  My database(s) had Transparent Data Encryption on them, or we passed on having TDE on the server for [name the reason, and  you WILL really need to justify it].

 Sometimes this won’t be up to you.  You can recommend it and your business can choose to pass.  All you can do is be ready, because if you stay in the business long enough eventually you’ll be in this situation, *IF you’re lucky enough to catch the breach.

In the presentation we’ll cover what a datafile looks like in an unencrypted vs. an encrypted state, how to turn TDE on and how to turn TDE off.  How it effects the Tempdb, backup compression, and some tips and scripts for self managing TDE Certificates.

I’ll have a follow up post later in the week after the presentation.  I hope to see you there!

Thanks,

Brad

Thursday, March 3, 2011

Automate Deploying Maintenance Plans in SQL Server 2005, 2008, and 2008 R2

Have you ever set up a SQL Server before? If you’ve landed on this post, chances are yes you have! And for most of us we haven’t set up just one SQL Server Instance we’ve set up SQL Server many, many times.  When you have to do a repetitive task you figure out the best way to do the task as quick as you can.  For DBA’s this normally means automating the process, setting up a SQL Agent Job, a Scheduled Task, or using a 3rd party scheduling software because we’ve normally got other fires to put out.

So when you set up a new server what are some things that you automate?  Normally you have a standard set of Jobs, Alerts, and Maintenance Plans that you want to have in place.  You document them, you put together master Scripts that you will deploy so you don’t have to create them by hand.  But that doesn’t work for Maintenance Plans.

"So Ball's", you say "So I have to make them manually, big deal."  Ahh Dear Reader when your piecemealing them one at a time, it may seem like no big deal.  But let us say that we are upgrading HUNDREDS or even THOUSANDS of servers.  Your going to want to skip as many steps as possible.

I'VE FOUND A NAME FOR MY PAIN AND IT'S BATMAN MAINTENANCE PLANS

If you Create a Maintenance Plan, let’s make a Simple one, and try to script it out you’ll notice you can’t.  So if you take a look at the tables that store the Maintenance Plans In MSDB, you'll find that the Maintenance Task is in varbinary(max) format.  You can try scripting it out, but that is the old fashion way.  

Sometimes we as DBA's get used to doing things the old fashion way, we need to get with the times.

Embracing the New Hotness SSIS!

There's a New Game in Town!  If I imported an SSIS package to your server, and you wanted to place it on another server, how would you go about this Dear Reader.  Why you'd use SSIS!  Scripting out jobs is so SQL 2000, we've grown and so has our technology, so let's treat a Maintenance Plan like we should, it's an SSIS package and we shall use SSIS to migrate it.

If you look closely at the picture you will see I have a SQL 2005 & a SQL 2008 R2 instance on my laptop.  For this demo, I will create a Maintenance Plan in 2005, and using SSIS Export the Maintenance Plan to my 2008 R2 instance.  

Step 1 Create the Maintenance Plan
Name the Maintenance Plan
This will be a simple plan with two subplan's, Integrity Check and Update Statistics.
Let's Save the Maintenance Plan and verify that it created SQL Agent jobs for each subplan.

Step 3 Let's log onto my local SSIS instance (*This could be a remote instance SSIS doesn't have to live on the server where you created your Maintenance Plan)



Because this is local I don't have to Import the Maintenance Plan it is already in my SSIS server stored under Maintenance Plans, and it will be titled what ever you name it.

Step 4 Export the Plan to the desired location


Type in the Server Name that you want to deploy your package to.  Click on the ellipsis button

Click on the Maintenance Plan folder, Click OK

And Click Okay
Here is a before, so you can see there was not Maintenance Plan.
And Here is the After the Maintenance Plan is in place. 

But Wait There's More

If you take a closer look Dear Reader you will find that your Schedule and your SQL Agent Jobs have not been created.  You must manually re-create these.  

No Schedule

And No Agent Jobs.  Creating the Schedule will take some clicking, but the Agent jobs will be created as soon as you open and save the Maintenance Plan.

Plan Owner

The Plan Owner will end up being the Windows Account that you authenticated to SSIS with, in order to set the Maintenance Plan Owner as SA you should run the following script.

SQL 2005

use msdb;
go
update dbo.sysdtspackages90
set ownersid = (
select sid from msdb.sys.syslogins where name = N'sa')



SQL 2008 & 2008 R2

UPDATE msdb.dbo.sysssispackages
SET [ownersid]=0x01



I hope you find this helpful, and it get's you along the way of standardizing your Maintenance Plans in 2005, 2008, and 2008 R2.  (In a couple month's I'll be redoing all this for Denali!)


Thanks For Reading,

Brad