SQL Server with Mr. Denny


August 4, 2008  11:00 AM

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



Posted by: Denny Cherry
Config, Encryption, IPSec, OpenSSL, PGP, Secure Data Transfer, Secure FTP, SFTP, SQL

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



Posted by: Denny Cherry
Andew Fyer, Article, Brent Ozar, Brian Kelly, Chris Shaw, Christian Lefter, Interview, Namwar Rizvi, Pinal Dave

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



Posted by: Denny Cherry
RDL, SQL Server 2005, SQL Server 2008, SSRS

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



Posted by: Denny Cherry
BIDS, Integration Services 2008, SQL Server 2008, SSIS

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



Posted by: Denny Cherry
Back To Basics, BACKUP DATABASE, SQL, SQL Server 2000, SQL Server 2005

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


July 21, 2008  11:00 AM

A better way to index text data



Posted by: Denny Cherry
Index Performance, Query tuning, SELECT statement, T/SQL, Tables, Uni-code

Indexing text data (varchar, nvarchar, char, etc) is a good way to make it faster to find the data you are looking for.  However these indexes can end up being very hard on the disks behind the index, as well as the memory of the server.  This is because of the large amount of data being put in the index.

As an example, let’s say that we have a table like this.

CREATE TABLE Employee
(EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
EmailAddress VARCHAR(255))

Now assume that you want to be able to search by the EmailAddress field.  We will then want to index the EmailAddress field with a non-clustered index.  If we work for a company like AMD, then our email addresses will be pretty short (f.lastname@amd.com).  However if we work for a company like I work for then the email addresses are a bit longer (flastname@awarenesstechnologies.com).  Now when we index this column we will be putting the entire email address into the index, taking up a lot of space within the index; especially compared to a numeric value such as an integer.  This becomes doubly true if you are using a uni-code data type as each character requires two bytes of storage instead of the usual one.

This also becomes a problem if you are working on a system with URLs in the field to be indexes.  Depending on the length of the URL, the values may be longer than is allowed in an index which could then give you sorting problems on the indexes.

There are a couple of variations on this technique which I’ve seen.  The one I’ve used the most is to use the CHECKSUM function as part of a calculated column, and then index the calculated column.  This way you simply get the CHECKSUM of the value you want to find, and search the calculated column.  As we are now have an index made up of integers the index can fit a lot more data on each physical data page reducing the IO cost of the index seek as well as saving space on the disk.

So doing this turns our table into something like this.

CREATE TABLE Employee
(EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
EmailAddress VARCHAR(255),
EmailAddressCheckSum AS CHECKSUM(EmailAddress))

Now I wouldn’t recommend using this technique for each table you create.   I usually only recommend a technique like this when the value to be indexes won’t fit within the bounds of the index, or the table will be very large and searched often so the memory saved is worth the extra CPU time of having to hash the values before doing the lookup.

Now there are a couple of gotchas with this technique.  If you are check summing domain names, some characters don’t check sum correctly.  Also check summing a Unicode version of a string will give you a different result than the non-unicode version of the same string.

You can see that with these three SELECT statements.

SELECT CHECKSUM(‘google.com’), CHECKSUM(‘g-oogle.com’)
SELECT CHECKSUM(‘google.com’), CHECKSUM(N’google.com’)
SELECT CHECKSUM(N’google.com’), CHECKSUM(N’g-oogle.com’)

As you can see the first one you get two different values as you would expect ( 1560309903 and 1560342303 respectively).  With the second query you get two very different values between the Unicode and character strings (1560309903 and -1136321484 respectively).  Based on the first query you would expect to get two different values for the third query, but you don’t.  With the Unicode strings the – appears to not count as part of the CHECKSUM giving you the same CHECKSUM value for both strings (-1136321484).

Another version of this technique which Kevin Kline talked about recently uses the HASHBYTES function of SQL Server 2005 to get the hash of a column and use that.  In his blog he’s talking about using it for partitioning a table, but that same technique can be used here as well.

