Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server Mailbag: Asymmetric encryption, log shipping issues

In this edition of the SQL Server Mailbag, our expert tackles reader BI and data warehousing questions including encryption queries and the difference between SSAS editions.

In this edition of the SQL Server Mailbag, database expert Denny Cherry tackles reader questions on asymmetric encryption, hard disk defragmentation, the difference between SSAS in standard and enterprise editions and more.

Have a question of your own? Send it along to

Is there any chance to get high profile job after completing the MCSE exam conducted by Microsoft ? -- Sagermartha

Getting a certification will not by itself lead to money and a cool job; that requires a lot of experience. However, having a certification will help and make you look like a more attractive candidate to some employers. Some people like having employees who are certified, some don't care.

Personally I've got about a dozen Microsoft certifications. I have no idea if they help me get jobs or not.

Can asymmetric encryption in SQL Server 2005 be used to encrypt data on one database (DB) server and then be used to decrypt that same data from a second DB server after copying the data across?

For a conversion project, we are pulling legacy data onto one DB server and then copying it to a second server in order to transform it before we send it off to our target system. The encryption happens on the first server, but the decryption would be on the second server. It appears I can use symmetric encryption by passphrase, but I can't seem to get the asymmetric encryption to work. Ideally, I would only like to enter a password when I decrypt it. Otherwise I have to enter the password on both ends. -- John

If you want to decrypt data by a password, then you need to encrypt it via password. To make the process more automated, I would recommend encrypting the data with a certificate, exporting the certificate and then importing it into the destination SQL Server. Or, for a more transparent process, configure IPSec on the servers so that all data traffic between the two servers is encrypted. This way nothing needs to be changed on the SQL Server: The data is simply sent from SQL Server to SQL Server, and the operating system then encrypts and decrypts the traffic as it flows over the network.

If your NIC supports it, you can even offload this data encryption and decryption to the processor on the NICs instead of the CPU of the server, which will increase the overall system performance while running this process.

Previous mailbag editions

CALs, witnesses and unwanted changes

Stored procedures, triggers and SSRS reports

Should I be performing hard disk defragmentation -- not the SQL index defragmentation but a physical operating system defrag -- on the .mdf and .ldf volumes?

In the 10 years that I have worked with SQL Server, I have never heard of this, but a developer recently told me the reason his stored procedure is running slowly is because of the physical HDD fragmentation. When we did this in the past, there was little or no performance increase -- it just increased the downtime. -- Phillip

Physical hard drive fragmentation typically won't get you a whole lot of performance benefit -- especially when you are working in an environment that has a very high buffer cache hit ratio. (If your disk subsystem shows that, then you are getting a very quick response back from the disk array.) If a stored procedure is running slowly, it is usually because of incorrect indexing or statistics being out of date and the SQL Server generating a poor execution plan.

I have setup log shipping on our production SQL Server to send the logs to a server I have at my home for a warm backup solution. The process seemed to work but then I had a problem on my secondary server - it seemed to stop working for a period of two weeks (I dont know why - I could not login to it and had to power it off to reset it). I didnt notice until I did a random check.

My question is, will log shipping survive if the receiving server goes offline for two weeks? When I bring it back online, does it go and collect all the logs it hasnt yet seen and RESTORE them correctly? The reason I ask is because I am now seeing errors saying "cannot be restored due to a 'gap in the log chain'." How do I solve this issue? (I'm running SQL Server 2005). -- Lorelai

It will survive short gaps. The length of time it will survive is controlled by the amount of time the system keeps the transaction logs on the primary servers hard drive. Once they are deleted from there they are gone. You'll need to do a full backup and restore it to the backup machine then the log shipping can work again.

While using Spotlight on SQL Server 2005 standard edition, the following message appears:

Current network throughput is averaging greater than 90% of the theoretical maximum bandwidth.
Is there something I need to tell our server builders (hardware team)? -- Chris

Check to make sure that the network ports are each running at 1 GB. If they aren't, then make sure they are 1 GB capable and that the switch port is a 1 GB switch. If not, you may want to look at a hardware upgrade (this won't be cheap if the network switch needs to be upgraded). If you hardware supports it, you can look at teaming the NICs, which may require additional drivers from your server vendor.

Are there any functionality or capability differences in SQL Server 2005 Analysis Services standard edition versus the enterprise edition? It appears most of the additional features in published comparisons are associated with the database itself. -- Bill

The two editions have a few different features. Some of the features that are only in the Enterprise SKU include: Proactive Caching, Full Writeback Support, SSIS Data Flow Integration, and Text Mining. For the full list features, check out the business intelligence section on the Microsoft website.

I am a SQL Server Reporting Services rookie. I put together a report to get information on the usage of parts, and I am looking for things that have not been used since. I have two parameters with calendars and I have the default value non-queried as =Today. I want the default to be one year back from today so the user is closer to the date they need to pick on the calendar. -- Bob

Use this code:

"=dateadd(DateInterval.Year, -1, Today())"

Have a burning SQL Server question of your own? Let us know at, or pose it to our IT Knowledge Exchange forum for fast responses from your peers.

Denny Cherry has over a decade of experience managing SQL Server, including's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.
Check out his blog: SQL Server with Mr. Denny

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.