The folks at Quest Software asked me to write a paper for them discussing the differences between managing a SQL Server using just the Microsoft provided tools and their Spotlight on SQL Server Enterprise product. That paper has just been released and can be downloaded here. We’ll also be discussing this paper (among other things) at a web cast Thursday 2/7/2008 at 8am which you can read about here and here.
Well today is my birthday. Since it’s a regular posting day I figured I’d skip the technical posting and just say hi. (I’ve got this thing against working on my birthday. I’d much rather spend the day with family and friends. It’s my day and I’ll do what I want, as long as it’s ok with the wife at least :-).)
On Thursday Feb 7, 2008 at 8am Pacific (11am Eastern, 4pm GMT) myself and Jason Hall, Systems Consultant at Quest Software will be co-hosting a webcast looking into the challenges in seeing exactly what is happening within SQL Server (including SQL Server 2008) using Spotlight® on SQL Server Enterprise compared to the native tools.
For those who are interested in attending the URL is: http://www.quest.com/events/listdetails.aspx?contentid=6732&technology=&prod=&prodfamily=&loc=4
FIRST TIME USERS: To save time before the meeting, check your system to make sure it is compatible with Microsoft Office Live Meeting: http://go.microsoft.com/fwlink/?linkid=52354
I look forward to seeing you all there.
The SoCal Code Camp was this last weekend. I had a great time attending and speaking at the code camp this time around. I hope that everyone who attended my sessions had as much fun attending the sessions as I did speaking at them. I did my best to make them as much fun and interactive as I could.
As promised here are the slide decks and sample T/SQL code from the four sessions. Everything is within a single ZIP file. I’ve included two copies of each slide deck. One in the Office 2007 format, and one in the Office 97-2003 format. They are identical to each other, but I wanted to include both so that people with the older version of Microsoft Office can see the deck without having to download and install the patch which allows Office 2003 to view Office 2007 files.
The sample scripts which I’ve included are all run against the AdventureWorks database or the AdventureWorksDW database (check the USE commands at the top of the scripts). If they need a different database they will create the new database.
Session 1 – SQL Server Query Tuning (SQL 2000+)
Session 2 – SQL Server 2008 Resource Governor (SQL 2008 CTP5+ only)
Session 3 – SQL Server Service Broker in the Real World (SQL 2005+) (I’ve fixed the problem with the single server script that we were having at the Code Camp. Turns out I had left the route in place which is why the message never showed up. The first script didn’t run correctly because I had run the server to server script on my virtual machine and the route was left by accident.)
Session 4 – SQL Server 2008 What’s on the Horizon (SQL 2008 CTP5+)
If you have any questions about these slide decks or sample code feel free to post a comment here, or drop me an email.
You can install SQL 2000 and SQL 2005 on the same machine. I always recommend that they be installed in this order to try and get everything working as best as possible.
- Install SQL 2000
- Install SQL 2005
- Install SQL 2000 SP4 (Or the latest service pack)
- Install SQL 2005 SP2 (Or the latest service pack)
There are literally thousands of different message boards on the Internet where you can find technical information from fellow IT professionals. When asking questions on these boards there are some basic guidelines which most people like to have followed. They are sometimes informal, but they are fairly common across most of the boards.
- Don’t use topics like “Help!!!” or “It doesn’t work”. Using a descriptive topic will make more people want to read your thread and help you out.
- Be polite when talking to the people who are trying to help you. They aren’t getting paid to do this, so be nice to them. It’s always nice to get a thank you from the poster.
- Check your spelling and grammar. It doesn’t have to be perfect, but many people prefer proper spelling and grammar. (Mine isn’t the best, but I do try.) If English isn’t your first language and people are giving you a hard time about your spelling or grammar some people will be a little more forgiving once they know that you are not a native English speaker.
- If the message board is in English and you are posting an error message which is in another language, please translate it to English. More people will be able to read it that way. You may not get the translation perfect, but it’s usually close enough to get the point across.
- Don’t post just the error number. There are thousands of error messages and we need the entire error message.
- Don’t just say that it’s broken. Be specific about what isn’t working.
- Let the readers know what you have done to try to fix the problem already. That will save you time as the people trying to help won’t have to have you try stuff twice.
- If you find the solution your self post it to the thread. This will help the next person who has the same problem. These forums are all about helping others and this is a great way to give back to the community.
- Please remember that the people responding aren’t computers. It may take time for someone to read your thread who knows the answer.
Virtualizing servers is huge right now. And thanks to VMWare it is very easy to build virtual machines. In most of IT virtualizing servers is a good think. In the database world, that isn’t always the case. As we know SQL Server uses a lot of memory and CPU resources. If you try to virtualize a high load SQL Server you could end up with all the resources of the virtual server being used by the single virtual server.
Now don’t get me wrong. If you have a small server with low load which has a SQL Server on it, such as a Blackberry Enterprise Server, that may be an excellent server to virtualize. Your production CRM database, probably wouldn’t be a good server to virtualize as it probably needs more CPU and memory resources than your Virtual server would be able to afford to give it. Now that said; if your VM Servers are huge servers with hundreds of Gigs of RAM and lots of CPUs (such as the new HP DL 580 and 585 models) and rack space is tight, then virtualizing SQL Servers might be a good plan provided you can deal with any disk IO issues.
In addition to the CPU and memory concerns there are the disk performance issues to look into. Typically when setting up a virtual machine, the disk drives are also virtualized. This means that to the virtual server host the virtual machines disks are simply files stored on the file system. This probably isn’t the best way to store SQL Server databases (or any system which uses the disk subsystem a lot). If you can setup your disks to be mapped directly to physically SCSI arrays you can remove this potential problem. Check your VM solution to see if this is supportd (VMWare does, I don’t know about Microsoft Virtual Server). If you can provide information about this feel free to post as such in the comments.
I found this great little MS KB article today. It’s got all the SQL Server 2005 post SP2 host fixes including bug numbers and descriptions of the bug. The MS KB article ID is 937137.
Microsoft’s Log Shipping is pretty good. But it requires that you have SQL Server Enterprise Edition on both the machines. This makes the solution fairly expensive. Because of this I’ve written a replacement which can be used on any edition of SQL Server including SQL Server Express Edition (as long as you use a different scheduler).
I recommend keeping the drive letters the same on the two machines, but this isn’t required.
First backup the database to log ship and restore it to the backup server using the WITH STANDYBY option. You are now prepped to start shipping the transaction log.
Setup a job on the primary server which backs up the log every few minutes to a network share on the backup server. I use code alone these lines.
backup log Northwind to disk='\\backupsql\BackupFolder\northwind.log' with NOINIT, NOSKIP, NOFORMAT
Add a second step to the job which uses osql to start a job on the backup server. Something like this. (The on failure action should be to Quit with Success for this step.)
osql -S BackupSQL -E -Q "msdb.dbo.sp_start_job 'Restore Northwind Log'"
Setup a restore job on the backup server (making sure that the name matches the job name in the second step of the backup job. The restore job will have four steps in it.
Step 1 (T/SQL):
/*This first part of the code ensures that no one is using the database that we are about to restore. If we don't do this then the restore will fail.*/
declare @spid as varchar(10)
declare @CMD as varchar(1000)
declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid =
(select dbid from sysdatabases where name = 'Northwind')
fetch next from cur into @spid
while @@FETCH_STATUS = 0
set @CMD = 'kill ' + @spid
fetch next from cur into @spid
Step 2 (OS Command):
REM /*This removed the last file we processed.*/
Step 3 (OS Command):
move d:\RestoreFolder\Northwind.log d:\RestoreFolder\Northwind.2.log
REM /*This moves the current file into place for processing.*/
Step 4 (T/SQL):
declare @i int
declare @j int
set @j = 1
set @i = 0
restore headeronly from disk='d:\RestoreFolder\Northwind.2.log' /*This tells us how many transaction log backups there are in the file that we need to restore.*/
set @i = @@ROWCOUNT
while @i+1 > (@j) /*This loop runs us through the file restoring the logs in order.*/
restore log Northwind from disk='d:\RestoreFolder\Northwind.2.log'
WITH FILE = @j,
STANDBY = 'F:\MSSQL\Backup\RMDBArchive.sby' /*This keeps the database in standby mode ready for more restores.*/
set @j = @j + 1
If your folders don’t exist between the two servers you’ll need to add the MOVE parameter to the restore commands.
If you do being using this please post a note to the comments if you can. I like to know who is using this log shipping code.
The engineers from Microsoft PSS has posted some excellent information on thier blog about SQL Server 2008’s Resource Governor.
It’s some pretty hard core information but when it comes to SQL 2008 it’s going to be some useful information to have.
Update 2008.01.14: Now with correct URL. Sorry about that.