SQL Server with Mr. Denny


August 25, 2008  11:00 AM

How to setup a server to read log files nightly.

Denny Cherry Denny Cherry Profile: Denny Cherry

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

delete from #Files
where IsFile = 0

declare @FileName nvarchar(4000)
declare cur CURSOR FOR SELECT FileName from #Files
open cur
fetch next from cur into @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName = 'D:\Path\To\Your\Log\Backups\' + @FileName
RESTORE LOG YourDatabaseNameHere FROM DISK=@FileName WITH STANDBY='D:\Path\To\Your\StandBy\File.standby'
IF @@ERROR <> 0
exec xp_delete_file @FileName
fetch next from cur into @FileName
END
close cur
deallocate cur

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

August 21, 2008  11:00 AM

SoCal Code Camp Sessions are posted

Denny Cherry Denny Cherry Profile: Denny Cherry

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“.

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


August 20, 2008  10:58 PM

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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


August 19, 2008  4:52 PM

Tuning SQL Server performance via disk arrays and disk partitioning

Denny Cherry Denny Cherry Profile: Denny Cherry

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


August 18, 2008  11:00 AM

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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


August 14, 2008  11:00 AM

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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


August 13, 2008  10:37 PM

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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


August 13, 2008  1:05 AM

Don’t Consolidate Yourself Into Performance Problems

Denny Cherry Denny Cherry Profile: Denny Cherry

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


August 11, 2008  11:00 AM

Back To Basics: The RESTORE DATABASE Command

Denny Cherry Denny Cherry Profile: Denny Cherry

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


August 7, 2008  11:00 AM

How to configure DTC on Windows 2003

Denny Cherry Denny Cherry Profile: Denny Cherry

Back in the Windows 2000 days, getting DTC to work was easy.  It was setup by default to work.  There wasn’t a whole lot of configuration which needed to be done to get it setup correctly.

When Windows 2003 was released things got a bit different.  All of a sudden DTC wasn’t working by default, and there wasn’t a whole lot of documentation around on how to get it working.  The first thing to realise about DTC on Windows 2003 is that it isn’t completely installed by default.  The basics of DTC are there, but network support isn’t enabled by default.  What’s the point of that you ask?  This will allow two applications installed on the same machine to use a single transaction, but that transaction isn’t going to be able to go between machines.

 The first thing that we need to do is install the network half of DTC.  We do this in Add/Remove Programs under the Add/Remove Windows Components button.  When the Windows Components Wizard opens select Application Server and click Details.  You’ll then want to check the “Enable network DTC access” check box.

Add/Remove Programs

After that, finish running through the wizard and network DTC will be installed.  Don’t get to excited yet (Distributed transactions are exciting right?), we still need to configure DTC.  To do this we need to open the Component Services MMC from the Administrative Tools menu.

Component Services

From there, right click on My Computer and select properties and select the MSDTC tab. (If the network components aren’t install this tab usually won’t show up.)  From the MSDTC tab select the Security Configuration button (bottom left).  When the next screen opens all the check boxes will be unchecked.  If you aren’t sure what you need to enable, simply check everything and select No Authentication Required.  If you have setup a DTC Logon Account which is a network account on all machines then you can require authentication if you would prefer.  If any of the machines which are going to be involved with the transaction are clustered via Microsoft Cluster Service you must setup all machines in the transaction to No Authentication Required.  DTC when setup as part of a cluster does not support Authentication.

When deciding which Authentication to use, every machine in the transaction should have the same authentication settings.  So if any machine is clustered all machines using DTC that talk to the cluster, or that talk to machines which talk to the cluster, etc will need to be setup for No Authentication Required.

Security Configuration

From here simply click OK, then OK.  It will prompt you that DTC needs to be restarted.  Allow it to do so, and you will be all setup to use distributed transactions.

When configuring DTC on a cluster you only need to configure one node.  This is because DTC is a cluster aware service so when you install DTC after setup clustering (or you setup clustering after installing DTC) the DTC service will already be setup as a clustered resource within the first cluster resource group created.  When you configure DTC for network access on a cluster the settings are written to the Quorum drive as well as the system registry which allows both nodes to share the settings.  If you have a cluster and you have to go through a firewall with DTC and have followed KB Article 250367 you will need to have more than 20 ports available to DTC.  This is because when you configure the DCOM protocols to use specific ports you are configuring all of RPC to use those specific ports.  This means that the cluster administrator needs to use these ports as does the Component Services window which monitors for distributed transactions.  When dealing with a cluster it is recommended that you have at least 100 ports open between the machines within the transaction.

If you need to setup MS DTC to talk to another DTC coordinator then you will probably need to use the No Authentication Required setting unless they support the other options.  Check with the vendor of the other coordinator to find out.

(These are the settings which you need no matter which version or edition of SQL Server you have installed.)

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: