Wednesday, July 29, 2009

Cluster is alive

Just an FYI, the SQL 2008 cluster has been alive for a week and a half now, and we have even done a maint window OS patch on it. Works like a champ.

I had to work through the normal SQL 2000 to SQL 2008 performance nightmares, poor or non-existent indexing (inherited databases ya know) and refreshing of stats along with the rebuilding of some key indexes.

All in all it wasn't bad. The worst part was waiting for the 300+ GB of data to go over a 1GB pipe to the new iSCSI array for the cluster. Everything else was cake.

I am now in the planning stages of building our second 2008 cluster for our data warehouse. There is far less data here and far fewer containers, but there are some key issues to grapple with performance wise.

As a comparison to my last clustering adventures (Windows 2000 server, SQL Server 2000 and a SCSI Dell Power Vault array) this has been quite refreshing.

It seems Microsoft has done a good job in improving the lot. After clustering I plan on taking on either log shipping or replication to completely redo how my company does reporting and to eliminate some holes we have in our disaster recovery plan.

Cursors, distriubted transactions and two versions of SQL Server

It is normally a great idea to use FAST_FORWARD cursors in TSQL if you must use a cursor because really we shouldn’t be modifying data in the cursor now should we? Seeing that cursors are quite possibly the worst constructs to use in SQL and all.

It has come to my attention that we have a couple of SPs that have cursors that do not specify what type they are. IF these cursors then go and create a Distributed Transaction, and within that transaction update data on a distributed server (via linked server) AND the remote server thinks that it is within a non-read only transaction you will get a rather odd error. The Calling server is SQL 2000 and the remote server is SQL 2008 both enterprise. The SQL 2000 server is on windows 2003 R2 Server, the SQL 2008 server is on Windows 2008. DTC works just fine between the two after some minor tweaks. The Error:

Class: 16 Error #: 8180 at line:376 Message Statement(s) could not be prepared. Class: 15 Error #: 102 at line:376 Message Incorrect syntax near 'Tbl1001'. Class: 15 Error #: 1018 at line:376 Message Incorrect syntax near 'FASTFIRSTROW'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. ---> System.Data.SqlClient.SqlException: Statement(s) could not be prepared.
Incorrect syntax near 'Tbl1001'.
Incorrect syntax near 'FASTFIRSTROW'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

That said, this is not the actual error at all. That code doesn’t even exist in the stored procedure in question. What?!? A Microsoft SQL Server error mislead you? NEVER! Oh, wait, yeah, they do that frequently.. say it ain’t so.

When the code was dissected the actual error was more telling. The real error is

Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.

Ah-ha! Now, we don’t actually update the cursor at this level, so why not use a fast_forward cursor type. This seems to clear up the issue, the SP ran and now I await actual data to see if this will be the fix.