SQL Server 2008 provides a feature, which, in my opinion, has been far overdue -- backup compression. For too long, if you wanted the benefit of compressed backups, you had to look at a third-party tool. Now, backup compression is built right into SQL Server 2008, and what's even better is that it's easy to use. All you have to do is append the WITH COMPRESSION option to your backup statements and you are off to the races. This bit of code will back up the AdventureWorks2008 database using compression:

BACKUP DATABASE AdventureWorks2008
TO AdWorksBackup
WITH COMPRESSION

Using compression with SSMS is just as easy. Simply set the compression option on the Options page of the Back up Database dialog.

You may be asking yourself: How effective is this compression? The answer can be tricky because it depends on the structure of your database and the type of data being stored. When I backed up my copy of the AdventureWorks2008 database, which is using about 700 MB of disk space, I got a 147 MB compressed backup file. Compare that to the 636 MB file I got when not using compression.

Here's what else is cool: You can change the default compression behavior of your entire server. On the Database Settings tab of the Server Properties dialog, you can select the option Compress Backup. Alternatively, you can run the following t-SQL code:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'backup compression default', 1
RECONFIGURE

The first command enables advanced options and the second will make compression the default for all backups. If you go this route, you won't have to change a thing about your backup scripts in order to take advantage of compression. Now, just a simple BACKUP DATABASE statement will use compression. To run a backup without compression when it's the server default, simply use the WITH NO COMPRESSION option.

SQL Server 2008 finally offers backup compression natively and I hope you find it to be a useful feature. It's great for saving disk space and you no longer have to zip backup files before moving them over the network just to improve the copy time

Digg It

You ever find yourself with the need to copy SQL Server logins from one server to another? Maybe you are setting up a failover site, building a replacement server, setting up a reporting instance, or maybe you just want to backup the logins just in case. If you are using Windows Logins, this is a simple matter of scripting the login and applying it to the other server. Copying SQL Server Logins from one box to another is a bit trickier because SQL Server stores and manages the password. So just how do you copy the login and preserve the password? I am glad you asked.

Understanding Login Components

To successfully copy a login from one server to another, you will need to ensure that the copy has the same SID and password. The link between database users and logins is done with the logins SID, if this is different on the new server than any databases you copy over will contain orphaned users. To ensure that both the SID and the password are the same, Microsoft has written a stored procedure to aid in our transfer.

SP_HELP_REVLOGIN

SP_HELP_REVLOGIN is a stored procedure that will return a complete list of the logins that exists on you SQL Server in a script that can be run to recreate them. This script does not exist on your SQL Server by default, you must create with the code provided by Microsoft in KB article 918992 here http://support.microsoft.com/kb/918992/. Once you have create the procedures you can easily generate the create statements that allow you to copy your logins.

As an example, I created a new login on my local instance of SQL Server called SQLScript with a password of scriptme. Now I can run SP_HELP_REVLOGIN as follows:

sp_help_revlogin 'SQLScript'

RESULTS:

/* sp_help_revlogin script

** Generated Oct 30 2007 9:23AM on laptop1 */

-- Login: SQLScript

CREATE LOGIN [SQLScript] WITH PASSWORD = 0x0100B642C5A8BC6778ECE4710ED3DC8D70E0EA31B6DF6B122756 HASHED, SID = 0x80525EB475F8414FB32D627BB876F213, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

As you can see, I now have the syntax I need to recreate the login on another box. The SID will be forced to the same value and the passwords will match by virtue of this statement providing the hashed version of the password. If you need to copy all the logins, SQL Server and Windows Logins, you can run SP_HELP_REVLOGIN with no parameters.

Digg It

Welcome to the podcast for IT pros at CSTechcast.com. This week we look at the coming trends for the SQL Server database platform with our friend Paul Nielsen, author of SQL Server 2005 Bible. Find Paul and his books at sqlserverbible.com. In the news; Apple's DNS patch fails to randomize ports plus other DNS patches show new flaws, IBM commits to the cloud with a heavy investment in data centers, Microsoft is set to deliver Small Business Server 2008 for mom and pops and Essential Business Server 2008 for the mid-market this year, the Storm worm pops back onto the radar with an FBI spoof, and Sun debuts JavaFX to compete with Adobe. Plus, Apple's culture of secrecy gets "The Worst Tech Move of the Week", and we put mobile security in our crosshairs for "A Closer Look".

Links to stories discussed during the show:
Apple's patch fails to fix DNS flaw, researchers claim (ComputerWorld)
DNS patches cause problems, developers admit (InfoWorld)
IBM Brings Cloud Computing To Earth With Massive New Data Centers (InformationWeek)
Windows Small/Essential Business Server RC1s arrive (Ars Technica)
FBI warns of new Storm worm attacks (ComputerWorld)
Jobs entrusts a NYT columnist with the truth about his health, even before he tells Apple shareholders (VentureBeat)
Travelers' Laptops May Be Detained At Border (Washington Post)

We apologize for the late post of our podcast, but system problems prevented a timely post. This is the first time we have missed the release mark. Anyway, we hope everything is back on track hardware wise. The drawing for an Amazon.com gift certificate is just a few weeks away, so visit CSTechcast.com to submit your feedback to enter. We'd like to thank those who have submitted the wonderful, constructive feedback so far and look forward to more from our listeners. Please subscribe and write a review on iTunes or your favorite podcast site. Thanks for listening.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=36

