T-SQL Tuesday #32 - A Day In The Life

T-SQL Tuesday has been a little off my radar for the last few months (as has all blogging activity!), so I was delighted when Twitter reminded me that it was that time of the month. Even better, the topic chosen by Erin Stellato (Blog | Twitter) was a great one that I felt I could really engage with.

So here we go. For the record, my job title is Database Administrator.

The task as laid out in the invitation suggested to talk through your day on the 11th or 12th July which just so happened to be the day I resigned from my current role. Writing down a handover list was an interesting task in itself as it gave me the opportunity to put down in words what the key parts of my job were and what skills gap would be left. I have to say, I was surprised (and pleased) that the number of tasks on the list that required significant handover to another collegue were minimal as I have been very diligent in ensuring that I was not a Single Point of Failure in my organisation. As such, I have written plenty of documentation and made extensive use of Source Control and Task Tracking systems to ensure that even if people do not know what I've done, there is a paper trail to explain it.

What was apparent was the varying skills I employ in my day to day, many of which are not even related directly to SQL Server. Much of my day is spent in project meetings, planning or analysing roadmaps and although the applications typically have a database backend, its not the focus of my attention. I am fortunate to be able to dedicate some time each day to reading blogs and technical articles to help stay up to date although I also spend my fair share of time in firefighting mode. The traditional DBA element probably takes up less than 1 day a month partly due to the small size of the estate but also because I've been effective (backslap for myself here!) in automating many of the tasks.

Is my job really a Database Administrator role? I don't think so and in truth it wasn't from the outset but this hasn't been a bad thing. I enjoy the variety of being involved in the different aspects of SQL Server and feel it makes me a much more effective professional and I imagine that this is the case for many other fellow SQL Server pros. However, I've encouraged my company to not advertise the role under this job description as I believe it may attract candidates with unrealistic expectations.

As for me, I'm not sure what my next role has in store but I hope it affords me the time to participate in future T-SQL Tuesdays!!

Admin: Bulkadmin vs ADMINISTER BULK OPERATIONS

I had an application thrust upon me recently which required the user executing it to have permissions to use the BULK INSERT command in T-SQL. I was aware of the server role bulkadmin which would have been a nice sledgehammer approach to crack this nut, but I was hoping for something a little more refined. Unfortunately, the only thing I came up with was the ADMINISTER BULK OPERATIONS command which would grant this privilege without having the server role membership but I was curious as to what the difference between the 2 was.

As far as I can tell, there is no difference between the 2 operations and both seem to achieve the same thing. From this I assume that bulkadmin is just a wrapper around the ADMINISTER feature although I'd be interested in knowing (as I couldn't find out) if there are other permissions the server role gives you.

Lets look at the example (running on SQL2008 SP2)

-- create some test logins
CREATE LOGIN [DOMAIN\user1] FROM WINDOWS
GO
CREATE LOGIN [DOMAIN\user2] FROM WINDOWS
GO
-- lets impersonate a user
EXECUTE AS LOGIN = 'BARRHIBB\user1'
GO
-- and check the server permissions 
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO 
-- does the user have bulk op permissions? 
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS');
GO

-- lets go back to being a superuser 
REVERT
GO
USE MASTER
GO
-- how about if we just grant administer bulk operations to the user? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO
-- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1' 
GO 
-- and check the server permissions
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS'); 
GO



-- can the user grant other users to be bulky people? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO

Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.

So lets see what happens with the server role:

REVERT
-- remove the users permissions 
REVOKE ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO 
-- now add the user to the bulk admin role and check permissions again
EXEC MASTER..sp_addsrvrolemember @loginame = N'DOMAIN\user1', @rolename = N'bulkadmin'
GO
 -- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1' 
GO 
-- and check the server permissions 
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER') 
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS'); 
GO

NB: The main difference here is that the permission has not been explicitly granted but it is an effective permission.

-- perhaps with the server role the user grant other users to be bulky people? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO

Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.

So it seems that the bulkadmin server role doesn't offer any "admin" from a security point of view which begs the question, just why would you add a user to this role rather than just explicity grant them the permission through the GRANT statement (or vice versa)?

