Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLBalls

Bradley Ball is a MCITP SQL 2005 & MCTS 2008 DBA with over 10 years of IT experience. Bradley spent 8 years working as a Defense contractor for clients such as the U.S. Army and The Executive Office of the President of the United States. He is currently a Sr. Consultant for Pragmatic Works. He has presented at SQL Saturdays 62, 74, 79, 85, 86, 131, for the MAGICPASS & OPASS SSUG’s, SQL Rally 2011 & 2012, SQL Dev Connections 2012, the PASS Summit in 2011, and is scheduled for the PASS Summit 2012 and SQL Live 360 later this year. He recently finished Chapter 14 of Expert SQL Server Practices on Page & Row Compression and can be found blogging on http://www.sqlballs.com.

Partitioning Deck, Demos, and Recording Live!

Hello Dear Reader!  This week finds me up in Jacksonville at Pragmatic Works HQ.  I'm a little behind in getting the blog up for last week Webinar on Partitioning in SQL Server 2012.  We covered a lot of great things in the webinar and I wanted to recap some of them.  This was the first time I’d given this presentation and over 300 people tuned in to watch!

I’d like to say a quick Thank You to all the people that spent their hard earned time with me.  If you would like to download anything from the presentation Click Here for the Deck, Click Here for the Demo Scripts, and click here to watch the video recording of the presentation.  All of this is also up on my Resource page.

I had a hiccup on the Piecemeal Restore Demo that I did and I wanted to review it.   Unfortunately I was playing around with the script and just to be on the safe side had backed up my data base.  At the header of the script I inserted a restore command.  I got an error running the script and wanted to fit a couple other demos in and skipped over it.  So now we can tackle it.

“So Balls”, you say, “What’s a Piecemeal Restore and WHY would I need to use one?”

Excellent question Dear Reader! The Piecemeal restore was introduced in SQL 2005.  It gave us a sequence of steps we could take in order to recover a portion of a database online at a time starting with the Primary Filegroup.  This allows us to bring critical portions of the database online for quick access.

If you had a very large database with a lot of historical data you wouldn’t want to make the business stay offline in a critical outage while all the historical data is restored.  If you have TB’s of data that could take hours!
A Piecemeal restore gives us the ability to bring a segment of the database online at a time.  A very easy way to demonstrate this is using partitioning.

DEMO TAKE II

Make sure to use 02 Demo_a Set Up demoInternals_Partition.sql to set up our demoInternals_Partition Database in the scripts above.  

First let’s take a look at our table.  We will use sys.partitions and sys.indexes to see how the data is distributed across filegroups.  

This will also let us see the Clustered Index and Non-Clustered Index we created.

SELECT
     OBJECT_NAME(sp.object_id) AS tableName,
     si.name AS indexName,
     sp.partition_number,
     sp.rows
FROM
     sys.partitions sp
     JOIN sys.indexes si
     ON si.object_id=sp.object_id AND si.index_id =sp.index_id
WHERE OBJECT_NAME(sp.object_id) ='myTable1';



You can see from the count we have 18,000 rows in our table. Now that we’ve looked at our data the next thing I need to do is backup my database.  I’m going to perform 3 types of backups.  First a Full backup, secondly I’m going to perform a Log Backup, third we’ll perform a tail of the log backup and leave our database in a restoring state. 

*NOTE* You can use a Piecemeal restore with all recovery models however I’m running in Full recovery for the sake of the demos today.

USE master
go
BACKUP DATABASE demoInternals_Partition TODISK=N'C:\Backups\demoInternals_Partition2.bak' WITH INIT
GO
BACKUP LOG demoInternals_Partition TODISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH INIT
GO
BACKUP LOG [demoInternals_Partition] TO  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'WITH  NO_TRUNCATE , INIT, NORECOVERY
GO


Looking at our database I can see that the commands have completed and we are in the Restoring state.  Our database is completely inaccessible, I know I know snapshots…. But that’s not the point so stick with me Dear Reader.  First let’s restore our primary data file.

USE master
GO

RESTORE DATABASE demoInternals_Partition FILEGroup='primary'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH PARTIAL, NORECOVERY
GO
RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery


RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO


If I refresh my SSMS Object Explorer Window it looks like the database is back online.  However, I know better.  The only filegroup online is the Primary Filegroup.  I like to keep this filegroup small with only the metadata that is there when the database is created.  If I try to query the table dbo.mytable1 it should fail.  Let’s do that real quick.  We’ll query one of our DMV’s about our table that we cannot acces, let’s say sys.indexes.  Then we’ll do a very simple query against the database to get record 1.  Remember our 1stpartition had 2000 rows in it.

use demoInternals_Partition
go
select
     object_name(si.object_id)
     ,si.name
     ,si.type_desc
     ,si.name
from
     sys.indexes si
where
     object_name(si.object_id)='mytable1'



select
     *
from
     dbo.myTable1
where
     myid=1


As you can see we got results from our DMV, but we couldn’t even access row 1 in our table.  Now let’s bring FG1 online.

use master
go
RESTORE DATABASE demoInternals_Partition FILEGroup='FG1'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery
GO

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Now let’s try our query again.
USE demoInternals_Partition
GO
select
     *
from
     dbo.myTable1
where
     myid=1



Success. We can get all 2000 rows in the FG1 partition. If you want to get the same error as before for FG2, just change the 1 to a 2001.  This is a very flexible process that allows you to assign Business level SLA’s to different segments of your Database.  You do not need to use partitioning to do a piecemeal restore.  You could just use separate FG’s and segment tables by business segment.

Let’s bring online FG2 and FG6, leaving FG3, FG4, and FG5 still offline.
USE master
go
RESTORE DATABASE demoInternals_Partition FILEGroup='FG2'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE DATABASE demoInternals_Partition FILEGroup='FG6'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Now’ let’s execute the following queries:
use demoInternals_Partition
go
select * from dbo.mytable1 as FG2 Where FG2.myid=2500
select * from dbo.mytable1 as FG6 Where FG6.myid=12001
go
select * from dbo.mytable1 as FG3 Where FG3.myid=4500
select * from dbo.mytable1 as FG5 Where FG5.myid=8000


The Queries against FG2, and FG 6 Succeed.  The Queries against FG3 and FG5 failed.  Okay now let’s bring all the tables online.

USE master
go
RESTORE DATABASE demoInternals_Partition FILEGroup='FG3'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE DATABASE demoInternals_Partition FILEGroup='FG4'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE DATABASE demoInternals_Partition FILEGroup='FG5'
     FROM DISK='C:\Backups\demoInternals_Partition2.bak'
WITH norecovery

RESTORE LOG demoInternals_Partition  FROM DISK=N'C:\Backups\demoInternals_Partition_log.trn' WITH norecovery

RESTORE LOG [demoInternals_Partition] FROM  DISK = N'C:\Backups\demoInternals_partition_TailofTheLog.trn'WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

WRAP IT UP

We can now query from start to finish with the entire database online.  This is a pretty simplistic demo.  It is meant just to convey the different architectural options that are available for a Piecemeal restore.  As you can imagine this is something that could be utilized in a DR scenario to meet SLA’s and RTO.

This is a very powerful tool in the arsenal of the DBA.  You want to test this, and make sure that it meets your business needs before implementing it.  Don't forget to get the demo's and scripts from the presentation.  

As always Dear Reader, Thanks for stopping by!

Thanks,


Brad

Comments

Leave a comment on the original post [www.sqlballs.com, opens in a new window]

Loading comments...