CREATE TABLE Employee
(EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
EmailAddress VARCHAR(255),
EmailAddressCheckSum AS HASHBYTES('SHA1', EmailAddress)

This will however give you a longer string, therefor taking up more space within the index. However if working with long Unicode strings this may be a better option for you to use.

Denny


July 17, 2008  11:00 AM

Book: The Windows Manager’s Guide to iSCSI SANs



Posted by: Denny Cherry
Exchange, Microsoft Windows, SQL, SQL Server 2005, SQL Server 2008, Storage

That’s right I said a book.  Well it’s actually an eBook but good enough right?  The best part is that it’s available for free.  It’s a three chapter eBook called “The Windows Manager’s Guide to iSCSI SANs”.  Each chapter was posted separately so you have to click through to each one.

Registration is required to view the chapters (don’t blame me).  I wrote chapter 2, I hope you find it useful.

Chapter 1: Leveraging SANs in Windows Servers

Chapter 2: Gearing up for SANs on a SQL Server

Chapter 3: Reducing Exchange Server Complexity with SANs

Denny


July 14, 2008  11:00 AM

Using batching to do large operations without filling the transaction log



Posted by: Denny Cherry
DELETE statement, Query tuning, SQL, Tables

Deleting large amounts of data from a table is usually an important task, but if you don’t have a maintenance window to work within then you can end up causing locking and blocking against the other processes which are accessing the database.  Not to mention you will cause a very large amount of data into the transaction log no matter what your transaction logging level is set to.

Say you have a table with a date column and you need to delete a million plus records.  Doing this in a single transaction will put all million transactions into your transaction log, plus cause any other processes which are trying to access the table to be blocked.

 However if we batch the transaction into many smaller transactions our transaction log will not fill up as we can backup the log using our normal log backup methods throughout the process, or if we use SIMPLE recovery on our database then transactions will be removed from the log automatically.

In SQL 2000 and below you have to set the ROWCOUNT session variable to a number above 0, which would cause SQL to delete the first n records that it comes across.  In SQL 2005 we can use the TOP parameter as part of our DELETE command having the same effect, but without having to reset the session variable.

In SQL 2000 or below you can use a syntax like this one.

DECLARE @c BIT, @d DATETIME
SET @d = dateadd(yy, -1, getdate())
SET @c = 0
SET ROWCOUNT = 1000
WHILE @c = 0
BEGIN
DELETE FROM Table
WHERE CreateDate

If you are using SQL 2005 you can use this very similar syntax.

DECLARE @c BIT, @d datetime
SET @d = dateadd(yy, -1, getdate())
SET @c = 0
WHILE @c = 0
BEGIN
DELETE TOP (1000) FROM Table
WHERE CreateDate

Both pieces of code are very similar. Declare a variable which tells the loop when to exit. Then start deleting the data. If no records are deleted, then set the variable to 1 causing us to exit the loop. Now this will usually take a little bit longer to complete than a single delete statement, but the system will continue to be responsive during the process.

The number of records which you are deleting should be adjusted based on the width of your records, the load on the database at the time of deletion, and the speed of your hard drives which hold the data files and transaction logs. I usually start at 1000 records and see how the system responds. For tables which a just a few numbers I'll put it up as high as 50k or 100k records. For very wide tables I'll drop it down to 100 or 500 records if the system can't handle 1000 records.

Denny

Update:
Sorry this post didn't look very good at first. The blog site seams to have eaten it, and I didn't notice until just now.


July 10, 2008  11:00 AM

My Experience with putting a CRL Procedure into SQL



Posted by: Denny Cherry
C#, CREATE ASSEMBLY, CREATE PROCEDURE, Encryption, Microsoft.SqlServer.Server, SQL CLR Procedure, SQL Server 2005, SQL Server stored procedures, T/SQL

A little while ago I came upon a situation where I needed to put a CRL procedure into SQL Server 2005.  Now I’ve been using SQL Server 2005 since the day it went RTM, and this is the first occasion that I’ve needed to put a CLR procedure into SQL.  Could I have done what I needed to without a CRL procedure?  Yeah probably, but the .NET code was written, and had been working.

My code is written in C# as that’s what the .NET guys here use.  There is one method within the C# class called HashPassword.  It takes a text string and makes a SHA1 Hash of it which we then store.  We had to make a couple of changes to the C# code to make it work within SQL Server.

The original code looked like this.

using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Text;
 

namespace Rapid.Database.Security{
public class User
     {
     public static void HashPassword(String password, out String hash)
          {
          SHA1 sha1 = new SHA1CryptoServiceProvider();hash = 
          BitConverter.ToString(sha1.ComputeHash(UnicodeEncoding.Default.GetBytes(password.ToLower()))).Replace("-", "");

          }
     }
}

The changed C# code looks like this.

using System;
using System.Collections.Generic;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using Microsoft.SqlServer.Server;

namespace Rapid.Database.Security

{
public class User
     {

     [SqlProcedure(Name="HashPassword")]
     public static void HashPassword(String password, out String hash)
          {
          SHA1 sha1 = new SHA1CryptoServiceProvider();hash = 
          BitConverter.ToString(sha1.ComputeHash(UnicodeEncoding.Default.GetBytes(password.ToLower()))).Replace("-", "");

          }
     }
}

Once these changes are made and the DLL recompiled we can attach the DLL to the SQL Server database as an assembly.  This is done with the CREATE ASSEMBLY command.  I have to use the EXTERNAL_ACCESS flag instead of the SAFE flag because my dll requires the use of the System.IO assembly which can’t be run as SAFE under SQL Server 2005.

CREATE ASSEMBLY [Rapid.Database.Security]AUTHORIZATION [dbo]
FROM ‘D:\Rapid.Database.Security.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

We can now create our procedure which needs to have one input and one output parameter to match the number of parameters within the C# procedure.  This is done with the CREATE PROCEDURE command just like creating any normal procedure.

CREATE PROCEDURE [dbo].[HashPassword]
@Password [nvarchar](50),
@PasswordHash [nvarchar](50) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Rapid.Database.Security].[Rapid.Database.Security.User].[HashPassword]
GO

The three part name of the C# method are:

  1. Assembly name which SQL Server knows about (basically the name of the DLL without the file extension).
  2. The full name to the class, in our case the namespace then the class.  Our namespace is Rapid.Database.Security with the name of the class after that.
  3. The name of the method within the C# class.

Don’t forget to enable CLR within SQL Server using the sp_configure before trying to use the CLR procedure.

 At this point I am able to run the procedure and pass in text and get back the hash.

declare @PasswordHash varchar(50)
exec HashPassword @password=‘test’, @passwordHash=@PasswordHash OUTPUT
SELECT @PasswordHash

The value of this is: A94A8FE5CCB19BA61C4C0873D391E987982FBBD3

I hope that you find this useful. It took me and a .NET developer a couple of hours to get all this working correctly and loaded into the SQL Server correctly. I know that I’ll be referring back to this if I ever need to attach a CLR method into SQL again.

Do keep in mind that when we were doing this, we created this as a separate DLL for the SQL Server as to get the original DLL into the SQL Server, SQL wanted us to load up a bunch of other assemblies that some of the other methods required.

Denny


July 10, 2008  11:00 AM

SQL 2008 RC0 Intellisense doesn’t work against SQL 2005



Posted by: Denny Cherry
Beta, Intellisense, SQL, SQL Server 2005, SQL Server 2008

I’ve been working with SQL 2008 RC0 for a couple of weeks now, and something I’ve noticed that really sucks.  The Intellisense doesn’t work against a SQL 2005 server any more.  I’ve been using the SQL 2008 UI exclusively for several months now (since the November CTP at least) and I’ve gotten quite used to the intellisense.

While jumping around the net looking to see who else was talking about this (and several people are) I found this Connect bug about it.  The notes from Eric @ Microsoft are that if the vote count shows that enough people want this fixed they’ll see what they can do.   The comments in the Connect bug indicate that pretty much everyone wants it back, even if it’s not perfect.

The only other person I really see complaining about the lack of downlevel support is Aaron Bertrand.

Be sure to vote on the connect bug and leave a comment here or there so that the developers get the idea that we want it back.

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: