Two of the SQLServerPedia Editors, Tom LaRock and Douglas McDowell, have been nominated for elections on the PASS Board of Directors.

Congratulations, guys!

Readers - if you’re attending the PASS Summit in Seattle, stop by the voting kiosks to cast your vote for the Board of Directors. This crew of professionals is responsible for coordinating volunteer efforts, directing the community, and making a difference in how we do our jobs as SQL Server DBAs. It really does impact you.

If you’re not attending the summit, keep your eyes posted in your email inbox for a voting survey too.

Technorati Tags: pass, pass2008, passsummit

If you’ve been around a while, you tend to do things the way you first learned how to do them.  This can turn you into an “old timer” whose oblivious to new and better ways to do things that have appeared in the newer releases of the technology.

Take measuring log space, for example.  If I wanted to find out how much log space has been utilized, I would dash off a DBCC SQLPERF(LOGSPACE) statement.  But SQLPERF(LOGSPACE) only shows used/free space and not much more.  I might use DBCC LOGINFO to see how the active log “moved across the ldf file”, then examine the values columns such as “active” for more understanding.  Or I could go even more old-school, and look at the PerfMon counters or perhaps at the PerfMon counters exposed through the system table sysperfinfo.

However, I want to thank Tibor Karaszi and Gert Drapers for this great tip.  Simply use this query:

SELECT SUM([Log Record Length])

FROM ::fn_dblog(null, null)

WHERE…

The ::fn_dblog pseudotable provides a wonderful amount of information about the transaction log. Read more about this undocumented function at http://www.novicksoftware.com/udfOfWeek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm, or google ‘::fn_dblog’ for lots of other sources of information about it.

Thanks,

-Kevin

Technorati Tags: transaction log

Here’s the question in its entirety, so you get to see all of the details:

I have two computers running sql server, sqlserver01 and sqlserver01. On sqlserver01 I backup the database locally to C:\SQLBackup\backupfile.bak (db size: 150GB) then I copy and paste the *.bak file to sqlserver02 via the network. It finishes copying but when I try to restore it on sqlserver02 it fails. I have used tools like Eseutil from exchange but no luck. It seems like the bak file corrupts on the transfer.

What is the best way to copy over a 150GB *.bak file to another server then restore the *.bak file in a timely manner?
-end of question-
OK, to answer this I’ll need to actually throw out some other questions about this scenario.

Since this is to be timely, I would argue that this situation would benefit greatly from a tool that gives offers compressed backups. Compression can reduce the 150GB backup file down to 15-30GB, greatly improving copy speed.

But that does not address the real issue here. In fact, there should be no problem copying the 150GB file across the network to another location. It may be time consuming copying the file, depending on network speed, but this should work fine.

One question I have is if this is occurring while performing a normal Windows COPY or are if something like FTP is being used. If FTP, make sure a binary transfer is being performed.

Specific error messages weren’t given, so I would wonder if any are received or if the two instances are running the same version of SQL Server. Verifying that you can restore the database to the same instance from where it was created would be good to find out.  You can restore it to a new database name to avoid overwriting your original.

The other option available, again depending on network speed, is to back up the database to the network rather than to the local drive. A compressed backup would be a lot faster here as well.

If you want to, please provide some additional details of the error and your environments - Operation System versions and SQL Server versions.

Thanks.

–
David

Technorati Tags: .bak, Backup, backup and recovery, compression, database instance, operating system, restore, sql server

Microsoft Operations Manager (formerly MOM, now SCOM) has gained some traction in Windows shops for monitoring servers. I like it a lot for Windows, especially IIS and Sharepoint, but out of the box, it’s not that useful for DBAs. The default set of SQL Server alerts throw a lot of false alarms on things I can’t change, and it ignores the things I really want to know.

I haven’t seen a good howto article that helps with SQL Server monitoring with MOM - until now. Tom LaRock, who moonlights as SQLServerPedia’s Monitoring Editor when he’s not fighting crime, wrote an in-depth article for Simple Talk:

Operations Manager: A Big Tinker Set

Technorati Tags: microsoft mom, mom2005, operations manager, scom2007

I’ve been asked quite a bit lately about which of the new features of SQL Server 2008 are available in the standard edition.  Rather than going feature by feature it is easiest to link to the MSDN article that includes a detailed feature breakdown.  As you’ll notice, most of the new and exciting features are enterprise edition only (isn’t that always the case) but those standard users also have some features to be excited about.

Here is the link:
http://msdn.microsoft.com/en-us/library/cc645993.aspx

Technorati Tags: SQLServer 2008

This year at the PASS Summit in Seattle, Quest Software is sponsoring an expert panel event on automating SQL Server processes. Sounds boring, right? Think again - here’s when you need to check out process automation:

• You can’t hire help (either no budget or no available people) and you can’t keep up
• Your number of servers keeps growing
• You don’t know if every server got backed up in the last hour
• You don’t have an index maintenance plan for rebuilds & defrags

I’m always amazed that Microsoft SQL Server has been around for so long, but there’s very few standardized automation scripts. So many of us have our own duct-taped SQL Server backup T-SQL scripts, have disdain for the built-in maintenance plan approach, and would never think to Google for an automation script instead of rolling our own.

The experts with their fancy opinions:

• Allen White, SQL Server MVP and Trainer for Scalability Experts (moderator)
• Kevin Kline, SQL Server MVP and Technical Strategy Manager, Quest Software
• Buck Woody, Program Manager, SQL Server, Microsoft
• Dan Jones, Product Manager, SQL Server, Microsoft
• Thomas LaRock, Database Engineering Lead, ING Investment Management
• Charley Hanania, Production Product Owner, SQL Server, UBS
• Brent Ozar, SQL Server Domain Expert, Quest Software

Why am I on the list? Because I, for one, welcome our new robot DBA overlords.

When: Thursday, November 20, 2008
7:00 - 8:30 a.m. (breakfast served until 7:30)

Where: Seattle Convention Center
Room 613 (6th floor)

If you want the free eats, you gotta RSVP online for the event.

Technorati Tags: pass, pass summit

This is a very general question that someone could certainly write a book on but I’ll go at answering it by providing a few options and explaining the pro’s and con’s of each solution.  The following list is by no means comprehensive.

The first question you need to ask yourself before going on this journey is where does the encryption need to occur and who are you protecting the data from.  These two questions will drive the amount of effort and complexity required in building a solution.

1)  In database encryption.  The database is encrypted in the database and is viewable to no-one unless they have access to the encryption key.  The data could be encrypted at the application level by first running the data through an encryption algorithm before inserting into the database or the data can be encrypted using SQL Server 2005’s column level encryption or SQL Server 2008’s transparent data encryption.  Using in database encryption is the most secure option in that it can be leveraged to protect sensitive data from anyone that gains access to the database engine itself (whether valid or invalid access) however it has significant performance and scalability caveats.  Encrypting at the application level basically ensures that SQL Server will not be able properly index the data and therefore query perofmance may be dreadful with large data sets.  Leveraging column level encryption or TDE also will introduce query processing overhead and adversely affects the performance of your queries but not to the same extent.   Another drawback of In Database encrption is that it renders backup compression (whether through SQL Server 2008 or a third party backup product or winzip) obsolete.  Encrypted data is not compressible, therefore the size of your encrypted database will roughly equal the size of your backup files regardless of the compression technology used.

2)  If you do not need to secure data inside of the database but want to secure data once it leaves the database, a good solution would be to leverage a third party backup solution such as LiteSpeed.  These solutions will compress and also encrypt backup data in memory so that it is secured as soon as it leaves the database.  This is an attractive option to companies that store backups on network devices and are worried about internal or external users gaining access to network resources and therefore having direct access to backup files.  If you think that native backup files are secured, try opening a small database backup file in notepad.  You will see that all of the data is stored in plain text.  If you have a copy of pubs lying around from the SQL 2000 days you can back it up, open the backup file in notepad, search for “Smith” and you will see Smith’s social security number right there.  This method of database encryption will not secure the data in the database, so anyone who gains database access has free reign on your data, but thats what DBA’s are for right???

3)  A third option, and the last that we will discuss here, is to leverage encryption at the tape level.  Most organizations take their disk based backups and eventually migrate them to tape, where the backups files are then encrypted and eventually moved to offsite storage.  This should be a bare minimum for any SQL Server as once a tape leaves offsite anything could happen to it.  There have been many major news stories recently discussing companies that have had backup tapes lost or stolen and without leveraging at a minimum the tape systems encryption, that data is available to anyone that may “find” a backup tape.  Tape level encryption does nothing to protect the database info inside of the database or the backup data inside of a companies network, but it does protect the data once it is transported offsite.  The nice advantage of this as well as the previous option is that they impose no overhead on standard database workload, they simply may add some overhead to your backup time depending on the level of encyption that you are performing.

Hopefully this brief rundown of database encryption options has given you enough info to get started decideing which implentation strategy is best for you, and as always if you have any further questions, feel free to submit them.

Technorati Tags: Backup, Encryption, SQL Server 2005, SQL Server 2008

Jeffrey wrote in with a question: “How do you change the default location of a mdb file? Currently when I detach it is set for C: Drive path and I need to change it to D: Drive path. How can I change it?”

Database Default Locations

To change the location using SQL Server Management Studio, right-click on the server name in the object browser and click Properties.  Click on Database Settings, and there’s two paths for the default data file location and the default log file location.

Ideally, you want these files on something other than the system drive, which is probably what got you started changing locations.  When databases are created in the system drive, it’s easy to fill up the system drive accidentally.  Running that drive out of space causes all kinds of system issues.

If you’ve inherited a server with its master, model or TempDB databases on the C drive, those can be moved too - although it’s a little bit of work.  Details on how to do it can be found in Microsoft knowledge base article #224071, “How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server.” That name is a little deceiving - the KB article covers a lot of different options.  Make sure to read through all of them to find the method that’s right for your version of SQL Server and for the specific database you want to move.

I received a question this morning about whether there are any ways to estimate the memory requirements of executing SSIS packages?  In the case of the question, the emailer wanted to know how to do things like find out the data size at the source and at the destination of a data transformation, or how to reckon the memory consumption of a SSIS package of variable size.

It turns out there are several good sources of information for this question and for others like it.  First of all, I always recommend that you investigate http://www.sqlis.com, a site dedicated to Integration Services and supported by my MVP friends in the UK, Allan Mitchell and Darren Green.

Next, I googled for help on this specific question and came up with these resources:

1. http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx#EK

2. http://www.scalabilityexperts.com/default.asp?action=article&ID=167

3. And finally, http://msdn2.microsoft.com/en-us/library/ms137622(SQL.90).aspx, showing how to monitor the performance of the data flow engine in SSIS.

I hope this helps,

-Kevin 

P.S. Thanks to Wee Hyong for also tackling this question!

Technorati Tags: Memory

The full original question is:  How do you change the default location of a mdb file? Currently when I detach it is set for C: Drive path and I need to change it to D: Drive path. How can I change it?

First, thanks for the question submission!  You can detach the database, move the file(s), and reattach using the CREATE DATABASE… FOR ATTACH command.

This is a SQL Server 2005/2008 example. For SQL Server 2000, you would use sp_attach_db/sp_attach_single_file_db to perform the attach.

For example:

Detach the database during a maintenance window - make sure you have a backup just in case something goes wrong:

USE master;
GO
EXEC sp_detach_db <db_name>;
GO

Now move the database file to the D: drive and reattach:

– Execute CREATE DATABASE FOR ATTACH statement
CREATE DATABASE
ON (FILENAME = ‘D:\\’) FOR ATTACH;
GO

Keep the questions coming!  We get a large volume of submissions and love seeing what the community is thinking about. 

David

Technorati Tags: database administration, detached database, mdb, sql server, t-sql, tsql