August, 2008

Don’t Consolidate Yourself Into Performance Problems - Archive

The webcast which I did for Quest Software called Don’t Consolidate Yourself Into Performance Problems is now available for viewing after the fact.

This is the webcast which I did last week for Quest Software.  It you had signed up for the session Quest should have sent you an email with this URL already.

 Thanks,

Denny

How to setup a server to read log files nightly.

So you’ve been tasked with setting up a quick and dirty reporting server.  The goal is to restore the log files from the production server to the reporting server nightly.

 The backups are simple, use the SQL Maintenance plan to backup the logs, and then copy them to the remote machine.  But how do you restore the logs to the reporting server nightly.

 Well I’ve got a two step SQL job which should help you out.

Step 1 kills all current sessions in the database, and step 2 does the actual restores.

The code for step 1 is: 

declare @spid varchar(20)
declare cur CURSOR FOR
select spid
from sys.sysprocesses
where dbid = db_id(’Your Database Name Here’) /*<—Put your database name here*/
and spid > 50
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
exec (’kill ‘ + @spid)
fetch next from cur into @spid
END
close cur
deallocate cur

The code for step 2 is:

create table #Files
(FileName nvarchar(4000),
Depth int,
IsFile bit)insert into #Files
exec xp_dirtree ‘d:\’, 1, 1

I hope this makes your process easier. Now this code only works on SQL Server 2005 and up as the system stored procedures which I use were not included until SQL Server 2005.

Denny

SoCal Code Camp Sessions are posted

I and many of the other presenters have begun entering our sessions for the next SoCal Code Camp on on October 25th and 26th at USC in Los Angeles, CA.

The Code Camp is the weekend before PDC, and right near where PDC will be held.  So if you are coming to PDC this year, come a couple of days early and check out what is hoped to be the largest SoCal Code Camp ever.

I’ve got four sessions scheduled, all of which are tagged with “SQL Server“.

• Back To Basics: Getting Back To The Basics of SQL Server (Part 1)
• Back To Basics: Getting Back To The Basics of SQL Server (Part 2)
• Scaling that database bigger than ever (Part 1)
• Scaling that database bigger than ever (Part 2)
• Storage For the DBA

If you are planning on attending the Code Camp, be sure to mark the sessions you want to attend with the interest check box.  That way the Code Camp staff knows how big of a room each session will need.

If you are interested in speaking at the Code Camp, feel free to enter more sessions.  The more sessions we have, the more popular the event will be.

As the date gets closer additional sessions will be entered onto the site.  Be sure to check back often.

I’ll do my best to get the slide decks and sample code posted before the Code Camp starts.  Odds are I’ll get them posted on Saturday morning as I did last time.  If you missed any of my sessions from prior Code Camps, let me know in the comments and I’ll see what I can do about adding those sessions to the new schedule.  See you at the Code Camp.

Denny

I had a great talk with the NJ SQL User Group last night

Last night was my talk with the NJ SQL User Group.  I would jut like to thank them for the invite, I had a great time coming and speaking with them.  It was a very interactive night, with a lot of questions.  Quest Software was nice enough to not just send me out there, but one of the local sales reps also came, with a massive amount of give aways for the members.  He brought T-Shirts, demo disks, and the new Quest Tote Bags which were a huge hit.

 The presentation went great, and there were a ton of questions.

 Congrats to the two winners of the Laptop Cop software which was graciously provided by my employer Awareness Technologies.

Denny

Tuning SQL Server performance via disk arrays and disk partitioning

I’ve published another tips on SearchSQLServer.com called “Tuning SQL Server performance via disk arrays and disk partitioning“.  This is sort of a part one of a two part series of tips on tuning the database server at the hardware level.  In this tip I’m focusing on getting the disks setup just right.  I also show how to use diskpart.exe to see if the disks are correctly aligned.

Denny

Great article about how Microsoft prevents cheating on the certification exams.

A while back I was flipping through the Microsoft news groups and found a link to an excellent story that networkworld.com put together on how Microsoft is protecting it self from brain dump sites and exam cheaters, and how they are protecting the investment that we all have made in getting Microsoft certifications.

Denny

I’ll be speaking at the New Jersey SQL Server Users Group next week

After months of waiting the time is finely here.  I’ll be speaking at the New Jersey SQL Server Users Group next week.

 The topic of the talk will be SQL Server Service Broker in the Real World.  You can download the slide deck and sample code which I’ll be using at the presentation.

 You can read up more about the New Jersey SQL Server Users Group at their homepage.

See you there.

Denny

Why Entry-Level Certs Aren’t Enough to Get You a Job

Ed Tittel has written an excellent post on his blog (IT Career JumpStart) entitled Why Entry-Level Certs Aren’t Enough to Get You a Job.  I think that this is an excellent read for anyone who is just starting out in the IT Certification path (including the MCP, MCDBA, MCTS, MCITP, etc path).

Thanks Ed!

 Denny

Don’t Consolidate Yourself Into Performance Problems

Consolidated too many databases or too many instances onto a single server?  Got users screaming for their own servers back?

Join myself and David Swanson from Quest Software (and frequent poster on SQLServerPedia.com) as we show you how to identify problematic applications so that they don’t take down all of the other applications on the server at the webcast “Don’t Consolidate Yourself Into Performance Problems“. Attend this webcast to save your consolidation project and to keep your users happy.

The webcast will be on Thursday, August 21, 2008 at 8 a.m. Pacific / 11 a.m. Eastern / 4 p.m. United Kingdom / 5 p.m. Central Europe.  After the presentation there will be a Q&A session where David and I will answer as many questions as possible.

Denny

Back To Basics: The RESTORE DATABASE Command

The restore database command is what is used to recover a database which has been backed up using the BACKUP DATABASE command.  The syntax of the RESTORE DATABASE command is very similar to the BACKUP DATABASE command in many respects.

 You start with where you are restoring the database from.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'

Then if you need to move the physical files to another location because your disks are laid out differently, or because your folder layout is different you can add a MOVE command for each file you want to move.  For each MOVE command you specify the local file name, and the new physical file name.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'
WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,
  MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’

When restoring the database, many people think that you have to create the database first.  You do not.  When restoring a database through the UI (Enterprise Manager, or SQL Server Management Studio) if you create the database first, it will then be selectable in the drop down menu.  Even with using the UI, creating the database first is optional as you can simply type in the name of the new database in the UI.

If you are restoring from a striped database backup then you will need to specify the name of all the members of the strip.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase1.bak',
  DISK=’E:\Backups\MyDatabase2.bak’
WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,
  MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’

If you need to restore a differential or log backups after you restore your full backup you will want to place you backup with the NORECOVERY flag. This will tell SQL Server not to complete the recovery process, and to leave the database in an unusable state. This will allow you to continue the restore process. Once the database has been switched into a writable state you won’t be able to restore any transaction logs to the database without restoring from the full backup again.

RESTORE DATABASE MyDatabase FROM DISK='E:\Backups\MyDatabase.bak'
WITH MOVE ‘MyDatabase_Data’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Data.mdf’,
  MOVE ‘MyDatabase_Log’ TO ‘D:\MSSQL\MSSQL.1\MSSQL\Data\MyDatabase_Log.ldf’,
  NO RECOVERY

Denny