3

DBCC CLONEDATABASE in SQL Server 2014

In SQL Server 2014 SP2 an interesting new DBCC command was included, DBCC CLONEDATABASE

This command creates a “clone” of a specified user (not supported for the system databases) database that contains all objects and statistics of the specified database. Hmm, could be useful, but, how does it work? Let’s have a look.

First create a database: –

USE [master];
GO

CREATE DATABASE [Test];
GO

And then create a test table: –

USE [TEST];
GO

CREATE TABLE dbo.TestTable 
(PK_ID	   INT IDENTITY(1,1),
 ColA	   VARCHAR(10),
 ColB	   VARCHAR(10),
 ColC	   VARCHAR(10),
 CreatedDate DATE,
 CONSTRAINT [PK_ID] PRIMARY KEY (PK_ID));
GO

Insert some data and then make sure stats have been generated: –

INSERT INTO dbo.TestTable
(ColA,ColB,ColC,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('B',10),REPLICATE('C',10),GETUTCDATE());
GO 100000

EXEC sp_updatestats;
GO

Now we can run the DBCC CLONEDATABASE command: –

DBCC CLONEDATABASE ('test','testclone');
GO

And verify that a read only copy of the database has been generated: –

dbcc clonedatabase

So, let’s have a look at the data in the new database: –

SELECT TOP 1000 [PK_ID]
      ,[ColA]
      ,[ColB]
      ,[ColC]
      ,[CreatedDate]
  FROM [testclone].[dbo].[TestTable];
GO

No data! Ok, so let’s have a look at the stats: –

USE [testclone];
GO

EXEC sp_spaceused 'dbo.testtable';
GO


DBCC SHOW_STATISTICS(N'testtable',PK_ID);
GO

dbcc clonedatabase stats
There’s the stats, SQL thinks that there’s 1000 rows in the table, pretty cool.

What we’ve ended up with is a read only database with no data but the objects and stats of the target database.

First thing, I’d be doing is backing that clone up and bringing it down to my local instance. Want to see how code will execute against production but don’t want to touch that prod environment? Here’s your answer.

@Microsoft, can we have this for other versions of SQL please?

1

SQL Server 2016 – Perform Volume Maintenance

One of the server settings that I always enable when configuring a new instance of SQL is database instant file initialisation

In case you don’t know what this is, it is a setting that can be granted to the service account that the SQL database engine runs under that prevents SQL Server from “zeroing out” new space when a data file is created or expanded.

This improves the performance of CREATE/ALTER DATABASE statements, RESTORE statements and AUTOGROWTH operations. A full detailed article to how and why can be found here.

It’s cool to see that you can now enable this when installing SQL Server: –

SQL2016 Perform Volume Maintenance

I can’t think of any downside to having this enabled (off the top of my head, there’s probably one or two) and it’s good to see that Microsoft know that most people enable it so adding it as an option in the installer is great imho.

1

Bug fixes in SQL Server

SQL Server 2016 CU1 has been released and one thing I noticed was: –

FIX: Canceling a backup task crashes SQL Server 2014 or 2016

That’s pretty nasty, when I originally clicked on the link I was expecting to see detailed a pretty precise set of circumstances in which that bug can occur but no no, apparently not. Cancelling any backup task can lead to this happening.

This lead me to thinking about going to RTM versions of SQL when they’re released, there’s always a debate of whether to wait for the first service pack or not. Often people (like myself) are keen to upgrade to take advantage of new features that are available and will push for the upgrade.

Actually I tell a lie, I’ve always installed new versions of SQL Server on my laptop as soon as they’re available but I’ve been firmly in the “wait ’til the first service pack is released before deploying to production” brigade since SQL Server 2012.

The reason for this is that in a previous position, when SQL Server 2012 was released the devs were very keen to implement it immediately for a new project. I did halfheartedly object but in the end it was deployed (I even remember someone telling me, “yeah but it’s been in CTP for aaaaaages, they’ll have picked up most of the bugs”, ha ha ha).

