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


July 28, 2008  11:00 AM

You can’t deploy a SQL 2008 SSRS Report to a SQL 2005 SSRS Server

Denny Cherry Denny Cherry Profile: Denny Cherry

SQL 2008 and SQL 2005 use different RDL namespaces and so they pretty much aren’t compatible.  You can edit SQL 2005 Reports using the SQL 2008 editor, but new reports won’t be able to be deployed to the SQL 2005 report server.

With enough major editing of the SQL 2008 report you can convert it to a SQL 2005 report.

In a future post (you can read this as, as soon as I figure out just what editing needs to be done) I’ll cover what editing needs to be done to the SQL 2008 RDL to make it run within a SQL 2005 Report server.

This is going to come in handy as you can’t have SQL 2005’s UI and SQL 2008’s UI installed on the same machine.

Denny


July 24, 2008  11:00 AM

How to setup SQL 2008 BIDS to use VB.NET as the default scripting language

Denny Cherry Denny Cherry Profile: Denny Cherry

If you are like me and don’t know anything about C#, and you don’t want to have to change the script language every time to create a .NET script from C#.NET to VB.NET you can change the default.

 Open BIDS, and select Tools > Options.  On the menu on the left select “Business Intelligence Designers” then “Integration Services Designers”.  In the Script box in the middle of the right pain change the option in the drop down from “Microsoft Visual C# 2008″ to “Microsoft Visual Basic 2008″.

If you prefer C# you’ve got nothing to worry about as C# is the default option.

Denny


July 22, 2008  11:00 AM

Back to Basics: The BACKUP DATABASE command

Denny Cherry Denny Cherry Profile: Denny Cherry

Now that you have objects created within your database, and data within the tables you need to secure your database in case of a server failure or accidental data deletion.  This would be done by taking backups of the database.  Backups are taken by using the BACKUP DATABASE command.

The frequency that full backups are taken at should be determined by how much data changes within the databases.  On busier systems full backups should be taken daily.  On systems which are less busy the full backup can be taken less often, every few days, once per week, etc.

The syntax of the BACKUP DATABASE command is fairly basic.  You specify the name of the database to backup, and the destination that you will be backing up to.  If your database name is MyDatabase and your going to backup to a file named MyDatabase.bak on the E drive your BACKUP DATABASE command would look something like this.

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak'

This command will backup all tables, views, procedures, functions, etc as well as all the data within the database.  Backing up the database in an online operation meaning that users can be connected to the database, continuing to make changes to the database while the backup database command is running.  The database is backed up in the same state that it was in when the command started.  All changes made during the backup are logged into the database and staged in memory.  Upon completion of the BACKUP DATABASE command these changes are destaged from memory to the physical file during a checkpoint operation.

If you have a full text index, and are using SQL Server 2000 the full text backup is not included as part of the backup which is created by the BACKUP DATABASE command.  After restoring the database you would need to recreate the full text indexes.  This issue is resolved in SQL Server 2005 when the full text indexes are added to the database backup.

If you wish to create two backups of the database so that your backups can survive a failure of the disk you backup the database to you can use the MIRROR TO clause of the BACKUP DATABASE command.  This clause creates two exact duplicate backups of the database.  This option is included in SQL Server 2005 and higher.

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak' MIRROR TO DISK='F:\MyDatabase.bak'

If you find that your backups are taking to long do to a large database size and/or slow disk speed on the disk you are backing up to you can strip the database backup across database backups.  This is done by simply specifying additional destinations.

BACKUP DATABASE MyDatabase TO DISK='E:\Mydatabase.bak', DISK='F:\MyDatabase.bak'

Now striping your database can improve your backup performance, but it puts your backups at greater risk for a disk failure as each part of the backup disk is spread across two drives.  If either backup file is lost or damaged the entire backup is useless.  Because of this you can combine the striping for speed and the mirroring for safety.  To do this the number of backup devices specified in the MIRROR clause must be the same as the number of backup devices specified in the initial destination clause.

BACKUP DATABAES MyDatabase TO DISK='E:\MyDatabase.bak', DISK='F:\MyDatabase.bak' MIRROR TO DISK='G:\MyDatabase.bak', DISK='H:\MyDatabase.bak'

You can read up more about detailed backup techniques in the eZine article I wrote a few months back.

Check back in the coming weeks for information about log backups and restoring your database and log backups.

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: