Wednesday, July 31, 2013

24 Hours of PASS Deck and Demo's Live!

Hello Dear Reader!  Another very quick blog.  Thank you to all of the people that tuned in to see me present on SQL Data Internals for the 24 Hours of PASS tonight.  I truly appreciate you spending your hard earned time with me.

My Deck and demo's are now live on the resources page.  I've added a list of all the presentation's that I used as references.  Any Scripts not in the deck you can find at the following links.

Click Here for the Slide Deck and Click Here for the Demos.  Now the links to all the other material.

Paul Randal MCM Video Series Data Structures http://technet.microsoft.com/en-us/sqlserver/gg313756.aspx

Bradley Ball SQL Internals Reading Data Records Part 1: Tag Bytes http://bidn.com/blogs/SQLBalls/ssas/2776/sql-internals-reading-data-records-part-1-tag-bytes

Bradley Ball SQL Internals Reading Data Records Part 2: Null Bitmap Offset http://bidn.com/blogs/SQLBalls/ssas/2781/sql-internals-reading-data-records-part-2-null-bitmap-offset

Bradley Ball SQL Internals Reading Data Records Part 3: Fixed Length Columns http://bidn.com/blogs/SQLBalls/ssas/2785/sql-internals-reading-data-records-part-3-fixed-length-columns

Bradley Ball SQL Internals Reading Data Records Part 4: Null Bitmap http://bidn.com/blogs/SQLBalls/ssas/2789/sql-internals-reading-data-records-part-4-null-bitmap

Bradley Ball SQL Internals Reading Data Records Part 5: Variable Offset Array http://bidn.com/blogs/SQLBalls/ssas/2791/sql-internals-reading-data-records-part-5-variable-offset-array

Bradley Ball Differences in the Null Bitmap between SQL 2005 and SQL 2012 http://www.sqlballs.com/2012/07/differences-in-null-bitmap-between-sql.html


Bradley Ball SQL Internals Reading Data Records Part 6: Variable Length Data http://www.sqlballs.com/2012/07/sql-internals-reading-data-records-part.html



As always Thanks for stopping by!

Thanks,

Brad

I'm Presenting Today for 24 Hours of PASS!!

Hello Dear Reader!  This is just a very quick blog to let you know that the 24 Hour's of PASS is currently going on.  Brent Ozar(@BrentO | Blog) started things off Strong and Alan Hirt(@SQLHA | Blog) is working his magic as we speak.

This year I was extremely honored to get to introduce Joseph D'Antoni (@Jdanton | Blog) in the 6 am slot tomorrow.  It would also work out that I could be a backup presenter as well.  So Tonight at 6 PM East Coast Time, following the one and only Brian Knight(@BrianKnight | Blog) is SQLBall presenting on Data Internals.

This is a mere glimpse into the 90 minute Spotlight Session I'll have at the Summit this year. So come join me for some Spelunking around the Data Internals tonight!  Here's the link to sign up and Here is the Abstract.



SQL Internals

Duration: 60 minutes
Track: Enterprise Database Administration & Deployment
Often as DBAs, we learn backwards. You get a DBA job, and you have databases that have tables, which have data in them. Nobody tells you that there are records, stored on pages, grouped in extents, assigned by allocation bitmaps, with IAM pages and allocation units that span partitions and filegroups.

Today is your lucky day! This 24 Hours of PASS session will cover byte swapping and bitmaps and dive a little into decoding Hex and binary, working to give you an understanding of database internals and how you use them every day!  




Alright Dear Reader, I'm off to practice!  I'll see you at 6 pm.

Thanks,

Brad

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

Monday, July 15, 2013

T-SQL Tuesday #44 Wrap UP



Hello Dear Reader, what a busy week we’ve had last week!  I’ve got 3 big Thank You’s that I would like to give. 

First off I’d like to say Thank You to Adam Machanic for allowing me to host T-SQL Tuesday #44.  When I first started blogging, I participated in T-SQL Tuesday to keep myself writing at least once a month.  Hosting one seemed like a very far off goal at the time.  Thank you Adam for coming up with the concept and helping to promote growth for all of us bloggers out there!

Secondly my SQL Family, SQL Friends, and fellow Bloggers.  Without you writing there is no content.  You put your hard earned time into this effort and I Thank you.  Not gonna like I’m getting a little verklempt! 

Lastly Dear Reader, Thank You.  Without you all we may as well not put words to digital paper.

The subject was Second Chances and I was very impressed with the blogs, all around great job everyone.   18 spectacular blogs all about different types of Second Chances.



Koen Verbeeck (@Ko_Ver | Blog) Wrote about free time on the job, and how he would approach it now vs. years ago.



Jes Schultz Borland (@grrl_geek | Blog) Blogs about her first experience with SQL Server Clustering and the lessions she’s learned along the way.  She also has the funniest picture of the day, I believe people stopped and looked at me as I laughed.


Steve, our resident Aussie/DBA in Exile, (Blog) talks to us about some of the many different mistakes made.  I believe my groan and the words “ouch” may have audibly escaped my lips stirring my cube mates when I read about the SAN bullet point.  Most importantly he reminds us to celebrate failure as well as success, and how to do it with a sense of humor.

Joey D’Antoni (@jdanton | Blog) A mountain of a man, with a stare that could straighten out T-SQL at 200 paces, tells us all about the moment you realize that you’ve done something wrong, and a time where a QA task was run, but not against QA.  Probably the best quote of the day goes to him “the bead of sweat moment”.  Beautiful description of a feeling that all of us have either had, or will have.


Oliver Asmus (@OliverAsmus|Blog) shares with us a story of woe when he was a Junior DBA.  It involves a Delete statement that has a where clause, but only the delete portion was highlighted.  The dreaded where clause, we know thee well.  Fortunately a Sr DBA was there to help out and Oliver adds some nice thoughts on explicit transactions.

The always excellent Robert Pearl (@PearlKnows|Blog) gives us some Pearls of Wisdom on sending emails.  In our youth we tend to fire them off rather quickly, sometimes that leads to trouble.  This is great advice for anyone in the business world with an email account!

Lance England (@LanceEngland|Blog) reminds us of all the things that can go wrong when we leave our cell phone at our desk during lunch….. and kick off a large update transaction on prod…… and do not execute a commit or rollback.  The most important part is once you make the mistake how you learn from it.

Stuart Moore (@napalmgram|Blog) reminds us that it isn’t just mistakes that we want a second shot at.  There are quite a few where you did a good or even great job but you know you could have done better with more time.

Martyn Jones (@MartynJones|Blog) Wrote his FIRST T-SQL TUESDAY Blog this week!  First off Thanks for Joining the Party Martyn!  Martyn takes us through an exercise in making sure you’re executing your code in the right environment.  Great Point!  I always like to check SSMS to validate my environment, once bitten twice shy.


The Editor and Chief of SQLServerCentral.com Steve Jones(@way0utwest|Blog) weighs in on SQL Slammer and the havoc that can be done by not patching.  Ahhh 2002, we all remember you well!



WRAP IT UP 

When you look at the people that participated this past month you see MVP’s, an MCM, Consultants, DBA’s, and none of us are perfect.  Mistakes are a part of life, and it isn’t the mistake that is important but how you handle it.

The next time you do I hope you remember that, take a deep breath, and just keep going.  That Second Chance to do things differently will be just around the corner.

As always Thanks for stopping by.

Thanks,


Brad