Last week, I completed an upgrade of a SQL Server 2000 server to SQL Server 2005. Not really a big deal; servers are routinely upgraded to a newer version of software. Sometimes it seems the upgrades come too quickly. Other times it seems like we are waiting for the proverbial pot of water to boil while eagerly looking forward the next release.
This server, however, included a Notification Services instance. It got me to thinking about upgrades and some of the changes in SSNS between v2.0 and 2005. I've compiled a "Top 10" list of enhancements to SSNS 2005. (I wish I could have composed the Top 10 list for SQL Server 2008, but alas it's not there. I'm sure we're all aware by now of the its premature deprecation. That'll be the topic of another blog.)
Number 10. Support for the 64-bit platform.
As an add-on for the prior version of SQL Server, SQLNS was not supported on a 64-bit installation. In 2005, it is.
Number 9. Hostable execution engine.
With the newer version, each SQLNS instance may be hosted within an existing application or process; it no longer requires that a NS$InstanceName service be created.
Number 8. Better vacuuming performance.
The vacuumer now transverses through the obsolete data in a much more efficient manner.
Number 7. Analysis Services Event Provider.
You may now use MDX queries to collect event information.
Number 6. Management API.
The Microsoft.SqlServer.Management.nmo API may be used to programmatically create and manage SQLNS instances.
Number 5. Database independence.
The database objects historically created in the instance and application databases may now be created in a database that we specify during the build process.
Number 4. Subscriber defined conditions.
Under certain circumstances, subscribers may define query clauses over a pre-defined dataset.
Number 3. Management Studio integration.
SQLNS is now a full-fledged part of SQL Server and it\u2019s been integrated into Management Studio.
Number 2. New views.
There are new views in SQLNS 2005 we can use to create and manage subscribers, devices, and subscriptions using T-SQL. (Click here to see a prior post on this subject.)
And the Number 1 feature. Goodbye Notify() function!
A new view allows us to write T-SQL code in our matching rules which is not only more intuitive, but also offers improved performance. (Click here to see a prior post on this feature.)
And there you have it. My Top 10 List of new and greatly appreciated features in SQLNS 2005.
As for conducting the upgrade, here are a couple of resources to help.