Well, we had no end of problems and it wasn’t until SP2 was released and we patched our instance that things improved. KBs like this one really put me against using RTM versions and stuff like this made me even more cautious.

So when Microsoft announced that they were changing their patching policy and recommending that CUs should be proactively installed as they are released, I wasn’t particularly happy. Should I have been? Surely testing the CUs to the same level as the SPs is a good thing?

Being the pessimist that I am, all I saw with this was just more ways to introduce risk to my systems. If I’m not suffering any issues why would I install all CUs released instead of waiting for the SP?

And to be honest that’s what I’m going to do, unless I’m seeing a specific issue I’m going to wait for the SP and not bother with the CUs. Unless, and I’ve seen this mentioned, Microsoft do away with the SPs and just release CUs periodically.

Then I guess it’ll be a matter of installing on my dev/staging systems and test, test, test.

0

The new SQL Server Management Studio

Ok, so this is old news I know, but I’ve had a busy couple of months and am only getting around to writing this now. SSMS is now a separate release from SQL itself. The latest release is here.

Monthly releases and no longer having to download GBs to get a client?

SSMS Updates

Cool!

Has anyone else noticed this?

SSMS Themes

Microsoft! Give us a dark theme, come on! I’ve always been enviously of those Devs with their dark themed Visual Studio.

I am a little annoyed about one thing however (please try to hide your shock). Management Studio since April uses a different add-in model which means all the old plugins that I had no longer worked.

Thankfully it seems that vendors have been working hard and there are a number of plugins out there now that support the newest version of management studio. A couple of my favourites are:-

SQL Sentry’s Plan Explorer (who doesn’t use this)

ApexSQL Refactor (the best of the free formatters that I could find)

Another couple of cool developments in the community are:-

The SQL Server Management Studio Enhancements Trello Board

This allows people who use SSMS to suggest ideas (bug fixes?) to Microsoft and if a “card” gets enough votes, it’ll become a Connect item. Nice to see but let’s watch that space and see what happens (says the cynic in me).

The SQL Community on Slack

I have mixed feelings towards Slack if I’m honest. On one hand it is a really good collaboration tool for teams and one the other, it’s another way for me to be interrupted at work. I now have email, Skype, Skype for frickin’ business and now Slack…grrr
Anyway, at the very least it’s going to allow me to keep in touch with other members of the SQL community out there.

Thanks for reading!

0

Setting up powershell remote sessions

Hey guys, differing from usual this is a quick post on setting up powershell remote sessions. I know you can remotely connect to powershell sessions using the Server Manager that comes with Windows Remote Administration Tools but it’s a bit of a clicky process and I like to eliminate using the mouse as much as possible.

Disclaimer! I’m not a scripter, there are probably much better ways of doing this but I’ll show you the way I set it up and how to fix any errors you may come across.

So here goes, first thing to do is check that the Windows Remote Management service (WinRM) is running, so open a powershell session (as an admin) and run:-

Get-Service WinRM

If it’s not running, start the service:-

Start-Service WinRM

And now enable powershell remoting:-

Enable-PSRemoting -force

Next thing to do is check your trusted hosts lists, so run:-

cd wsman::localhost
cd Client
dir

N.B. – I’m going to the Client via two cd commands as going straight to wsman::localhost\Client sometimes generates an error (not sure why TBH). If you get errors, try each command separately.

If the host you want to remote to isn’t in the list, run the following (changing the RemoteComputer1,RemoteComputer2 to your servers): –

winrm s winrm/config/client '@{TrustedHosts="RemoteComputer1,RemoteComputer2..."}'

Btw, if you see this error:-
WinRM Error

Restart the WinRM service: –

Stop-Service WinRm
Start-Service WinRm

Cool, so now we can connect to a remote powershell session on the servers entered into the list. Save this simple script somewhere: –

Param (
    [string]$Server,
    [string]$Credential
      )

Enter-PSSession -ComputerName $Server -Credential $Credential