Admin: Start and Stop Local Instance of SQL Server

As a database professional, i'm accustomed to having a local sql server instance installed. However, it seems that SQL Server (typically Express) is finding its way onto more and more desktops, often unused and unknown to the user!! With the web browser FireFox taking up almost half your memory, you don't want any other applications taking your valuable resources.

I recommend changing the service settings of your SQL services to manual and only turning them on when you are actually going to use them. If you're using a shared database server, you may find that even as a SQL developer you may not use your local instance as often as you might have thought.

I have created 2 simple batch files on my desktop which I can use for stopping and starting my local SQL services meaning its a mere double click away to get your instance available.

StartSQLServices.bat

NET START mssqlserver
NET START sqlserveragent
NET START ReportServer
NET START msdtsserver100

StopSQLServices.bat

NET STOP mssqlserver
NET STOP sqlserveragent
NET STOP ReportServer
NET STOP msdtsserver100

* your Service names may vary depending on instance names and SQL version.

Admin: can't drop impersonated login

This week, I came across an interesting behaviour while working with EXECUTE AS. I'd set up a test database to demo some work with a test login and when trying to tidy up the objects, I was faced with:

I can repro the behaviour with the following example. NB: there are 2 separate connections in this script.

/*
CONNECTION 1
*/

-- create a test database
CREATE DATABASE TestDropLogin;
GO
-- and a test login
CREATE LOGIN DropLogin
WITH PASSWORD = 'abc';
GO
USE TestDropLogin
GO
-- and finally a test user for that login
CREATE USER [DropLogin]
FOR LOGIN [DropLogin]
GO

EXECUTE AS LOGIN = 'DropLogin'
GO
SELECT ORIGINAL_LOGIN() -- 'DOMAIN\MyUser'
SELECT SUSER_SNAME() -- 'DropLogin'
GO
USE [master]
GO

/*
CONNECTION 2
*/
-- drop the database as we no logner need it
DROP DATABASE TestDropLogin;
GO
-- try and drop the login too
DROP LOGIN [DropLogin]
GO

Msg 15434, Level 16, State 1, Line 1
Could not drop login 'DropLogin' as the user is currently logged in.

That's because I didn't revert my execution context earlier and so I'm still logged in as DropLogin. If I go back to the 1st connection I can run the REVERT command.

Msg 15199, Level 16, State 1, Line 1
The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

But i've dropped the database where EXECUTE AS was called so issuing a USE command gives us:

Msg 911, Level 16, State 1, Line 1
Database 'TestDropLogin' does not exist. Make sure that the name is entered correctly.

So I don't appear to be able to REVERT the execution context and therefore can't drop the login. The only solution I found to this was to force the connection to close by issuing a KILL command.

Admin: Enable .Net 3.5 SP1 on Windows 2k8

.Net 3.5 Framework SP1 is a pre-req of installing SQL2008 onwards and so I'm posting this here as reminder to myself of how to get this on your Windows 2008 server as you need to enable it via Powershell.

1) Run Powershell with Administrator privileges
2) Execute Import-Module ServerManager
3) Execute Add-WindowsFeature AS-NET-Framework

This is what it should look like:



Enjoy.

Admin: Sizing your transaction log

You will often hear DBA folk telling you how you need to size your transaction log appropriately and, like data files, avoid shrinking it. Why is this?

Well, lets try using a finance theme with a Credit Card analogy which records all your purchases but it also has a limit. When you fill your credit card and reach your limit you either need to pay off the balance or extend the limit by asking/begging your issuer for a bigger limit. Its possible of course (and will probably be successful) but will take up some of your time and may involve the provider asking some awkward questions. Of course, there may be times when you need to extend your limit, perhaps when you're moving house but there is a cost to it and its something you want to avoid doing too frequently. Hopefully when you set up your credit card, you do so with an appropriate limit based upon knowledge of your spending habits.

All of this is the same with a transaction log. It too has a limit which is its size and when it gets full it will either stop taking transactions or ask the operating system for more space (if set to Autogrow). Think of auto grow as akin to asking your credit card provider for that extension which will most probably granted but not without effort, here the time it takes to provision the file growth. While the file is growing, transactions will be queued up meaning your application can't process its requests and things will start to get ugly. Alternatively, rather than extend the limit of your log, you can perform a transaction log backup and "clear the balance" or if your database is in SIMPLE recovery mode, this behaviour is done on checkpoint.

Your ideal scenario is to have auto grow set on your log but with a maximum size set on the file (you don't want this credit card brining down the whole of the bank!!) and ideally it will be sized at a level which won't require too many file growth operations. You want to avoid shrinking the physical file as you don't want to repeatedly go through the "limit extension" process as its just unecessary cost. If you know your app needs 10GB, then just set it at that.

Admin: Disk Configuration RAID 0+1

I saw an interesting question SQL Server Central recently on RAID Configurations. Essentially, it was asking which RAID level is more tolerant to multiple disk failures RAID 1+0 or RAID 0+1.

I have to be honest, i'd never even heard of RAID 0+1 before then and it got me thinking exactly what the configuration would look like. So i understand that 0 is striped and 1 is mirroed but how does mirror/stripe differ to stripe/mirror? Reading wikipedia gave me some information but to be honest, it still wasn't 100% clear so i had to get out my pencil and paper to try and visualise the difference.

I think i've got it, hopefully aided by the following diagram:



Essentially, if you have a RAID0+1 array, the whole sum of the disks is mirrored, not the individual disks (10 = 10). So if you have a disk failure, then the mirror is broken as there is no quorum disk to calculate the missing disk data and read requests can only be serviced by the one stripe set (7 != 10). This leaves you exposed to any disk failure in the remaining stripe set giving you a increased risk of data loss. In a RAID 1+0 array, as each disk is mirrored requests can be serviced by either array and as long as you don't suffer a failure of a pair of mirrored disks, your data is available.

I'm not quite sure what the benefits are of using one over the other. Once there has been a disk failure, I suppose you get better write performance with 0+1 as it is effectively a RAID0 array but i'm not sure that counts as a benefit. I'll be sticking with RAID 1+0.

Powershell: Copy those backups

I blogged earlier this year that i'd endeavour to make more use of Powershell and here is my first implementation of a powershell script.

I wanted to copy my SQL Backups to an offsite location and thought i'd harness the power of Powershell to achieve this. I have a simple SQL Agent Job with a step which backs up my files to a drive. Once completed, the second step (a powershell step) copies the files to the remote server and a final step to remove files older than 5 days.

Step 1:

A SQL maintenance plan which backups the files but I won't be showing whats involved there. I really want to get down in text the content of my Powershell scripts:

Step 2:

copy-item "F:\SQLBackup\*" "\\MyRemoteServer\SQLBackups" -Force -Recurse

Step 3:

$files = gci \\MyRemoteServer\SQL
foreach ($file in $files)
{
   $datediff = ((Get-Date) - $file.CreationTime).Days
   if ($datediff -gt 5 -and $file.PsISContainer -ne $True)
       {
       $file.Delete()
       }
}



Admin: Elevate Privileges with TRUSTWORTHY

The database scoped TRUSTWORTHY flag was introduced in SQL 2005 and . Its only a guess, but I dare say that its often turned on as its the path of least resistance without understanding its consequences. For example, when deploying CLR assemblies which require EXTERNAL_ACCESS its far easier to just set the TRUSTWORTHY flag than create an asymetric key and login specifically for that routine - this is something i'm often guilty of. In many scenarios, its probably not an issue as the databases do not hold sensitive data and/or all the databases on the server are managed by the same people so having access across them doesn't pose any threat.

There isn't actually a stack load of information out there on this, so I thought i'd demonstrate how the flag can allow users access to databases that perhaps they shouldn't. Essentially, if the login which is the owner of the database (with the trustworthy flag) has permissions in other databases, then a different user in the database can create modules which access other databases by using the EXECUTE AS OWNER clause.

Look at this example:
NB: My example uses AdventureWorks but you can use any user database and modify the queries slightly

USE [master]
GO
-- create a sysadmin login
CREATE LOGIN MySYSAdmin
WITH PASSWORD = '123';
GO
EXEC sp_addsrvrolemember 'MySYSAdmin', 'sysadmin';
GO
-- impersonate that login to create the database
-- and therefore be the owner
EXECUTE AS LOGIN = 'MySYSAdmin';
GO
CREATE DATABASE TrustworthyTest
GO
USE [master]
GO
-- now create a test user who has full privileges
-- within that database
CREATE LOGIN MyTrustworthyTestDBOwner
WITH PASSWORD = '456'
GO
USE TrustworthyTest
GO
CREATE USER [MyTrustworthyTestDBOwner]
FOR LOGIN [MyTrustworthyTestDBOwner]
GO
EXEC sp_addrolemember 'db_owner', 'MyTrustworthyTestDBOwner'
GO
USE [master]
GO
-- drop out of the sysadmin context
-- and lets impersonate our new user
REVERT
GO
USE [TrustworthyTest]
GO
EXECUTE AS LOGIN = 'MyTrustworthyTestDBOwner'
GO
-- can we select from another database? No!!
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
-- Msg 916, Level 14, State 1, Line 1
-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- how about in a stored procedure?
CREATE PROCEDURE dbo.FetchFromAdventureWorks
WITH EXECUTE AS CALLER
AS
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
EXEC dbo.FetchFromAdventureWorks
GO
-- Msg 916, Level 14, State 1, Procedure FetchFromAdventureWorks, Line 4
-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- ok, lets revert back and reimpersonate our
-- sysadmin and make the database trustworthy
REVERT
GO
EXECUTE AS LOGIN = 'MySYSAdmin';
GO
ALTER DATABASE TrustworthyTest
SET TRUSTWORTHY ON
GO
REVERT
GO
-- and again impersonate the user
EXECUTE AS LOGIN = 'MyTrustworthyTestDBOwner'
GO
-- we still can't access the other database
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
-- Msg 916, Level 14, State 1, Line 2
-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- nor through the stored procedure
EXEC dbo.FetchFromAdventureWorks
GO
-- Msg 916, Level 14, State 1, Procedure FetchFromAdventureWorks, Line 4
-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.


-- BUT if we change the procedure to execute
-- in a different context. WE CAN!!!
ALTER PROCEDURE dbo.FetchFromAdventureWorks
WITH EXECUTE AS OWNER
AS
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
EXEC dbo.FetchFromAdventureWorks
GO

As I said, for many users of SQL Server having access across databases is not that big a deal and using the TRUSTWORTHY flag is preferable to creating keys and logins for CLR assemblies but its still important that you understand this behaviour.

Admin: Useful Resources

Some fantastic tutorials here demonstrating some of the common DBA tasks.

http://sqlserverpedia.com/wiki/SQL_Server_Tutorials

Admin: Data Compression Storage Savings

Data compression was introduced in SQL2008 (though sadly just in Enterprise/Developer Editions) and recently I was asked what sort of benefits you can get from it. So I thought i'd jot down a quick example to illustrate the reductions in space the different types of compression give you and also, an unexpected behaviour you get from using the REBUILD command.

Here i'm only speaking about storage savings. As with most things, there is a cloud to this silver lining although just how gloomy the cloud is will be the subject of a different post. Essentially though, compression gains disk space and IO read performance at the cost of CPU. Anyway, on to the example:

-- create the object
CREATE TABLE [dbo].[tblOutput](
   [Output_ID] [int] NULL,
   [T1] [int] NULL,
   [T2] [int] NULL,
   [Value] [varchar](255) NULL,
   [Run_ID] [int] NULL,
   [Date_Created] [datetime] NULL
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [IDX_tblOutput] ON [dbo].[tblOutput]
(
   [Output_ID] ASC
) ON [PRIMARY]
GO

-- I populate this table with a 3rd party application which throws in plenty of rows and "random" values.

-- lets take a look at how much space this takes
EXEC sp_spaceused 'tblOutput'
GO

/*
name      rows        reserved    data        index_size  unused
--------- ----------- ----------  ----------  ----------  -------
tblOutput 19240000    1666896 KB  1025216 KB  641440 KB   240 KB
*/

-- and look into the physical stats DMV
SELECT page_count, record_count, compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('tblOutput'), NULL, NULL, 'DETAILED')
WHERE index_id = 0

/*

NB: WOW CHECK OUT THAT FRAGMENTATION ON THE NONCLUSTERED INDEX!!

index_type_desc     avg_page_space_   avg_fragmentation                            compressed_
                    used_in_percent   _in_percent        page_count  record_count  page_count
------------------- ----------------- -----------------  ----------- ------------  ------------
HEAP                99.8007659995058  6.26560159760359   128152      19240000      0
NONCLUSTERED INDEX  53.5752656288609  99.4513134653689   79827       19240000      0
NONCLUSTERED INDEX  68.7832592043489  100                344         79827         0
NONCLUSTERED INDEX  50.9574993822585  100                2           344           0
NONCLUSTERED INDEX  0.531257721769212 0                  1           2             0

*/

-- first lets try ROW compression
ALTER TABLE tblOutput
REBUILD
   WITH (DATA_COMPRESSION = ROW)
GO

/*
name      rows        reserved    data        index_size  unused
--------- ----------- ----------  ----------  ----------  -------
tblOutput 19240000    1153064 KB  808888 KB   343896 KB   280 KB
*/

/*

NB: THE NONCLUSTERED INDEX HAS BEEN DEFRAGMENTED!! WASN'T EXPECTING THAT!

index_type_desc     avg_page_space_   avg_fragmentation                           compressed_
                    used_in_percent   _in_percent        page_count record_count  page_count
------------------------------------  -----------------  ---------- ------------  -----------
HEAP                99.7074623177662  0.126572264852464  101112     19240000      0
NONCLUSTERED INDEX  99.8241907585866  0.01               42852      19240000      0
NONCLUSTERED INDEX  99.2432666172473  2.34375            128        42852         0
NONCLUSTERED INDEX  37.929330368174   0                  1          128           0
*/

-- now lets enable some PAGE compression (remember, this includes ROW compression)
ALTER TABLE tblOutput
REBUILD
   WITH (DATA_COMPRESSION = PAGE)
GO

/*
name      rows        reserved    data        index_size  unused
--------- ----------- ----------  ----------  ----------  -------
tblOutput 19240000    904104 KB   559920 KB   343896 KB   288 KB
*/

/*
index_type_desc     avg_page_space_   avg_fragmentation                           compressed_
                    used_in_percent   _in_percent        page_count record_count  page_count
------------------  ----------------  -----------------  ---------- ------------  -----------
HEAP                99.8160612799605  0.136798905608755  70149      19240000      70145
NONCLUSTERED INDEX  99.8241907585866  0.01               42852      19240000      0
NONCLUSTERED INDEX  99.2432666172473  2.34375            128        42852         0
NONCLUSTERED INDEX  37.929330368174   0                  1          128           0
*/

-- and just revert the compression on the table
ALTER TABLE tblOutput
REBUILD
   WITH (DATA_COMPRESSION = NONE)
GO

/*
name      rows        reserved    data        index_size  unused
--------- ------------ ----------  ----------  ----------  -------
tblOutput 19240000    1369384 KB  1025232 KB  343896 KB   256 KB
*/

/*
index_type_desc     avg_page_space_   avg_fragmentation                           compressed_
                    used_in_percent   _in_percent        page_count record_count  page_count
------------------  ----------------  -----------------  ---------- ------------  -----------
HEAP                99.7992092908327  0.118586942953439  128154     19240000      0
NONCLUSTERED INDEX  99.8241907585866  0.01               42852      19240000      0
NONCLUSTERED INDEX  99.2432666172473  3.125              128        42852         0
NONCLUSTERED INDEX  37.929330368174   0                  1          128           0
*/


You can view the documentation on the ALTER TABLE REBUILD command here but doesn't mention that nonclustered indexes will be rebuilt as a result of a REBUILD command.

Admin: how much data do/will you have?

Its important to understand your storage requirements for your database. In many applications i've worked with, the bulk of data is stored in one table and its this table which is most sensitive to data load.

So with that in mind, you need to be able to predict just how large your table(s) is likely to get. If you've "inherited" a database, you can use existing data to make current predictions and harness some of the procedures and DMVs that SQL Server makes available. If you're in the situation of designing a database from scratch, you can make an informed guess by using the alrogithm posted on MSDN.

Existing Database

So how can you guage the size of a database and the objects within it? Lets look at the things at your disposal which give varying degress of accuracy:

1) The last backup size - this will be for the entire database and also include the data in your transaction log
2) The size of the mdf (+ndfs) - this won't tell you exactly how much data you have as the file may have been set to a certain size and not be "full" of data.
3) Right click the database in SSMS (or run DBCC SHOWFILESTATS) - this gives you the total extents used in the database from which you can get a ball park figure of how much space is required (An Extent is 8 x 64KB pages) but you'll need to rememeber that not all pages/extents will be "full" of data
4) EXEC sp_spaceused - this gives can be run for the entire database or just a single table
5) Query sys.partitions and sys.allocation_units to see how many pages an object is using

Empty Database

This article provides a method of estimating the space required in a table:
http://msdn.microsoft.com/en-us/library/ms175991.aspx
The articles give some caveats as to why this is only an estimate and in my analysis, I got a difference of approximately 10% more in actual size of data in a table compared to the estimate.

Example:

CREATE TABLE [dbo].[tblOutput](
   [Output_ID] INT NULL,
   [T1] INT NULL,
   [T2] INT NULL,
   [Value] VARCHAR(255) NULL,
   [Run_ID] INT NULL,
   [Date_Created] DATETIME NULL
) ON [PRIMARY]
GO
-- 19240000 rows in table
-- ESTIMATED SIZE INFORMATION
-- 995820 kb simple estimate based upon #rows / rowsize in kb (not accurate, assumes pages are full)
-- 1012624 kb based upon MSDN algorithm to estimate data

-- ACTUAL SIZE INFORMATION
DBCC SHOWCONTIG('tblOutput')
-- 19678 Total Extents for the table DBCC SHOWCONTIG
-- 157392 Total Pages for the table DBCC SHOWCONTIG
EXEC sp_spaceused 'tblOutput'
-- 1905488 kb (including indexes)
-- 1259136 kb (just data)
DBCC SHOWFILESTATS
-- 1907648 kb based upon 29807 Extents (DBCC SHOWFILESTATS)
SELECT *
FROM sys.allocation_units u
   INNER JOIN sys.partitions p
       ON p.partition_id = u.container_id
