Feeds:
Posts
Comments

Image

 

It has been nearly one week since SQL Saturday 132 in Pensacola Florida.  I was chosen to give my talk on “Know Backups and Know Recovery”.  The session was in the first time slot and when I arrived at the event just before 8:00 AM I had to walk through about 4 inches of water soaking my feet.  It was raining pretty hard and shortly after arriving the thunder and lightening arrive and I am not talking about Devin Knight or SQL Chicken.

I walked around and caught up with some of my friends and then headed up to my room to get setup.  It took a little longer and I would have liked to get the audio visual equipment hooked up but I made good use of the time asking questions that would lead into my presentation.  I was honored to have Tony Davis from Simple-Talk attend my session and spend some time with me afterwards.  It was great getting his feedback on my session.  It was a bit eerie though watching the fierce wind, rain and lightening out of the windows of in the back of the room.

My intention was to leave the event shortly after my session to spend some time with my wife and three kids that were back at the resort on Pensacola Beach.  I couldn’t quite leave when I wanted due to the really bad lightening.  Once there was a quick break I was able to venture out into the parking lot with water just below my knees.  When I got in my wife’s new Jeep Rubicon and backed out of the parking space I noticed several inches of water in the passenger side floorboard.  Oops.

I thought that would be the worst of it but upon leaving the university and driving down the canals of downtown Pensacola (they couldn’t call them streets that day) I encountered water levels of about 3 1/2 feet deep.  It was a rather strange feeling driving the streets of Pensacola in 4 wheel drive worrying about submerging your vehicle in too deep of water.  It was even more strange driving 15 MPH across a mile long bridge in a complete white out.

Once I crossed the toll bridge onto Pensacola Beach the clouds stopped dropping the wet stuff and it was rather pretty.  I pulled in the parking garage of Margaritaville and pulled the drain plugs out of the floor board of the Jeep and let the water drain out, then put on my swim suite and had some fun with the kids and wife on the beach and in the pool.

It was fun watching the twitter feed seeing all the new hashtags.  #sqlswim, #sqlscuba, #sqlmonsoon, #sqlflood, etc.

The drive home Sunday was just as adventurous with heavy rain the entire way.  We might not have had all the fun in the sun that the kids came down for, but we managed to spend time together, worked in some beach, ocean, pool time and got to be part of history for Pensacola.  All in all I think the kids will have some great memories from this trip.

Lets hope for better weather in Fort Lauderdale this weekend for SQL Saturday 141.

 

 

 

The Columbus GA SQL Users Group will be hosting their first ever SQL Saturday on September 8th 2012 at Columbus State University.  We currently have 5 classrooms reserved and have the ability to add a couple more if needed.  Each room holds over 40 people so our current capacity is 200 attendees.

SQL Saturday’s are a free event for attendees.  Anyone can attend and get free Microsoft SQL Server training.  If our event is like all overs we will have a nice selection of sessions to pick from.  Topics of interest will include database administration, business intelligence, power shell and professional development.

I have been involved with helping to organize the last two SQL Saturday’s in Atlanta GA and have spoken at eleven SQL Saturday’s since Jan 2011.  I am hoping that through my involvement in other events that myself and my leadership team here in Columbus GA can pull of a very successful event for our local SQL Community and help grow the Columbus GA SQL Users Group.  Currently the only other event on this day is SQL Saturday #162 in Cambridge and a few friends have already submitted to that event.  If I had the funds I would be there too.  Would love to see my UK buddies.

Here’s to happy planning.

Without Instant File Initialization turned on, each time a data file on SQL Server 2005 and above is created or has to grow in size, the file or portion of the file added must be zeroed out.  Depending on the size of the file this can be a very time consuming task.

By turning on Database Instant File Initialization, you are telling the operating system to trust SQL Server to properly handle this allocation.  Since you are looking to enable Instant File Initialization you should also consider setting a proper auto growth setting for your database.  Something in MB verses percentage.  This should be a fail safe, you try to implement some type of policy or procedure to monitor the amount of free space in your databases and try to grow them after hours.  What me demonstrate this on YouTube.

