SSMA bug watch

The SQL Server 2005 Developer Edition installed on my Windows XP SP3 laptop had crashed over the weekend, so after a fresh reinstall including SP2 with hotfixes up to version 3073, I decided to re-run my SQL Server Migration Assistant for Access (SSMA) to create a new conversion file for testing.

The application performance was extremely poor, so I tried running SQL Profiler to try to find the bottlenecks, but I was seeing reads in the range of 20-50 which seemed trivial, however the Audit Login and Audit Logout events were literally taking minutes instead of seconds. I had saved the trace to a table, so I copied some of the SQL statements into SSMS and they ran very quickly, each barely taking a second.

I did a bit of research on connection pooling, but unfortunately that .NET option is not available in either SQL Server 2005 or MS-Access 2003. So why was Access connecting & disconnecting for every single database access?

Then I looked into the Access app and discovered that the SSMA was using the "SQL Server" provider in the connection strings. I changed that to the "SQL Native Client" and voila! the app performance sped up by a factor of at least 10x. Overall, the performance is now marginally slower than when using the native Access tables. That's fine, because this database will ultimately be running on a real Windows 2003 server with four dual-core CPU's and mucho RAM, waaay more powerful than the puny Centrino in my laptop.

I also took a couple of miscellaneous Access files & converted those as a further test, and they all converted using the old "SQL Server" intended for SQL 2000.

I've always run the SSMA using the wizard to save time, but now I'll need to revisit that to see if there is an option to use the correct provider for SQL 2005.