Which can be called by a .BAT file, passing in the parameters:-

powershell -NoExit -ExecutionPolicy Bypass -File "PATH TO THE PS1 File" -Server YOURSERVER -Credential YOURLOGIN

N.B. – Use the -Credential parameter if your server is in a different domain (like a Staging or QA environment).

This .BAT file can be called via a cool program like Launchy and voila, a remote powershell session!

0

SQL 2016 – Dynamic Data Masking

One of the new features that’s coming with SQL Server 2016 is Dynamic Data Masking. DDM is designed to allow DBAs to limit exposure of sensitive data within a database with minimal (if any) changes required to existing queries.

How often have you seen a column in a database…in plain text…called password?? ARGH.

I really like the idea of being able to mask data without having to change any existing queries in the database. But how does it work? Well, SQL doesn’t do anything to the data itself but when a select statement retrieves data a “masking” function is applied to the columns (I won’t blame you if you just rolled your eyes and said “well…duh” when reading that last statement). These functions can be:-

Default: Changes column values to xxxx

MASKED WITH (FUNCTION = 'default()'))

Email: Changes email address to XXXX@XXXX.com

MASKED WITH (FUNCTION = 'email()')

Random: Replaces numeric data with range of values specified. Example shown will replace data with values from 1 to 100.

MASKED WITH (FUNCTION = 'random(1,100)')

Custom: A user defined type that can mask characters specified. Example shown masks all characters apart from the first and last with XXX.

MASKED WITH (FUNCTION = 'partial(1,"XXX",1)')

These functions can be applied when the table is created or added afterwards by running a ALTER TABLE statement. But enough of me explaining, let’s create a database for testing: –

USE [master];
GO

CREATE DATABASE [DDM_Demo];
GO

Now we can create a table, specifying the masking function inline: –

USE [DDM_Demo];
GO

CREATE TABLE dbo.Users
(UserID			INT IDENTITY(1,1) PRIMARY KEY,
 Forename		VARCHAR(20),
 Surname		VARCHAR(20),
 Username		VARCHAR(20),
 Email			VARCHAR(50) MASKED WITH (FUNCTION = 'email()'),
 AccountPassword	VARCHAR(50) MASKED WITH (FUNCTION = 'default()'));
GO

This will create a pretty simple table with the columns Email and AccountPassword masked with the email and default functions respectively. To see how they work insert some test data: –

INSERT INTO dbo.Users
(Forename,Surname,Username,Email,AccountPassword)
VALUES
('Andrew','Pruski','APruski','dbafromthecold@gmail.com','123456ABCDE');
GO

But I’m guessing your a sysadmin in your test instance (if you’re not, what’s going on there?) so in order to see the data masking in action, we need a new login & user: –

CREATE LOGIN [DDM_Login] WITH PASSWORD = 'testing', CHECK_POLICY=OFF,CHECK_EXPIRATION=OFF;
CREATE USER [DDM_User] FOR LOGIN [DDM_Login];
ALTER ROLE [db_datareader] ADD MEMBER [DDM_User];
GO

Now run a SELECT statement with under the new account’s credentials: –

EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

ddm1

Cool! The data has been masked to the user. If we want this user to be able to see the data, we can run: –

GRANT UNMASK TO DDM_User;
GO

Now re-run the SELECT statement: –

EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users;
REVERT;
GO

ddm2

And now the user can see the data. To remove the access: –

REVOKE UNMASK TO [DDM_User];
GO

To add a new column with masking (this time using the custom function): –

ALTER TABLE dbo.Users
ALTER COLUMN Surname ADD MASKED WITH (FUNCTION = 'partial(1,"XXX",1)');
GO
EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

ddm3

The column has now been masked with a custom function. To remove the data masking function from the column, run: –

ALTER TABLE dbo.Users
ALTER COLUMN Surname DROP MASKED;
GO

N.B. – As far as I can tell, adding/dropping the data masking function is a meta-data only operation (if anyone knows otherwise, let me know!).

Obviously there are some limitations around columns that can have masking applied. Masking cannot be applied to: –

  • Columns already encrypted
  • Filestream
  • Sparse columns
  • Computed columns

There is a caveat with computed columns however, if a computed column depends on a masked column then the computed column WILL be masked. Also, masked columns cannot be part of a key for a full text index.

A final word of caution with this feature…THIS IS NOT ENCRYPTION! It is exactly what it says it is, a mask. This can be seen by having a look at the data on the page: –

DBCC IND('DDM_Demo','Users',1);
GO

DBCC TRACEON(3604);
GO
DBCC PAGE ('DDM_Demo',1,232,3);
GO

N.B. – For more info on DBCC IND & DBCC PAGE go here:- http://strictlysql.blogspot.ie/2010/08/dbcc-ind-dbcc-page-intro.html

ddm4

The data can be seen in plain text on disk, the masking function only comes into play when reading the data. I’ve been playing around with this and, I can’t see any huge performance impact incurred when having to mask the data. SQL does report a extra operator when reading the data: –

GRANT SHOWPLAN TO DDM_User;
GO

EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

ddm5

That COMPUTE SCALAR operator isn’t there usually when running a bog standard select against a table but I’ve tried inserting 1,000,000 rows into the table, selecting from it, then dropping the masking, re-running the select and comparing the performance statistics but cannot see any (significant) impact. Here’s the code I used to test (have a go at running it yourself): –

--Switching off performance metrics for the inserts
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SET NOCOUNT ON;
GO

--Inserting a load of rows to the table
--I know this is horrible code!!!!!!!!!!!!!!!!!!!!!!!!!
DECLARE @Counter INT = 0;

WHILE @Counter <= 1000000
BEGIN

    INSERT INTO dbo.Users
    (Forename,Surname,Username,Email,AccountPassword)
    VALUES
    ('TestForename' + CONVERT(SYSNAME,@Counter),
     'TestSurname'  + CONVERT(SYSNAME,@Counter),
     'TestUsername' + CONVERT(SYSNAME,@Counter),
	'testemail'    + CONVERT(SYSNAME,@Counter) + '@gmail.com',
	'TestPassword' + CONVERT(SYSNAME,@Counter));
    
    SET @Counter = @Counter + 1
END


--Allowing the user to see the execution plan
GRANT SHOWPLAN TO DDM_User;
GO

--Cleaning out the data from the buffer pool
DBCC DROPCLEANBUFFERS;
GO

--Running the initial select
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

--Dropping the masking
ALTER TABLE dbo.Users
ALTER COLUMN Email DROP MASKED
ALTER TABLE dbo.Users
ALTER COLUMN AccountPassword DROP MASKED;
GO

--Cleaning out the data from the buffer pool (again)
DBCC DROPCLEANBUFFERS;
GO

--Re-running the SELECT without the masking
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXECUTE AS USER = 'DDM_User';
SELECT * FROM dbo.Users ;
REVERT;
GO

I have seen a minor increase in execution times but nothing that makes me worried. I am aware that this is a very simple test so any “gremlins” may not be apparent so as ever with a new feature, if you’re going to implement it, get it on a test system and hammer the thing!

Thanks for reading.

0

Tools of the trade

I’ve been meaning to write this post for a while now, nice and simple, just want to list the tools that I use on a day-to-day basis and hopefully a couple of them may be new to someone out there. Btw – if anyone uses anything else regularly and thinks I should be, let me know!

So first off has to be SQL Server Management Studio. There are other applications out there that can be used to administer/query SQL Server but I’m yet to find one that beats SSMS. Sure, it has it’s problems but for ease of use and the sheer amount of plugins available, I can’t see myself ever using anything else (thankfully it’s now available separately from SQL Server itself, how long did it take Microsoft to do that??).

Here’s the plugins that I have (they’re all free ‘cos I’m cheap): –