While there isn’t an option within SQL Server to turn this on, the process is very simple.  You simply have to add a SQL Server group to a security policy.  To find which group to add you need to run lusrmgr.msc on the workstation or server in question.  Click on Groups, marked with a RED arrow on the top left of the image below.  Then look for the group that states “specified instance of SQL Server”  This is highlighted in the image below.  The group in my experience has nearly always been “SQLServerMSSQLUser”, plus Machine Name, then Instance Name.  Take note of this group (write it down) as you will need to add it to a local security policy.  You can now close out of lusrmgr.

Next click start, run and type SECPOL.MSC.  The dialog box in the image below will open up.  Expand “Local Policies”, click on “User Rights Assignment”, then scroll down the list of Policies until you see “Perform volume maintenance tasks”.  Double click on “Perform volume maintenance tasks”.

Click on “Add User or Group”

Click on “Object Types”

Uncheck all boxes except for “Groups” and click “OK”

Next make sure that “Locations” is your local workstation or server name, not the Active Directory Domain.  Next click “Advanced” and chose “Find Now”, a list of groups should now appear.  This is where you will scroll down the list and find the group you determined from above (lusrmgr.msc).  In my example it is “SQLServerMSSQLUser$WIN-LJDS9D9PTAA$MSSQLSERVER”.  Once you have selected your group click “OK”

You should now see your group listed.

Click “OK” and then “OK” one last time.  That is it. You can now close the Local Security Policy window.  When your database grows or when you add additional files, you should no longer have the long waits of the system having to zero out the additional space.  Want to see a live demo of this, check out my YouTube Channel

Additional things to note is
1) You could just grant access to the SQL Server Service account, however if this Service Account is ever changed you will have to go back and add the new account to the Security Policy.  Which ever way works best for you.  The important thing is to just have it on.
2) By not zeroing out the file there could possibly still be data that could be read.  This is a very slight security risk and there are only a few scenarios where this could pose a risk.  The average Joe could not access the data.
3) Instant File Initialization only works for your data files, it does not have any impact to your LOG files.

Want to see a live demo of this, check out my YouTube Channel

Without Instant File Initialization turned on, each time a data file on SQL Server 2005 and above is created or has to grow in size, the file or portion of the file added must be zeroed out.  Depending on the size of the file this can be a very time consuming task.

By turning on Database Instant File Initialization, you are telling the operating system to trust SQL Server to properly handle this allocation.  Since you are looking to enable Instant File Initialization you should also consider setting a proper auto growth setting for your database.  Something in MB verses percentage.  This should be a fail safe, you try to implement some type of policy or procedure to monitor the amount of free space in your databases and try to grow them after hours.

While there isn’t an option within SQL Server to turn this on, the process is very simple.  You simply have to add a SQL Server group to a security policy.  To find which group to add you need to run lusrmgr.msc on the workstation or server in question.  Click on Groups, marked with a RED arrow on the top left of the image below.  Then look for the group that states “specified instance of SQL Server”  This is highlighted in the image below.  The group in my experience has nearly always been “SQLServerMSSQLUser”, plus Machine Name, then Instance Name.  Take note of this group (write it down) as you will need to add it to a local security policy.  You can now close out of lusrmgr.

Next click start, run and type SECPOL.MSC.  The dialog box in the image below will open up.  Expand “Local Policies”, click on “User Rights Assignment”, then scroll down the list of Policies until you see “Perform volume maintenance tasks”.  Double click on “Perform volume maintenance tasks”.

Click on “Add User or Group”

Click on “Object Types”

Uncheck all boxes except for “Groups” and click “OK”

Next make sure that “Locations” is your local workstation or server name, not the Active Directory Domain.  Next click “Advanced” and chose “Find Now”, a list of groups should now appear.  This is where you will scroll down the list and find the group you determined from above (lusrmgr.msc).  In my example it is “SQLServerMSSQLUser$WIN-LJDS9D9PTAA$MSSQLSERVER”.  Once you have selected your group click “OK”

You should now see your group listed.

Click “OK” and then “OK” one last time.  That is it. You can now close the Local Security Policy window.  When your database grows or when you add additional files, you should no longer have the long waits of the system having to zero out the additional space.  Want to see a live demo of this, check out my Youtube Channel

Additional things to note is
1) You could just grant access to the SQL Server Service account, however if this Service Account is ever changed you will have to go back and add the new account to the Security Policy.  Which ever way works best for you.  The important thing is to just have it on.
2) By not zeroing out the file there could possibly still be data that could be read.  This is a very slight security risk and there are only a few scenarios where this could pose a risk.  The average Joe could not access the data.
3) Instant File Initialization only works for your data files, it does not have any impact to your LOG files.