- Eric Beehler (consortioservices.com/blog)

Digg It

I just started working on a LiveLesson DVD for Addison-Wesley tentatively titled "SQL Server Fundamentals for the Accidental DBA". The DVD will focus on the essential skills required to work with SQL Server for people that are not full-time DBAs. I will keep you posted as I have more details. For more information about the LiveLesson videos, check out http://www.informit.com/promotions/promotion.aspx?promo=135366.

Eric
Co-Host CS Techcast
http://www.cstechcast.com

Digg It

No need to wait in line for our newest podcast; all the week's tech news and insight for IT Pros is here at CSTechcast.com. We explore the latest in delivering applications to remote and global networks with Zach Seils, author of Deploying Cisco Wide Area Application Services. This week, Microsoft lobs another offer at Yahoo, DNS gets patched across all vendors, NVidia slowly reveals more about their overheating laptop chips, cloud apps have downtime woes, SQL Server will be out in August, and a new Microsoft push for software plus services. Overreacting tech bloggers make "The Worst tech Move of the Week", we take "A Closer Look" at the new Microsoft Certified Master program, and "The Weekly Tech Tip" covers WSUS troubleshooting.

Links to stories discussed during the show:

Yahoo Rejects Joint Microsoft/Icahn Deal But Open To $33 Per Share All-Microsoft Sale (Search Engine Land)
Patch domain name servers now (ComputerWorld)
Report: NVIDIA issues cover all G84 and G86 GPUs (ZDNet)
Rackspace Downtime: A Reminder That All Are Vulnerable (TechCrunchIT)
Microsoft SQL Server 2008 Due in August (eWeek)
Oracle, Salesforce.com Jump on iPhone Bandwagon (eWeek)
Microsoft still pushing Vista compatibility story (CNet)
iPhone 3G disaster (Scobleizer's Recent Qik Videos)
Microsoft Certified Master Program (Microsoft)
More on the Certified Master programs from me, Per, the program owner... (Technet Blogs)
Troubleshooting WSUS downloads (Technet Blogs)

Thanks for listening and give Dana a warm welcome. She will be helping us out with the news and shaking up the format a bit. We hope you like her as much as we do. Don't forget our give away. Enter at CSTechcast.com for your chance to win and take our money in the form of an Amazon.com gift certificate.

Link to the episode: http://www.cstechcast.com/home.aspx?Episode=33

- Eric Beehler (consortioservices.com/blog)

Digg It

The new SQL Server installation is so different, I thought I would take a second to write about it. For starters, the whole thing is now called the SQL Server Installation Center.

This tool is broken down into seven sections which include the following:

• Planning - This section gives you access to documentation and allows you to run the upgrade advisor.
• Installation - This is where the rubber meets the road. You can install stand-alone instances, clusters, add-nodes to existing clusters, and even start upgrades from 2000 or 2005.
• Maintenance - Here you can change your edition of SQL Server (that's right - change - did you install developer edition on production by accident, here you can change to to enterprise), repair an installation, or remove a node from a cluster.
• Tools - Access to a few handy tools such as the System Configuration Checker, a report to tell you what SQL Server features are installed, and an SSIS upgrade wizard.
• Resources - Links to helpful documentation on SQL Server
• Advanced - This section lets you install SQL Server based on a configuration file, perform advanced cluster prep, or complete a cluster from already cluster-prepared SQL Servers.
• Options - Allows you to specify the architecture (x86, x64, or ia64) and the location of the install media.

Enough about the Installation Center, let's take a look at the stand alone installation. I am not going to walk through step-by-step, I just want to highlight some of the new features that I think are pretty cool.  This is the face of the new instillation wizard.

The first few screens are just some checks and then the installation of the prerequisites. After that's done we get to the meat of the installation. Even here, the first few screens are pretty straight forward asking if you are installing a new instance or changing an existing instance and another screen allowing you to enter you product key. The first screen that grabbed my attention was the Instance Configuration. Here you not only specify and instance name, but you an also specify the instance ID. The instance ID is used in folder naming instead of random numbers. By default the ID will be the name of your instance.

The next few screens are your standard drive space overview and service account set up, nothing really new here. Ah, but then we get to the Database Engine Configuration screen which abounds with new options for security, file locations, and the new FILESTREAM type. The first tab, shown below, lets you set the authentication mode and set up accounts that will be administrators of SQL Server. Gone are the days of BUILTIN\Administrators and here are the days of telling SQL Server who you want to be administrators.

The second tab, Data Directories, allows you to set up the default locations of some common files used by SQL Server. This really gives you a level of control that was previous a registry hack, done manually, or done after the installation was complete.  You can set up individual locations for the following:

• Data Root
• User Database Files
• User Database Log Files
• Temp DB Data
• Temp DB Log
• Backup Files

Last but not least, the third tab allows you to enabled FILESTREAM for T-SQL access. I won't get into FILESTREAM here, but it is a feature of SQL Server 2008 worth your further investigation.

That's the bulk of the installation....one more check to look for problems that might cause the installation to fail and your off and running.

I know the installation wizard isn't the sexiest new thing in SQL Server 2008, but there are some neat new things that made me happy so I wanted to share my rather geeky bliss.

Digg It