SQL Server with Mr. 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


August 7, 2008  1:21 AM

Microsoft SQL Server 2008 goes RTM

Denny Cherry Denny Cherry Profile: Denny Cherry

Microsoft has just announced that SQL Server 2008 has been released to the public.

You can download the Eval edition as of today from the Evaluate Microsoft SQL Server 2008 webpage.  It’s has been released in a variety of languages including English, French, German, Spanish and Chinese.  Technet Plus subscribers can download it today, while retail packaging should be in stores shortly.

 (Do note that the main SQL Server 2008 webpage doesn’t show the release yet.  I heard about it via the TechNet newsletter.)

 Denny


August 4, 2008  11:00 AM

I need a secure transfer of data between SQL Servers. What are my options?

Denny Cherry Denny Cherry Profile: Denny Cherry

You’ve got a few options, none of which are all that easy to setup.

1. Export the data, then encrypt the file, then transfer the file, then decrypt the file, then import the file.
2. Setup IPSec between the two SQL Servers and then transfer the data as normal.
3. Setup a Secure FTP Server at the second SQL Server. Export the data, then use a SFTP server to transfer the file to the remove server, then import the file.

If you take option #1 you can use use openssl or PGP to encrypt the files.

If you take option #2 you can find some info about IPSec here.

If you take option #3 here is a site about setting up an SFTP server on Windows. This site also includes information on finding an SFTP client.

If I was setting this up I would probably go for Option #2, and if that didn’t meet my requirements Option #1.

Denny


July 30, 2008  1:24 AM

SQL Server Experts: MS SQL beats the “ACID” test for Database Technology in Features, Ease & Cost

Denny Cherry Denny Cherry Profile: Denny Cherry

I was asked to be interviewed for an article on Microsoft SQL Server by OdenJobs.com.  They have just posted the interview of myself and Christian Lefter, Andew Fyer, Namwar Rizvi, Brent Ozar, Brian Kelly, Chris Shaw, and Pinal Dave.

Part 1: MS SQL beats the “ACID” test for Database Technology in Features, Ease & Cost

Part 2: Stress, Pet Peeves, Roles and Responsibilities of a SQL Server DBA/Developer

Part 3: Becoming a SQL Server MVP and other Career Choices

Thanks,

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: