Tuesday, July 30, 2013

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 TO DISK=N'C:\Backups\demoInternals_Partition2.bak' WITH INIT
GO
BACKUP LOG demoInternals_Partition TO DISK=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 1st partition 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

Friday, July 26, 2013

SQL Server Login Errors

Hello Dear Reader.  Quick blog today.  If you are working in the SQL Server world eventually you will try to connect to a SQL Server and get an error.  A beautiful wonderful descriptive error that reads, pauses… looks around… stands straight…. In my best impersonation of a classic English poet proceeds to read, “Error: 18456, Severity:  14, State: X”.

Ahhh sweet music.  Okay Dear Reader I confess, it’s definitely not Lord Tennyson.  But the important part is the State.  In SQL Server 2005 they did a really great job of giving us more descriptive error descriptions when it came to the State.

SQL STATE OF MIND

When I first discovered this I made a flash card out of it.  I used to quiz myself on it.  Running production, QA, Test, Staging, and Dev DBA support this came in handy.  Whatever your environment when a user calls and says “I can’t log in”, you can use this as a guide.


ERROR STATE
ERROR DESCRIPTION
1
The Error information is not available.  This normally means that the user doesn’t have permissions to view the actual error state.  Check the SQL Server Error Log on the Instance they were trying to connect to.  If you are logging unsuccessful login attempts the real state will be in the error log.
2 and 5
The User ID is not valid.  Check for misspelled user name, or that the login has been created on the server.
6
Attempt to use a Windows login name with SQL Authentication
7
Login disabled and the password does not match what is on record.  A SQL DBA or someone with Administrative Permissions will need to Enable the Login.  If you get this for SA, then you may not have SQL Server Authentication Enabled.  Even after you enable it the SA may be disabled.
8
You typed in the password wrong, make sure you know what it is so you don’t lock it out.  If you do you’ll get error State 7
9
Invalid password
10
This was specific to SQL 2005
-When you try to use a SQL Server authenticated login to log onto the instance. 
-The SQL Server service is configured to use a domain account
-The SQL Authenticated logins that receive the "Logon Error: 18456" error message are configured to use Windows domain password policy enforcement

More details here: http://support.microsoft.com/kb/925744
11
12
Login is valid, but server access failed.  When I have seen this in the past it is because a login is created with specific and restricted permissions.  Instead of setting the default database to the DB in question it is left on a default database that the user cannot access.  Double Check the Default DB for the user.
13
The SQL Server service paused, at this point you need to get the service running.  Any failed login will resolve itself at this point.
16
Occurs when logins do not have access to the target database or the database no longer exists or is offline.
18
Password change is required, and you are probably accessing in such a way that a change password prompt cannot appear
23
SQL Server is shutting down and new incomming connections are attempting to connect.  More Here: http://support.microsoft.com/kb/937745
38
Introduced in SQL 2008 for Login error where the database doesn't exist, Login doesn't have  access to the database
40
This like 16 and 38 occur when the login doesn't have access to the default database or it is offline.  HOWEVER what sets this apart is it accompanies an error 4064. (http://support.microsoft.com/kb/307864)

*NOTE

If you have errors that I haven’t listed let me know and I’ll add them to the list.  Leave your name, Twitter handle, blog address whatever and I’ll make sure to give you credit!

As always Dear Reader, Thanks for stopping by!

Thanks,

Brad

Tuesday, July 16, 2013

SQL Saturday 232 Schedule is LIVE!

Hello Dear Reader!  Just a quick blog post today.  The Schedule for SQL Saturday 232 is now live!  You probably have heard me tell the story about how the First SQL Saturday was 6 years ago in Orlando.  Many people from the community participated to make it a success, and that success continues because of community members all over the world.  This year as SQL Saturday comes home for its annual event we have a lineup that is unparalleled. 

Currently on the schedule we have 13 active SQL Server MVP’s, 4 Microsoft Employees, and 1 MCM.  Speakers from all over the country are coming to Orlando for this event and now all we need now is you.

Come make a little bit of history with us in Orlando.  All of this doesn't even cover the Lunch, served by speakers and always delicious, or the prizes.  Last year we gave away an X-Box, IPAD, gift cards, software, and many other prizes thanks to our great Sponsors.

SQL MVP Rodney Landrum (@SQLBeat|Blog), Pass Evangelist Karla Landrum (@KarlaKay22|Blog), and your friendly neighborhood SQLBalls worked tirelessly on the schedule.  We scrapped and scrimped to get every slot possible to bring you the BIGGEST SQL Saturday Orlando ever.

More blogs on this during the ramp up to the event, for today all I ask it go check out the schedule by clicking here.  Signup, get some SQL Community, and make sure to stop by and say Hi!

See you there!

Thanks,
Brad