As I continue to explore how 2008 will affect an application upgraded from 2000 and 2005, one question that I get almost consistently every week is “should I upgrade from 2000 to 2005 first or jump directly to 2008?”. Sure, both are supported and of course, there is no shortage of opinions and recommendations from Microsoft and 3rd parties.

One of the better 3rd party write-ups on the subject is from Steve Jones who recommends users to skip 2005 and move straight to 2008. I’ve known Steve for a while though we typically only catch up at conferences (TechEd, PASS, etc…). Still, he’s been working with SQL Server a long time and knows what he’s talking about so it’s worth taking a peek at his article on SQL Server Central.

Of course, Microsoft too has a couple of papers on the subject that are worth reviewing. While they may sound like a gentle nudge towards upgrade to 2005 first, some of the reasons are good ones. There’s the original “Why upgrade to SQL Server 2005” paper which is ok if you really are brand new to 2005. While quite dated, the info is still relevant. The newer “SQL Server 2005 Upgrade FAQ” has the latest and greatest information including a short section on the benefits of moving to 2005 first then 2008 later. It’s a good start but IMHO, doesn’t provide enough to help the average user decide.

I really don’t believe there is a hard rule for this; it really depends on each individual deployment (yes, deployment not company). Here are a few important points to consider when trying to decide whether to first upgrade to 2005 then on to 2008 or do both in 1 step. I’ve laid them in in pros/cons format to make it easier to get most folks started but the right approach really depends on your specific deployment and what’s important to you.

Note the the lists is not ordered or weighted in anyway. They are just intended to get folks thinking about some of the issues involved. For example, testing efforts for a 1-step upgrade might be significantly more involved than a 2-step approach. This is because there are (a small number of) deprecated features that are fully de-supported/removed from SQL Server 2008. If you had taken a 2-step approach, you would have some time to setup alerts (use of unsupported features/command will fire an event create an entry in the SQL Server log) and phase them out gradually. With the 1-step upgrade, you’d have to hunt down every line of code including dynamic SQL to be analyzed by Upgrade Advisor or run through the Application Compatibility Testing tool.

Not purely an IT decision.
Sure, the DBAs and application admins are intimately involved since they need to handle the testing and deployment. However, there’s a fair bit of learning involved especially if you want to take advantage of Policy Based Management (aka DMF) so there’s the first involvement from the business side - training budgets. Still, the most important points, IMHO, from a business perspective are the downtime window and regulatory compliance. I’ve worked with some customers where extended downtime is schedule up to 2 years in advanced. This doesn’t leave much room for doing a lot of upgrade operations so you might have to just bite the bullet and make one big jump. On the other hand, if there are specific regulations that require you to run only products that have mainstream support, you’ll likely have to get to 2005 asap (2008 will not be available till summer).

The SP1 rule
One important point I’d like to make is about SP1. I know of a lot of companies that have this rule - no RTM deployments, wait for SP1. I used to subscribe to this rule also, a very long time ago when I helped manage some Oracle DBs and in my early SQL Server days. As of 7.0, this SP1 rule really made less sense with every release. My rationale is simple. Beta versions of SQL Server have been running in production environments for SAP customers and a bunch of Microsoft’s own internal applications (its own SAP and other business critical applications) since 7.0 and the practice continues today. If you know anything about SAP, you’d know running it in production on a beta database is no small feat regardless of how many modules you run. I don’t know of any other enterprise database that does this. So if multi-billion dollar companies can run their very complex business critical applications on beta versions of the product, I’m pretty comfortable running the released version.

If you still can’t decide after all this, do a POC. Download a copy of the AppCompat Tool, aka SSUA for SQL Server 2008 (currently slated for March release) and do your own application compatibility testing. That should give you a good idea of the effort it’ll take to upgrade your database/application which hopefully, puts you in a place to make the right decision on which path to take.

In short, there are no easy answers and your decision should not be based on features alone. Though the most common response to this question is “do you need the new features”, it is hardly the most important one. Most features can be worked around with some creative code or 3rd party applications. Downtime windows and regulatory compliance requirements are generally very inflexible. Of course, your environment might be such that neither of these apply so all you really care about are the new features. Like I said from the start, it depends on each individual deployment.

joe.

As Joe describes in his post ‘Upgrading to SQL Server 2008: The Next Version of SQL Server Upgrade Assistant’, Microsoft made available a very handy utility called the Upgrade Advisor (UA) to assist customers in assessing their existing SQL Server 2000 environments for upgrade to the SQL Server 2005 platform. With the arrival of SQL Server 2008 we also have access to the updated UA utility that supports analyzing both SQL Server 2000sp4 and SQL Server 2005sp2 installations (which are the supported platforms for upgrade to SQL Server 2008). The UA utility is applicable to both the relational engine as well as the SQL Server Business Intelligence (BI) components (Integration Services|Data Transformation Services, Analysis Services, and Reporting Services).

*The UA utility requires the .Net Framework 2.0 redistributable

In our testing of this utility based on the November 2007 CTP build of Katmai (CTP5) we found that the UA will allow the analysis of both local and remote instances of the BI components excluding Reporting Services. Reporting Services instances will require the UA to be installed on the report server itself. Another discovery we found from our tests is that in the CTP5 build the Reporting Services analysis does not seem to be working as designed. Integration Services is not yet supported in UA, however the tool does note that in a future CTP/build of Katmai UA will support analyzing SSIS. In addition, SQL Server 2008 will continue to support the Data Transformation Services (DTS) runtime and thus the UA allows you to analyze DTS Packages in both a SQL Server 2000 and 2005 environment. A nice bonus is the ability to analyze DTS packages stored in the file system as well. Also note that when you analyze a SSAS 2000 instance the UA utility will require the Decision Support Objects (DSO) object model.

*DTS Packages are an official deprecated feature in SQL Server 2008. You should migrate your organization’s DTS packages as soon as possible to help support future upgrades of your SQL Server environments.

The UA utility provides you with both analysis and reports. UA reports provide you with the alerts/warnings that you should consider or take action on prior to upgrading your SQL Server environments to the 2008 platform. A nice feature of the UA utility is its ability to provide the user with links directly to its alert documentation. Some of the more important UA alerts are as follows:

SQL Server Data Transformation Services UA Key Alerts

· SQL Server DTS Packages are now a deprecated feature

· To continue running and managing your existing DTS Packages, the SQL Server 2008 Backwards Compatibility Files are required (these files were required for the same reason in SQL Server 2005 platform)

· The DTS Designer Components are a required add-on if you wish to edit DTS packages in Management Studio (these files were also required for the same reason in SQL Server 2005 platform)

SQL Server Analysis Services UA Key Alerts

· VBA functions handle NULL values and empty values differently from SQL Server Analysis Services 2005

· Custom Aggregations are not migrated

· ODBC Connections are not migrated

· MDX Changes

SQL Server Reporting Services UA Key Alerts

· Custom Reporting Services extensions (DLLs) will not be moved

· Client Certificates were detected (Reporting Services 2008 does not support them)

· Reporting Services 2000 Report Server database is on a remote 2000 instance (blocks a 2000 to 2008 SSRS upgrade)

Be aware that UA cannot analyze every aspect of your SQL Server environments and thus it cannot detect every issue that could affect an upgrade. Some examples of items not analyzed by the UA are middle-tier or front-tier code containing TSQL, encrypted stored procedures, and extended stored procedures. Regardless of the UA limitations, it is a very good tool for the BI professional to leverage for prepping their environments for SQL Server 2008.

Happy Upgrading!

Derek Comingore

 Business Intelligence: Platform Introduction [9:13m]: Download

SQL Server 2005 introduced native database encryption with the use of encryption functions. This allowed users to secure data within the database so that even admnistrators may be prevented from viewing the data. While that is a great addition, it was not a “flip the switch” implementation. Applications had to be modified to enable this feature (although it was pretty easy to do) and you had to deal with potential performance issues. The latter is the bigger challenge as you cannot index an encrypted column so you effectively end up scanning the table with an encrypted column.

This isn’t very appealing to users who truly need the encryption capability as the column they need to encrypt is often the column which they search by. A common example is a credit card company where the credit card number is the column they want to encrypt but that is also the  column they would search when customers access their accounts or when employees need to lookup a customer. When you have hundreds of millions of accounts and need to do thousands of lookups every minute, it’s a bit of a problem.

Katmai takes this a (big) step further with Transparent Data Encryption. The concept isn’t new and at least one other major database has it. Basically, think of having a layer between the storage  engine and your disks that will encrypt data before it is written to disk and decrypt it before being read into memory. Turn it on is a lot simpler than Yukon’s encryption too. You just need to create a database encryption key and set the database encryption option on. Note that your database encryption key needs to be protected by a certificate that is protected by the database master key. This ensures it can be accessed after a restart or recovery.

Unlike Yukon’s table encryption, you don’t have the same challenges with indexes like table level encryption in Yukon. The following is a simple example for encrypting your the pubs database and a query whose plan will show an Index Seek. Not very exciting in itself but considering the past encryption technique where indexes don’t even come into play, this is pretty cool.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘p@55w0rd’;
GO

CREATE CERTIFICATE DBEncryptKeyCert  WITH SUBJECT = ‘DB_Encryption_Key_Cert’;
GO

USE pubs
GO
– Note that not all operating systems support AES so choose an algorithm that is supported by your OS
– SQL Server supports most common algorithm like AES, DES, 3DES, RC4, etc… See Books Online for details.

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DBEncryptKeyCert;
GO

ALTER DATABASE pubs
SET ENCRYPTION ON;
GO

USE pubs
GO
SELECT  fname, lname, emp_id
FROM  employee
WHERE  emp_id=’A-C71970F’;
GO

Just pretend emp_id is credit card number. If you had encrypted the column emp_id (i.e. credit card number) and created the encrypt/decrypt function along with the view, triggers, etc… you’d be scanning the table just to retrieve that one row. That’s what you had to work with in 2005 (unless you had some 3rd party solution). Sure, there are several ways to reduce/avoid that but solutions typically involve some variant of storing a substring of the column (hashed or as-is) and indexing that column. Not a very viable solution for credit cards since the first 6 digits make up the credit card issuer’s identification number. A bunch of other concerns pop up and though not insurmountable technically, they do present challenges and in some cases, regulations may disallow this approach. No such concern with TDE in 2008.

So what about EFS? Well, just consider it as another option to be used as appropriate. EFS encrypts the entire file system while TDE encrypts just the database. Picking the right option depends on your transportability requirements and concerns. For example, if your company uses a centralized repository for storing ALL backups and you don’t trust those pesky Windows admins that own that repository, TDE ensures nobody can copy the backup file and restore on another SQL Server instance. It is also possible to use both but be aware of the performance and administration implications especially during recovery (EFS access then database access).

Upgrade Effort & Impact
What’s the effort to benefit from this? Well, from an execution perspective, just the few steps in the sample scripts above or you can use Management Studio. If you are upgrading a SQL Server 2005 database that is already using column level encryption, suggest you revert to plain text before the upgrade. Though they are different features, there really isn’t a need for using both. Same with those who are using 3rd party encryption products. That said, there are a few challenges in the upgrade process. For instance, you might not be allowed to store data in clear text to facilitate a simple upgrade. As such, you will need to upgrade first then reverse the process which you employed to encrypt a column using the function based approach. That means you’ll turn on TDE on the database then move the data from the encrypted column to a non-encrypted column so you can do away with the UDFs (and views if you had those). Bottom line is, this may mean a longer upgrade/migrate time window plus a longer rollback time also.

There are also other considerations to your production server. This is a database wide option and will impact other components such as the transaction log and tempdb. To ensure data is not stored unencrypted once you turn it on, the current virtual log file will be “zeroed out” and new transactions will begin on the new VLF in encrypted form. Your tempDB will also be encrypted. This means you will have performance overhead on the system initially (both CPU and disk) and alters user behaviour later (e.g. can’t snoop around in tempdb easily anymore). Another important consideration is backup. With TDE, your backups are also encrypted so make sure you backup your keys/certificates also. Else, in a disaster recovery situation, you will not be able to restore your database. These and other considerations are well documented in BOL so do review in detail.

From a planning perspective, think real hard about why you need to encrypt your database. If the only reason you can come up with is “to make it more secure”, talk to your friendly neighbourhood Microsoft engineer or trusted partner before turning this on. You might eventually still employ TDE or you may not even need it (but you’ll likely discover other security concerns). Remember, security is not just about technology. If you don’t educate your staff and users or fail to practice secure processes diligently, no amount of encryption will save your data. Flipping over keyboards and turning photo frames around on users desks STILL reveal passwords (last verified Sept 2007) and backup tapes can still be found on DBAs/operators’ desks or at the reception awaiting picking. TDE is just another layer of defense in your arsenal for specific intents and attack vectors; don’t forget the others.
 

joe yong.

Minor one but we’ve had a couple of ISVs encounter this.

 When you upgrade an existing SQL Server 2005 or 2000 database instance, the size of your “MODEL” database increases. For example, 2000 model is under 1MB while 2005 is just over. 2008 is about 2MB. The log file size increases also.

 Why is this worth noting? Well, most users will not be affected by this but if you create applications that run on portable devices whether it’s laptops or handheld devices and part of your application setup involves creating a database, you might care. Also, if your database is deployed in very tightly controlled environments as far as disk space consumption goes, you might want to care.

The problem arises when scripts are used to create these databases and like the good dev/DBA that you are, you include all the options you want including initial file sizes. If your scripts were created for a 2000 or 2005 environment and you set your minimum file size based on what you know the model DB to be in those versions, they will not work in 2005 since the file size of the MODEL database exceeds your definition.

This is not detected by Upgrade Advisor but the number of users affected by this is likely to be VERY low.  Users typically only hardcode a large file sizes (many good reasons to start big) in their CREATE DATABASE statements or leave it to the default (MODEL size). Few actually hardcode a small file size value.

If you do fall into this category, it takes under 30 seconds to fix but if you don’t, your upgraded application’s setup process will fail.

joe yong.