Monday, January 19, 2009

The SQL Sentry Console is now 64-bit capable

I wanted to take a departure from the language-based posts I’ve done in the past and relay some information regarding the next release of our product. Starting with the next point release of our software (currently slated to be 4.3) The SQL Sentry Console will be able to run in native 64-bit mode. The reason for this blog post is to describe some of the reasons why this is available now and has not been in the past, as well as what it means to the end-user.

SQL Sentry has two components, the SQL Sentry Console, and the SQL Sentry Server Service. Each one of these talks to a number of different systems and uses many supporting dlls/assemblies to provide support for external systems. The SQL Sentry Server Service has run natively in 64-bit mode for some time now. The console, however has been forced to run in 32-bit mode. The reason for this is that in Windows, any 64-bit process can only load 64-bit dlls. You can’t mix and match 64-bit and 32-bit code.

SQL Sentry was conceived when SQL Server 2000 was the dominant SQL Server version. As such we decided early on to support reading SQL Server Enterprise Manager registrations into the console using SQL-DMO. We also found we could (with some work) show Enterprise Manager property windows and dialogs using the SQL-NS library. Both of these libraries were COM libraries, and since SQL Server 2000 was written at a time when 32-bit was your only OS option, the COM libraries were 32-bit. Eventually Microsoft added support for 64-bit operating systems in SQL Server 2000, but the client tools, running in 32-bit mode anyway, had no need to get these updates.

Because the console linked directly to these dlls to support reading registrations and showing SQL Server 2000 dialogs, the console was forced to run in 32-bit mode, lest it not be able to load the 32-bit dlls. This wasn’t a major issue to do, as it’s easy to flag an executable to run in x86 mode (vs. the default automatic detection mode for managed applications) by just setting that as a compilation option. It worked and that’s how SQL Sentry has been able to interoperate with SQL Server 2000 until now.

In 4.2 we wanted to support reading SQL Server 2008 registrations. This proved to be quite an engineering task as we found that Microsoft changed many of the APIs used to read in this registration data. Because of this we decided to go back to the drawing board and reengineer the way we read in registration information. As stated previously, before we linked directly to the registration COM objects (for SQL Server 2000) and SMO assemblies (for 2005). For 4.2 we elected to use an intermediate layer. We abstracted all the functionality we needed into a set of interfaces that we could read/write to, and then created plugins, one for 2000, one for 2005, and one for 2008. That allowed us to independently manage all the plugins and their corresponding references and decouple their implementations from the console. It was an undertaking but it paid off, because we have a much better separation of code layers, and we also will be able to support the next version of SQL Server without any major work.

Around the time when I’d looked at the feasibility of making the console work in 64-bit mode I quickly realized that doing so would require such a decoupling, because I had to get the 32-bit dlls out of the console process. It just so happens that the work done for 4.2 did just that.

In 4.3 the default build will not have the 32-bit flag in the manifest, so it will launch using whatever architecture the processor/OS supports. The side effect of this is if you are using the SQL Sentry Console on a 64-bit system and running it in 64-bit mode, you will be unable to read in SQL Server 2000 registrations, because it will not load the 2000 plugin (if it did it wouldn’t work due to the 64-bit/32-bit process/dll interoperability issue). The workaround for this is that we’re also shipping an x86 SQL Sentry Console that is flagged to run in x86 mode. This console will only be installed on 64-bit operating systems. If you install SQL Sentry on a 64-bit OS starting with 4.3 you’ll see two executable shortcuts in the start menu:

SQL Sentry Console and
SQL Sentry Console (x86)

If you require interoperation with SQL Server Enterprise Manager registrations, you should run the x86 version, otherwise, run the regular SQL Sentry Console, which will run in 64-bit mode on a 64-bit OS.

No comments: