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

Published On: 2008-07-28By:

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

Contact the Author | Contact DCAC

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

Published On: 2008-07-24By:

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

Contact the Author | Contact DCAC

Back to Basics: The BACKUP DATABASE command

Published On: 2008-07-22By:

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

Contact the Author | Contact DCAC

A better way to index text data

Published On: 2008-07-21By:

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

Contact the Author | Contact DCAC
1 418 419 420 421 422 452

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link