Moving backwards: Moving a database from SQL Server 2008 R2 to SQL Server 2005

I came across a strange scenario today. The vSphere service running on one of our SQL servers (I know, I know... terrible) was getting constant ASYNC_NETWORK_IO waits, and the only solution was to restart the service, but after a little time, wait times would increase again.

It turns out it was a CPU resource allocation issue, whereby the cores allocated to the particular virtual machine were insufficient, as well as being an issue with the SAN.

In an effort to rule out any and all database possibilities (which I had a feeling there were none in terms of SQL Server problems), I decided to move the database onto a server with very little activity on it (and the only one of our production servers that isn't mission critical). The issue with that was that it's a SQL Server 2005 server, and the current database sits on a SQL Server 2008 R2 machine. As we all know, you can't regress in SQL.

I've found a long, but reliable way around this. In broad terms but point format, perform the following:

1. Generate scripts for all databases concerned, making sure to script logins, bindings, collation and object permissions.
2. Run scripts on new server.
3. Before importing any data, run the following:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

4. In SSMS, right click on the source database, goto Tasks/Export Data. Select your source and destination server, the appropriate login details, Copy Data... option, and then click on the check box in the top left corner of the list of tables to select all tables (scroll down the list to uncheck any views - you don't want to copy those because they're already scripted).
5. Click on the Edit Mappings button and select "Enable Identity Insert".
6. Click Next a few times, and let it do its thing.

After this, all your data will be transferred. Run this script to enable all constraints again:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

If, for some reason, the export fails half way through, you can run either the first or second script to remove the data (because even though constraints are disabled, truncating a table doesn't work):

EXEC sp_msforeachtable "DELETE FROM ?"

Or:

DECLARE @TableName VARCHAR(200)
DECLARE @SQL NVARCHAR(500)

DECLARE CurTables CURSOR FAST_FORWARD FOR
SELECT [name] FROM sys.tables WHERE [type] = 'U'

OPEN CurTables

FETCH NEXT FROM CurTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL = 'DELETE FROM ' + @TableName
 --PRINT @SQL
 EXEC sp_executesql @SQL
 FETCH NEXT FROM CurTables INTO @TableName
END

CLOSE CurTables
DEALLOCATE CurTables

The second query is there in case you get an error talking about quoted identifiers and how they need to be set. According to Microsoft, you can't set quoted identifiers during runtime, so the second query does what the first one does, but with more visible code.

Apart from that, everything works well afterwards. Fun times.

Helpful queries in MySQL

So I've been using MySQL for a little bit now, and I've been wanting to get at some information that's quite easily seen in SQL Management Studio for MSSQL. Turns out it's ridiculously easy - just look through the INFORMATION_SCHEMA table (if you have access to it, which I believe every account does by default - correct me if I'm wrong). To get a list of the stored procedures (or "Routines" in MySQL parlance):

SELECT SPECIFIC_NAME FROM ROUTINES WHERE ROUTINE_SCHEMA = '[schema name here]';

To show all the foreign (referential) constraints:

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME FROM REFERENTIAL_CONSTRAINTS;

I'll add some more when I need to look for them.

Temp Tables vs Table Variables

I've been having a lot of issues in the last two days with TempDB having some ridiculously heavy usage. Some well placed (I think) indexes have seemingly helped alleviate some of the usage of TempDB. I still have one query to re-write, and it's so I stop using a table variable, and move to a temp table (I know, it sounds counter productive).

Here's a post about temp variables vs temp tables. Fun.

http://gyanprakashis.blogspot.com.au/2012/04/difference-between-temp-table-and-table.html

Taken down a notch or two in life

I got some feedback from an interview I had a few days ago. In my own words, I flat out sucked the big one. In their words, I didn't meet the level of technical expertise they require - which is completely understandable. Their business is far and above anything I've ever even considered, and where I'm at at present is, well, it isn't that challenging.

It's all good and well for family and friends to say "you'll be fine, you're smart, you'll ace it", but really, it's just bullshit posturing. I know what my abilities are, and while I appreciate their sentiments, I know that I need a kick in the pants to get me moving again. I've become too complacent in my role. It's all good to be the "go-to" guy where you're at, but there's ALWAYS a ton of people better than you, and you need to realise that so you can make yourself better.

So, it's back to the drawing board as such. I'm going to do a whole lot of study, and hopefully get my MCTS for Database Development. That study in itself should at least boost my knowledge a bit.

Things I learned in the last two days...

Pros and Cons of GUIDs vs. INTs for primary keys:

GUIDs:
- Great for when you need to combine rows from two or more tables (including identity columns) because you're guaranteed that you'll never have a conflict in the primary key.

- Guaranteed uniqueness.

- Problem is, they take up 16 bytes each and every time.

- More processing time for sorting because it gets converted to string data and then ordered, with all characters having to be evaluated.

INTs:
- The inverse of the advantage of GUIDs when combining rows from multiple tables into one. There can be IDs that are the same, and this causes problems when using SET IDENTITY_INSERT dbo.[TableName] ON. Changing the primary keys would mean having to update all child records etc. Quite undesirable.

- However, you get a little over 2 billion unique values from 4 bytes, thus saving a lot of space. 12 bytes * 2,000,000,000 = 24,000,000,000 bytes. Divide that by 1024 for KB, then 1024 for MB, then 1024 for GB, and you save 22 GIGABYTES of space in the ID column alone.

- Numbers are REALLY EAST for CPUs to evaluate and sort.

I learned a golden rule of indexes - anything over 30% fragmentation should result in an index rebuild, between 5% and 30% would be a reorganisation, and less than 5% is OK. Index rebuilds can take more than a day on big tables. Ouch.

I also gathered that no matter how much I know now, which results in some level of comfort and complacency, there's ALWAYS someone who makes you feel as if you were a child in kindergarten talking to a professor with the amount of knowledge they have on particular subjects (like SQL Server).

Thanks to a webinar by Kendra Little at Brent Ozar PLF, NOLOCK and READ UNCOMMITTED can be used together in a table hint, but are completely redundant, and also, don't necessarily mean that no locks will actually be obtained. Interesting indeed.

Pulling the trigger on unsupported modifications

CRM can do a lot of things, but there are some things that are just easier to do elsewhere. We had an asynchronous incremental numbering that was created to add a project number to an order. This has ended up not working 100% of the time for some reason (which we are still unaware of).

Rather than using a few hundred lines of C# code, one table containing 7 rows, 30 lines of Transact-SQL code in a stored procedure, and a trigger were all it took to have incremental numbering working properly.

I WAS going to use a computed column, but there were some limitations with that due to the construction of our project numbers (which are based on different areas of the country). All in all, a successful day.

Now if only the child of one of the system specialists would stop it with that damn cough...

The OUTPUT clause

One thing people like to do is have an audit trail of things that are done in their databases. One way to implement that is using the OUTPUT clause in your INSERT/UPDATE/DELETE statements. The inserted and deleted values are stored in special tables named, predictably, inserted and deleted.

To use them, here's an example:

UPDATE
    [Table Name]
SET
    [Field Name 1] = 'Value1'
    ,[Field Name 2] = 'Value2'
WHERE
    [Field Name 3] = 'Such and such'
OUTPUT
    inserted.[Field Name 1]
    ,deleted.[Field Name 1]
INTO
    #TempTable

As you can see, it's rather straightforward. You can insert the output into a temp table, a table variable or a permanent table in your database.