WHERE OBJECT_NAME(p.OBJECT_ID) = 'tblOutput'
-- used 157393 pages ~ 1259144 kb
SELECT page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('tblOutput'), NULL, NULL, 'DETAILED')
-- 157393 pages, 19240000 rows


You can see that although quite minor, depending where you look for your information you get differing results but for me, the differences are such that I wouldn't be too concerned on which method was used when it comes to predicting data growth. There are so many factors that can be introduced (notably indexes) that its questionable just how valuable these figures are in isolation.

As for which one to use, I suppose it makes sense to use sp_spaceused as this is likely to be maintained by MS as the truth on storage - or they will deprecate it and replace it.

Powershell: use it or lose it

Powershell has been around a good few years now and I've heard so much good stuff about it but i'm still finding it difficult to "get into". As i've said before, my favoured method of learning something is to use it in anger but I find that I go through patches of using it and then just naturally drift away. Use it or lose it, is the phrase that springs to mind.

Perhaps this is because at the moment, I don't have a huge server farm to deal with and so maintenance tasks aren't onerous and so its quite tough to force yourself to use a technology if you don't need to.

In saying that, I recognise how its a powerful language and could make many tasks (eg reading error logs) much more straight forward IF you need to do those tasks on multiple servers frequently i.e Powershell helps you benefit from economies of scale.

This year, i'm going to try and make a concerted effort to "learn" powershell and add this string to my bow as I believe its going to be a must have for any database professional in the near future.

The SQL Server Stack - too tall to see the top?

I was chatting with my old boss the other day and the conversation moved on to what versions of software they were running and I asked if they were test driving the next version of SQL Server, Denali.

Now, the company in question are definitely a forward looking firm who look to stay as current as possible - I remember when I was there we were one of the very early adopters of SQL Server 2005. Back then, the move from 2000 -> 2005 was a huge leap and the first major release in some time from MS and was a serious upgrade with many changes. We benefited from major performance and reliability improvements yet we didn't change our codebase significantly. And as it turns out, even to this day the codebase is very similar. When i asked, "do you think (a scaled down) version of your app would run on SQL2000?" the answer was "probably". Indeed, when pushed the only features that seemed to have been embraced were CTEs and a prototype using the spatial data types. Not much in 10 years!

Now we have the first CTP of Denali and yet more features are being bundled into the SQL Server Stack - SSIS improvements, T-SQL enhancements etc. I'm not saying these aren't necessary or what the market demands and its evidence that MS are not resting their laurels, but I do wonder how many of these features are ever used in production environments. SSIS has been a huge success but that was (takes deep breath) essentially just an overhaul of DTS. Not a new feature, just an upgrade of existing technology. We've seen Extended Events, Resource Governor, Change Data Capture and Policy Management which are undoubtedly impressive features but how many people take the transition from Demo/Prototype to adding it in a production environment. It seems to me that its the softer, less revolutionary features that have been the most successful. T-SQL enhancements such as the CTE
and TRY/CATCH that have had the most impact.