SQLSentry Plan Explorer
Everyone should have this installed, if you don’t…go install it now. Seriously, do it now.

RedGate SQL Search
Great tool for finding objects in your SQL instances. Don’t use it that much but it’s awesome to have already there when needed.

SSMSBoost
To be honest, I only ever use the the option to format SQL on a regular basis and if you only want that you could be better off downloading the Poor Man’s T-SQL Formatter. However the option to restore previously closed tabs has been a lifesaver in the past.

Here’s some of the other tools I use on a daily basis (again all free, did I mention that I’m cheap?): –

Edit – BeyondCompare isn’t free, you have a 30 day free trial and then it’s $30 (well worth it)

Terminals
Good RDP tool, I admit that I haven’t really tried any others as this does exactly what I need

Notepad++
I don’t think I need to say anything about this. I’ve tried other text editors (atom is pretty cool) but nothing imho comes close to this.

Launchy
I love this tool. I really love this tool. If you’re not using it, go and download it now. Yes it’s an application launcher it’s also much more. I use it as a app launcher but also to search for files/folders and run .bat scripts that perform maintenance tasks on my laptop. Very cool, go and download it.

QDir
For moving files around, I can’t recommend this enough. Loads of options, easy to use and navigate. There’s hundreds of multi-pane file explorers out there but I’ve found that this one has the best user experience (not that I’ve tried all of them mind).

BeyondCompare
If you have to compare scripts, this is a life saver. I often have to review changes being made to stored procedures this tool easily allows me to see the differences between the existing and new code. This has saved me hundreds of hours (not exaggerating).

Thanks for reading!

1

Update to TRUNCATE TABLE in SQL 2016 (partition support)

Continuing my obsession with partitioning I thought I’d write this quick post about a cool change in SQL Server 2016.

This change allows you to truncate an individual partition, instead of having to switch that partition out to another table and then truncate it. Full details can be found here: – https://msdn.microsoft.com/en-us/library/ms177570.aspx

Here’s a demo, initial setup to create a database, partition function & scheme and then a table: –

CREATE DATABASE [PartitioningDemo]
 ON PRIMARY
(NAME = N'PartitionDemo', FILENAME = N'C:\SQLServer\SQLData\PartitionDemo.mdf', SIZE = 51200KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), 
 FILEGROUP [DATA] 