Want to see a live demo of this, check out my Youtube Channel

A friend of mine contacted me through email today having a very common problem with a query he had written.  He received an error, “cannot resolve collation conflict for equal to operator”.

Early in my career I had experienced the same issue so I quickly searched my code snippets and found where I had used COLLATE DATABASE_DEFAULT after each of the comparison columns.  For example

SELECT COLUMN
FROM TABLE_A
INNER JOIN TABLE_B
WHERE TABLE_A.COLUMN COLLATE DATABASE_DEFAULT = TABLE_B.COLUMN COLLATE DATABASE_DEFAULT

If you experience the error “Cannot resolve collation conflict for equal to operator” I hope this solution helps you out.

Have you ever tried to restore a backup over an existing database and receive an error stating the tail of the log for the database has not been backed up? Many times if you are just restoring a database into a development environment you don’t care so you just restore WITH REPLACE and move on.  However if you want to ensure that your restore contains the latest transactions from the production database, simply make a transaction log backup.  Example

(BACKUP LOG [DB_NAME] TO DISK = ‘DRIVE:\PATH\DB_NAME_LOG.BAK’)

Now lets suppose you are working with a production system and something dreadful happens to the database.   Lets assume that you have a transactional database and take TLOG backups every 10 minutes. Now lets suppose at 2:00 in the afternoon a regular TLOG backup is taken and at 2:07 there is a hardware problem where your MDF file becomes corrupt. You now have to restore from backup. What happens to those 7 minutes of transactions?

Without recovering the tail end of the log, this data is gone. Since it was only the MDF file that was corrupt or lost, we can still take a log backup even without the MDF. To do so use

(BACKUP LOG [DB_NAME] TO DISK = ‘DRIVE:\PATH\DB_NAME_LOG.BAK’ WITH NO_TRUNCATE)

The key here is the NO_TRUNCATE statement.  Using NO_TRUNCATE allows you to be able to backup the transaction log without the database being online.  If your database is online you can just take a regular transaction log backup.

If you want to see this in action, check out my video.  In this video I go rouge and delete my MDF file and demonstrate how to backup and restore the tail end of the log.

How to backup and restore the tail end of a transaction log

I had the pleasure of attending SQL Saturday 112 this past weekend.  This was my second time speaking at a SQL Saturday in Birmingham.  I was selected to present two sessions again this year in the first and second time slot.  I started off with “Getting Involved and Getting Ahead”.  This session is all about the SQL Community and how volunteering and giving of your time can pay huge dividends in your career.  You get to learn so much more and at such an advanced rate that the sky is the limit.  While helping others you gain as much or more.  The contacts you make alone are work the investment of your time.

The second session I gave was a brand new session called “Know Backups and Know Recovery”.  I presented this session in March to the Columbus GA SQL Users Group.  I really enjoy this session and hope to make it my primary session I give.  In this session I cover best practices with make FULL, DIFFERENTIAL, and TLOG backups of your databases.  I find that most people are at least doing FULL and Transaction logs.  About 25% do weekly FULL and Daily DIFFERENTIAL backups.  I also have found that most attendees have not practiced restores to include their DIFF and Transaction log backups.  In this session I demo using a script to generate the restore scripts of the last FULL, DIFF and all transaction log backups since the last respective FULL or DIFFERENTIAL backup.  I also demo FILEGROUP backups, peace meal restores and even a TAIL LOG backup and restore.

In between giving these two sessions and the remainder of the day I helped work the RedGate booth and PASS Booth.  I had a great deal of fun helping to spread the word about all things PASS and helping to field questions about many of the RedGate products.  The organization team for SQL Saturday 112 did an excellent job putting on this event.  I really like the speaker shirt and the bags the team got.

The event location at Jefferson State Community College was great.  Large classrooms and a centrally located vendor area.  There was excellent signage leading up the event, schedules posted on the doors to the rooms, a very well put together event guide and tons of drinks, donuts and coffee.

I will definitely be back to this event as long as they are willing to have me.

My slides can be found on the SQL Saturday website here.

Follow

Get every new post delivered to your Inbox.

Join 773 other followers