Perhaps all this is just a reflection on my recent roles which have tended to be much more focused on Developer activites rather than DBA work? The SQL Server product is now so impressive and vast being much more than just a database engine with SSRS/SSIS/SSAS all justifying specialist skills and challenging the database professional ever more. I find that i'm able to learn and understand a feature if i get to use it in anger and this means more than just running through a set of tutorials.

An example is Extended Events. Jonathan Kehayias has put together a brilliant set of blog posts on them which i've been working my way through them and i've been impressed. But I'm in need of some real life scenarios where I can apply this knowledge otherwise it'll just be consigned to the "stuff i know, but don't know" bin.

With the new year, i'm really keen to get my head round many of the new features of Denali. The difficulty is getting the balancing act between being spread too thinly. In an effort to cover the entire SQL Server Stack you risk diluting your knowledge by not knowing enough. In focussing too much on one area, the risk is that you miss out on exciting (and more importantly) valuable features.

Admin: Analysis of Deprecated Features

I recently came across the perfmon counter introduced in SQL2008 of SQLServer:Deprecated Features. It basically tracks executions of deprecated features on a given database server which is invaluable if you are trying to find potentially breaking changes in a huge database - particularly if that database has grown organically across several versions of SQL.

There is also the list provided in Books Online, but i think a real live analysis of your database is much more powerful.

Using the DMV sys.dm_os_performance_counters, you can run a query similar to:
SELECT * FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Deprecated Features'

And this will give you the values for any deprecated features you may need to weed out. Further explanations of the exact changes can be found in the earlier article.

Admin: Force Encryption Flag

I've been looking to brush up on some of the security capabilities in recent version of SQL Server and I found myself playing with the encryption features for connecting to SQL Server.

I was getting a touch frustrated and confused at being unable to force SQL Server to deny my connection when using the Force Protocol Encryption to be true on the client. I'd opened SQL Server Configuration Manager (SSCM), right clicked the SQL Native Client and set the flag to be Yes. I was hoping to be see a connection refused error when connection to my remote server which had been started with a self signed certificate. However, I was able to connect successfully through SQL Server Management Studio (SSMS) and checking sys.dm_exec_connections showed that my connection was being made without encryption. To further muddy the waters, when forcing the encryption through SSMS using the options in the connection dialog box, i got an error i was hoping for:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

The problem (some people will be thinking, its obvious!!) was that I was testing using SQL Server Management Studio and as SSMS is written in .Net it doesn't use the SNAC to connect to SQL Server at all and uses the more snazzy .NetSqlClient. This explains why changing the properties on the SNAC in SSCM doesn't affect connections made through SSMS.

A method of checking that the flag does force an encrypted connection (it does!) is to use the Import/Export wizard (Start/Run/DTSWizard.exe) to connect which appears to use the SNAC and you'll an error message similar to the following:

SSL Provider: The certificate chain was issued by an authority that is not trusted. (Microsoft SQL Server Native Client 10.0)