(NAME = N'DATA', FILENAME = N'C:\SQLServer\SQLData\DATA.ndf', SIZE = 51200KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [ARCHIVE] 
(NAME = N'ARCHIVE', FILENAME = N'C:\SQLServer\SQLData\ARCHIVE.NDF', SIZE = 51200KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
(NAME = N'PartitionDemo_log', FILENAME = N'C:\SQLServer\SQLLog\PartitionDemo_log.ldf', SIZE = 20480KB, MAXSIZE = 2048GB, FILEGROWTH = 512KB)
GO

USE [PartitioningDemo];
GO

CREATE PARTITION FUNCTION PF_PartitionedTable(DATE)
       AS RANGE RIGHT
    FOR VALUES ('2014-01-01','2015-01-01','2016-01-01');

 
CREATE PARTITION SCHEME PS_PartitionedTable
    AS PARTITION PF_PartitionedTable
TO ([ARCHIVE],[ARCHIVE],[DATA],[DATA]);


CREATE TABLE dbo.PartitionedTable
(PKID INT IDENTITY(1,1),
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 CreatedDate DATE)
 ON PS_PartitionedTable(CreatedDate);
 
CREATE UNIQUE CLUSTERED INDEX [IX_CreatedDate_PartitionedTable] ON dbo.PartitionedTable
 (CreatedDate,PKID) 
ON PS_PartitionedTable(CreatedDate);
GO

Now inserting some data into that table: –

SET NOCOUNT ON;
INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2013-02-01');
GO 1000
 
INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2014-02-01');
GO 1000
 
INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2015-02-01');
GO 1000
 
INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2016-02-01');
GO 1000

Check the data in the partitions: –

SELECT
    t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, 
    --i.data_space_id, f.function_id, f.type_desc, 
    fg.name AS [filegroup], 
    r.boundary_id, r.value AS BoundaryValue, p.rows
    --,r.*
FROM
    sys.tables AS t
INNER JOIN
    sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN
    sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
INNER JOIN
    sys.allocation_units a ON a.container_id = p.hobt_id 
INNER JOIN
    sys.filegroups fg ON fg.data_space_id = a.data_space_id 
INNER JOIN
    sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN
    sys.partition_functions AS f ON s.function_id = f.function_id
LEFT OUTER JOIN
    sys.partition_range_values AS r ON f.function_id = r.function_id 
                                    AND r.boundary_id = p.partition_number
WHERE
    t.name = 'PartitionedTable'
AND
    i.type <= 1
AND
    a.type = 1 --in row data only
ORDER BY p.partition_number DESC;

PartitionedTable1

OK, now we can truncate an individual partition by running:-

TRUNCATE TABLE dbo.PartitionedTable WITH (PARTITIONS (1));
GO

Re-checking the data: –
PartitionedTable2

And the data in partition 1 has been removed! We can also remove data from multiple partitions: –

TRUNCATE TABLE dbo.PartitionedTable WITH (PARTITIONS (2,3));
GO

Looking again: –
PartitionedTable3

And the data is gone in both the partitions. Pretty cool! Makes it a lot easier to remove data that has expired and no longer needs to be retained.

Although, it does make it a lot easier to remove data in general…what could go wrong?

(OK, I think that’s enough with the partitioning, need to look at something else…)

0

Partitioning and filegroup restores

I’ve been playing around with partitioning quite a lot recently and wanted to write a quick post about how it can help you out in a DR situation.

Partitioning is mainly for increasing the manageability of your data but it also has other benefits, one of them being giving you the ability to split a single table across multiple filegroups. This will allow you to keep your current data in one filegroup and, let’s call it historical data, in another. In a DR situation, if you need to bring your current data online quickly and worry about the rest later, this can really help you out.

So let’s run through a quick example.First, create a database:-

CREATE DATABASE [PartitioningDemo]
 ON PRIMARY 
(NAME = N'PartitionDemo', FILENAME = N'C:\SQLServer\SQLData\PartitionDemo.mdf', SIZE = 51200KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), 
 FILEGROUP [DATA] 
(NAME = N'DATA', FILENAME = N'C:\SQLServer\SQLData\DATA.ndf', SIZE = 51200KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [ARCHIVE] 
(NAME = N'ARCHIVE', FILENAME = N'C:\SQLServer\SQLData\ARCHIVE.NDF', SIZE = 51200KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
(NAME = N'PartitionDemo_log', FILENAME = N'C:\SQLServer\SQLLog\PartitionDemo_log.ldf', SIZE = 20480KB, MAXSIZE = 2048GB, FILEGROWTH = 512KB)
GO

This database has three filgroups. The PRIMARY (as always), DATA and an ARCHIVE filegroup. What this demo is going to show you is how to bring the PRIMARY and DATA filegroups online first and then bring the ARCHIVE filegroup online afterwards.

So now let’s create a partition scheme and function:-

USE [PartitioningDemo];
GO

CREATE PARTITION FUNCTION PF_PartitionedTable(DATE)
	   AS RANGE RIGHT 
    FOR VALUES ('2014-01-01','2015-01-01','2016-01-01');
GO


CREATE PARTITION SCHEME PS_PartitionedTable
    AS PARTITION PF_PartitionedTable
TO ([ARCHIVE],[ARCHIVE],[DATA],[DATA]);
GO

The table we’re going to build will be partitioned by year, two partitions on the ARCHIVE group and two on the DATA filegroup.

So let’s create the table (and its clustered index): –

CREATE TABLE dbo.PartitionedTable
(PKID INT IDENTITY(1,1),
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 CreatedDate DATE)
 ON PS_PartitionedTable(CreatedDate);


CREATE UNIQUE CLUSTERED INDEX [IX_CreatedDate_PartitionedTable] ON dbo.PartitionedTable
 (CreatedDate,PKID) 
ON PS_PartitionedTable(CreatedDate);
GO

Now insert some data: –

SET NOCOUNT ON;

INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2013-02-01');
GO 1000

INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2014-02-01');
GO 1000

INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2015-02-01');
GO 1000

INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2016-02-01');
GO 1000

Let’s quickly check the data in the partitions:-

SELECT 
	t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, 
	--i.data_space_id, f.function_id, f.type_desc, 
	fg.name AS [filegroup], 
	r.boundary_id, r.value AS BoundaryValue, p.rows
	--,r.*
FROM 
	sys.tables AS t
INNER JOIN
	sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN
	sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
INNER JOIN 
    sys.allocation_units a ON a.container_id = p.hobt_id 
INNER JOIN 
    sys.filegroups fg ON fg.data_space_id = a.data_space_id 
INNER JOIN
	sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN
	sys.partition_functions AS f ON s.function_id = f.function_id
LEFT OUTER JOIN 
	sys.partition_range_values AS r ON f.function_id = r.function_id 
									AND r.boundary_id = p.partition_number
WHERE 
	t.name = 'PartitionedTable'
AND 
	i.type <= 1
AND
    a.type = 1 --in row data only
ORDER BY p.partition_number DESC;

DataInPartitionedTable1

So both filegroups have 2000 rows in them. Now let’s perform a filegroup restore, bringing the PRIMARY & DATA filegroups online first.

Take a full and log backup of the database:-

USE [master];
GO

--FULL DATABASE BACKUP
BACKUP DATABASE [PartitioningDemo]
   TO DISK = 'C:\SQLServer\Backups\PartitioningDemoFullBackup.bak'
   WITH INIT
GO


--LOG BACKUP
BACKUP LOG [PartitioningDemo]
	TO DISK = N'C:\SQLServer\Backups\PartitioningDemoLogBackup.trn'
	WITH NO_TRUNCATE, INIT
GO

OK, now we’re simulating a problem, first take a tail log backup:-

BACKUP LOG [PartitioningDemo]
	TO DISK = N'C:\SQLServer\Backups\PartitioningDemoTailLogBackup.trn'
	WITH INIT, NORECOVERY
GO

And now we’re going to perform a filegroup restore of the PRIMARY and DATA filegroups:-

--PRIMARY filegroup
RESTORE DATABASE [PartitioningDemo] 
   FILEGROUP = 'PRIMARY'
   FROM DISK = 'C:\SQLServer\Backups\PartitioningDemoFullBackup.bak'
   WITH 
   REPLACE, PARTIAL, NORECOVERY;
GO


--DATA filegroup
RESTORE DATABASE [PartitioningDemo] 
   FILEGROUP = 'DATA'
   FROM DISK = 'C:\SQLServer\Backups\PartitioningDemoFullBackup.bak'
   WITH 
   REPLACE, PARTIAL, NORECOVERY;
GO


--Restore transaction log & tail log backups
RESTORE LOG [PartitioningDemo] FROM DISK = N'C:\SQLServer\Backups\PartitioningDemoLogBackup.trn' WITH NORECOVERY;
RESTORE LOG [PartitioningDemo] FROM DISK = N'C:\SQLServer\Backups\PartitioningDemoTailLogBackup.trn' WITH RECOVERY;
GO

Now we can query the table:-

SELECT COUNT(*) FROM [PartitioningDemo].dbo.[PartitionedTable]
WHERE CreatedDate > CONVERT(DATE,'2015-01-01')
GO


--Check access to archive data
SELECT COUNT(*) FROM [PartitioningDemo].dbo.[PartitionedTable]
WHERE CreatedDate < CONVERT(DATE,'2015-01-01')
GO

First query will run fine but the second will generate an error:-
QueryError1

So we still have to restore the ARCHIVE filegroup:-

--Restore ARCHIVE filegroup
RESTORE DATABASE [PartitioningDemo] 
   FILEGROUP = 'ARCHIVE'
   FROM DISK = 'C:\SQLServer\Backups\PartitioningDemoFullBackup.bak'
   WITH 
   NORECOVERY;
GO


--Restore transaction log & tail log backups
RESTORE LOG [PartitioningDemo] FROM DISK = N'C:\SQLServer\Backups\PartitioningDemoLogBackup.trn' WITH NORECOVERY;
RESTORE LOG [PartitioningDemo] FROM DISK = N'C:\SQLServer\Backups\PartitioningDemoTailLogBackup.trn' WITH NORECOVERY;
GO

Bring the database fully online:-

RESTORE DATABASE [PartitioningDemo] WITH RECOVERY;
GO

And re-run the queries against the table:-

SELECT COUNT(*) FROM [PartitioningDemo].dbo.[PartitionedTable]
WHERE CreatedDate > CONVERT(DATE,'2015-01-01')
GO


--Check access to archive data
SELECT COUNT(*) FROM [PartitioningDemo].dbo.[PartitionedTable]
WHERE CreatedDate < CONVERT(DATE,'2015-01-01')
GO

Now both queries will return results:-
QueryResults1

Neat huh? You can see that in a DR situation, if you have a correct partitioning and filegroup strategy in place, you can reduce the amount of time it will take to bring your current data online.

More about filegroup restores: –
https://msdn.microsoft.com/en-ie/library/aa337540.aspx

0

First foray into presenting

It’s been a while since I’ve posted as the run up to Xmas last year and this January have been particularly busy. February doesn’t seem to be going to be any quieter but at least I’m being kept on my toes.

Anyway, waaaay back in December the SQL Server Ireland User Group (website) ran a Xmas “extravaganza” in which they held a series of lightening talks. Presenting is something that I’ve wanted to get into for a while so with a bit of pushing from one of the guys on the panel, I signed myself up. The talk only needed to be 5 minutes long but this was ideal for me considering I’ve never presented before.

The talk I did was on how I implemented partitioning on a set of existing tables within my company’s OLTP databases. First time presenting? Stick with something you know. I also felt pretty safe from awkward questions (or people pointing out how I could have implemented the solution more effectively!) as I wanted the session to be very specific to the environment I was working with and the limitations/restrictions I had to deal with.

So how did I do? Well considering I was quite convinced that I was going to stammer, freeze up and then flee the room (honestly, I thought this WAS what was going to happen)…quite well really. I did have a minor technical glitch…I was duplicating my desktop onto the projected screen and it started reverting back to extend (as I have it in the office), so the screen on the projector went blank. Not a problem at first as this happened when I was setting up so I quickly went into settings and set it back to duplicate.

However, I did this too quickly and forgot to click the “Keep Changes” option so after 10 seconds it reverted and the screen went blank again. So I went back into the settings, fixed it, forgot to click “Keep Changes” and ended up in this loop until one of the other presenters helped me out (thankfully). In the end I managed to laugh it off, continue and ended up winning a bottle of champagne for “Best New Speaker” so I’m not going to obsess about that too much (I promise).

In the end I really enjoyed the whole experience…OK…looking back with rose tinted glasses I enjoyed it, at the time I was too nervous. I’m not going to turn this post into another “Best pieces of advice for first time presenters” (although I can’t promise I won’t do one of those posts in the future) but one thing I will say is know, really know your presentation. Go over it over and over, practice continuing when making mistakes and don’t stick to a rigid script. By this I mean, if you go off track slightly, you’ll be able to make it through without stumbling over or losing track.

So what now? Well I’ve been accepted to present another lightening talk at SQL Saturday Exeter. It’s only 10 minutes I know but it’s double what I’ve done previously and the SQL Saturday events are much bigger. I’ll post about how it went afterwards but for now I’m looking forward to it, until the panic sets in!