DB Goby

New version of DB Goby soon ready!

2008-01-09T14:31:00.000+01:00

A new version of DB Goby is soon ready.

New features:

1) Moving database files will be easier.
It will now be possible to move files for severeal databases at once, based on a rule set. In this way it is easier to i.e. move all data files on a server to a second disk, and all log files to a third disk. This in just one operation.

This is a great feature when you run out of disk space on your server, and need to add more disks, or if you need to tidy up your placements of data files.

2) Database file management.

Create file groups and files in an easier way based on customizable templates.
Redistribute data over several files within a file group, so that data is evenly spread over the files.

When you are adding new files to a filegroup, the existing data will still remain in the old files resulting in an unbalanced file distribution. If you are designing your server architecture like described in the article "Physical Database Storage Design", you will get a better I/O after using this redistribution feature.

The new version is estimated available in a week or two.

Best regards
Øystein

Orphaned Transactions

2008-01-07T13:51:00.000+01:00

If you are using distributed transactions (MS DTC), it may happen that Sql Server loses the connection to the coordinator and the transactions will neither be commited or rolled back.
The locks by this transaction will remain on the server until you manually remove it, or restart the server.
You will see these locks in the Activity Monitor with a Process ID (PID) = -2

You can't just kill the -2 PID, so you need to get hold of what the Unit of Work ID is for these transactions by running the following query:

select request_owner_guid
from sys.dm_tran_locks
where request_owner_guid <> '00000000-0000-0000-0000-000000000000'

For those UoW IDs, run the KILL command, like this:

KILL 'B8041DFE-0732-418F-98AA-FE4D05DA554B'

Problems with Dual Core Processors

2007-12-10T14:07:00.000+01:00

I have a computer with a dual core processor myself, and I experienced problems with some computer games where the internal CPU timer really messed up the game. The graphics went faster and faster until my computer crashed with a blue screen. One trick was to set the CPU affinity on the application to one core. But that wasn't a satisfying solution.

I used a lot of time reading discussion forums to get rid of the problem.

The first recommondation I got was to try a hotfix that Microsoft had released, but that didn't solve the problem, it only introduced some more.

I also disabled the Qool'n'Quit technology in the BIOS, that helped some, but there was still a problem.

Later AMD came with a driver update that fixed the problem, and things seemed to work ok.

Now I have been introduced to similar problems for SQL Server. I know there has been problems with time stamping both for SQL Server 2005 and Reporting Services due to CPUs that change frequencies.

Microsoft has released a KB about this that you should read, if you have a servers running on dual core processors and you are getting strange error messages (i.e. I/O timeout).

http://support.microsoft.com/default.aspx?scid=kb;EN-US;931279

How NULL values affect NOT IN queries

2007-12-10T11:12:00.000+01:00

Using NOT IN queries may give unexpected results when the sub query contains Null values. I'll give you an example:

We create a table:

Create table #Person
([Id] int identity, [Name] nvarchar(20) not null, [Spouse] int null)

insert into #Person([Name], [Spouse])
select 'Jim', 2
union all
select 'Nora', 1
union all
select 'Jane', null

Jim and Nora are married to each other. Jane is unmarried.

Lets try a query that counts unmarried people, using NOT IN:

select count(*) as [Unmarried]
from #Person where [Id] not in
(select [Spouse] from #Person)

Unmarried
-----------
0
(1 row(s) affected)

So none is unmarried?

Ok. Let's try something different. Let's make a query to find out how many is not married to 'Jim':

select count(*) as [Not married to Jim]
from #Person where [Id] not in
(select [Spouse] from #Person
where [Name] like 'Jim')

Not married to Jim
------------------
2
(1 row(s) affected)

Hmm. Here we got a correct result. Is it because the sub query does not contain a Null value? Let's try getting another query where the sub query contains Null:

select count(*) as [Not married to Jane]
from #Person where [Id] not in
(select [Spouse] from #Person
where [Name] like 'Jane')

Not married to Jane
-------------------
0
(1 row(s) affected)

Ok? Appearantly, all are married to Jane...

Lets try the first query with Not Exists, instead of Not In:

select count(*) as [Unmarried]
from #Person P1 where not exists
(select 1 from #Person P2 where P2.[Spouse]=P1.[Id])

Unmarried
-----------
1
(1 row(s) affected)

Here we got a correct answere.

I guess the conclusion is to be very aware when using Not In in a query. If Null is in the result in the sub query, you most likely will get a wrong result. Use Not Exists rather then Not In if possible.

Changing Sql Server Edition

2007-11-01T13:13:00.000+01:00

Have you been in a situation where you have installed and used Sql Server Standard Edition, but need features from Enterprise Edition?
Or perhaps you have Enterprise Edition but should have installed Standard Edition due to licence costs?
I have even experienced vendors to install Developer Edition on a production server, which is not allowed!

If you need to change to another edition, you might have serious issues to uninstall the server so you can install the preferred edition. All information in the system databases will be lost.

Off course you can do a restore of the msdb and tempdb databases to get jobs etc back, but the master database can't be restored due to different version numbers.

The master database might contain user defined procedures, functions, sysmessages, extended procedures etc. that are vital for the applications.

So what do you do?

Well, you can actually change the edition without reinstalling. Just run the setup.exe file from command prompt with the SKUUPGRADE parameter:

start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER
UPGRADE=SQL_Engine SKUUPGRADE=1 /qb

This will work for the default instance. For a named instance you will have to change some of the parameters.

After installation, remember to reinstall Service Packs.

Be sure to take a backup of your databases before doing this! ;-)

Check out the install features at: http://msdn2.microsoft.com/en-gb/library/ms144259.aspx

What about the logins?

2007-11-01T12:19:00.000+01:00

When you are moving or copying a database from one server to the other using the detach/ attach method, the user objects follow the data file. But what about the logins?

The database user objects are connected to the logins using SIDs, and when attaching a database on a new server, the login for that user may not exist, or it may have a completely different SID.

One solution is to drop the user objects and recreate them based on the new server's logins, but then you would also have to reset the users' access rights and ownerships.

And what about missing SQL Logins on the new server? Can you script logins without compromising the passwords? Perhaps you don't even know the passwords...

There is a great MSDN article on how to do this at http://support.microsoft.com/kb/246133 .

If you can use the Copy Database Wizard, I think this will be taken care of automatically, but I haven't tested it, yet.

I actually plan to add a tool to DB Goby that creates scripts that in addition to attach a database, also scripts logins and match the SIDs.

Migrating from Sybase to Sql Server

2007-10-18T16:01:00.000+02:00

The last days I've been working on a project where I tested conversion of some databases.

The source was Sybase version 11.9, and I wanted to migrate to Sql Server 2008. As I found, this was not an easy job!

Sql Server Migration Assistant for Sybase (SSMA) only support Sybase version 12.5 or newer, and Sql Server 2005.

So I set up a temporary Sql Server 2005 for the migration job, and then I wanted to test how far I could get with a version 11.9 database.

Connection problems

The first problem was connecting to the Sybase database. I had installed the Sybase ODBC driver, but it wouldn't show up in the list in the Data Sources (ODBC) tool. After a search on the web, I found that when using a 64 bits server (Windows Server 2008), the system would have two folders for ODBC drivers; one for 32 bits drivers, and one for 64 bits drivers. The driver had installed itself in the 64 bits folder, while the Data Sources (ODBC) tool would only look up drivers in the 32 bits folder. Bummer!

I then installed SSMA on a Windows Server 2003, and could finally create an ODBC DSN. But I still could not connect to the Sybase database from the SSMA. After asking a Sybase DBA for help, we noticed that SSMA used the native Sybase driver and not the ODBC, and that required some .ini files in the Sybase folder to be configured to be pointed to the correct database and server. We finally had a connection! :)

Connecting to the Sql Server went off course smooth!

Converting a small database

I first tried to convert a small database with few objects and small amounts of data. This went relatively well. We had one single error converting the objects.

When installing the SSMA, it creates a conversion database on the Sql Server, containing some informaiton that is needed in the conversation process. Among other things it contains system functions that are treated differently in Sybase and Sql Server. For instance the datediff function is implemented as a user function called ssma_datediff so it is not confused with the original datediff system function in Sql Server.

When converting stored procedures containing calls to the datediff function, it would translate to:

sysdb.ssma_syb.ssma_datediff(...)

This returned an error in the SSMA engine because it could not validate the object.

What I did to solve this was that I imported the function into the database I was converting to, and changed the migration script so it called an internal user function instead:

dbo.ssma_datediff(...)

After that we migrated the data content, and there were no hiccups. Mission Accomplished!

Going for a larger database

OK. Now for a greater challenge: A Sybase database containing more than 450 tables, 150 views, 400 stored procedures, 200 triggers, 100 user defined data types, and many of the tables containing several millions of rows.

This time, when migrating the database objects with SSMA, I got a sql script with over 400 000 lines and with more than 2000 errors! Sigh!

I then started to edit the sql script, and found 4 kinds of problems:

1) System function calls

As with the small database, this one had a lot of references to user defined functions in the sysdb database. Solution: Import the functions and rewrite the call.

2) Parameters and Variables in Stored Procedures and Triggers

SSMA didn't just put brackets around object names like tables, schemes, database etc, but also around parameters and variables in the SPs. I think I had like 40 000 in the script that looked like this: [@myVariable]

Editing these by hand was out of question, so how do I use a search/replace without removing the brackets around the objects that should have them? I started to play around with Regular Expressions and ended up with this:

Search: \[{\@[A-Za-z0-9_]+}\]
Replace: \1

That hit the spot!

3) Old outer join syntax

A lot of procedures in the sybase database had old outer join expressions, and the SSMA couldn't translate them, so I had to do that manually.

Old syntax:

select * from
[TableA] a, [TableB] b
where
b.[a_ID]=*a.[ID]

Corrected syntax:

select * from
[TableA] a
left outer join [TableB] b
on b.[a_ID]=a.[ID]

4) Aggregates on aggregated values

Several of the procedures had queries using aggregates on aggregated values, like this:

declare @maxDate int
select @maxDate = isnull(max(max(a.[Date])),0)
from [TableA] a
group by a.[Date]
having sum(a.[Amount])!=0

First of all, this is not a valid TSQL statement in SQL Server. You will get an exception saying "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

Second, this statement doesn't make much sense to me! You are grouping by the Date column and then selecting the max value of just the same column... What is the point of using an aggregate on that at all? Perhaps it has some obscure meaning in Sybase, but it gives no meaning to me!

Anyway, I rewrote the statements to what I believed they were meant to do; simply getting the latest date where the sum of amount was not zero:

select @maxDate =
isnull(max(sub.[Date]),0)
from
(select a.[Date]
from [TableA] a
group by a.[Date]
having sum(a.[Amount])!=0) sub

If any Sybase gurus out there read this, please comment on this part!

After importing user defined data types and defaults manually, the new script ran successfully on the Sql Server.

Migrating data content:

Another challenge! Some tables could not be converted because of casting problems, and others just dropped the connection to the Sybase server when trying to read from them. But luckily most tables migrated with no problems.

For the tables that had casting problems, I decided to use Sql Server Integration Services (SSIS).

The first problem was again to connect to the Sybase server. But after reading some tips on the web, I found that the way to do this was to create an ADO.NET connection that the DataReader could use, then configure the ADO.NET connection to use ODBC driver and point it to the ODBC DSN I already had created when trying to get a connection from the SSMA.

After that it was rather easy to migrate table by table using a Data Reader source, a Data Conversion, and a SQL Server Destination.

Then I was left with five tables that could not be migrated. Every time I tried, the connection was dropped and I had to restart the Sybase service to be able to connect to it again.

I thought there had to be something wrong with the source, so I logged into ISQL on the Sybase server and made a simple select * from the problem tables. And after a while it also dropped the connection with a read error, so I guess the tables were corrupt.

Conclusion:

Except from the five supposedly corrupt tables, I managed to migrate all objects and data from the Sybase v.11.9 database to Sql Server 2008, using SSMA and SSIS. So I guess I can call that a success, and
Mission Accomplished! :)

Some useful resources

2007-10-17T08:46:00.000+02:00

Today I want to share some links to useful resources that has helped me a lot.

The first link I want to share with you, is to a web site called SQL Skills. The people behind the site have been very important resources for Microsoft and Sql Server. I'm sure most of you know Kimberly L. Tripp and Paul S. Randal. Be sure to visit their blogs! There is so much to learn there.

Next is a link to an MSDN article discussing Physical Database Storage Design. Useful information when designing databases!

My code library is a site I used to visit a lot earlier. It has a lot of fun code snippets, stored procedures, queries etc. The site hasn't been updated since November 2005, though, so it mainly has stuff for Sql Server 2000. But still it can work as a source of inspiration.

I'm a subscriber to the Sql Server Magazine. Nice articles, and fun exercises.

Cheers!

-Øystein

DB Goby v.1.0

2007-10-16T18:17:00.001+02:00

Well, here is a bit information about DB Goby v.1.0:

DB Goby is an application containing three tools:

A tool to create insert scripts from the content of a database
A tool to move data files to a new location
A tool to estimate disk requirements for a database.

The application is built for Sql Server 2005, and Sql Server 2008. Some functions may work on Sql Server 2000/7.0, but many features don't support those versions. This is because the system views/tables are different, and require different queries. I might add support for 2000/7.0 later on.

Some information about the tools:

As you probably guess, the Create insert script tool simply reads the content from one or several tables and creates insert scripts. ;-)

These scripts are useful when you are building one or several projects in a solution (i.e. using NAnt and/or CruiseControl etc), and deploying the solutions onto several development and test environments.

The tool should support all system data types, including LOBs.

The Move database files tool is good to have if you need to move a database with many filegroups and database files to a different location.

The tool will set the database into single user mode, then put it offline, copy the files to the destination, reset the filepointers in the database, bring the database back online and set to multi user mode.

It requires the use of extended stored procedures. So if the server has not extended stored procedures enabled due to security reasons, the tool will temporarily reconfigure the server while the operation is done, and then set it back to the original state.

The Estimate index sizes tool will read the structure of the database and estimate how much disk space the indexes/tables will require.

It is possible to change the values for the number of partitions, how many rows for each partition, variable column fill factor and LOB size for each table. By doing this you can estimate how much disk space you will need in a year or two, so you can plan for appropriate hardware in your production environment.

The formulas to calculate the report are mainly based on the MSDN article "Estimating the Size of a Database":

http://msdn2.microsoft.com/en-us/library/ms187445.aspx

If you find this interesting and want to try my application, you can download it at:

http://dbgoby.net/DBGobySetup.zip

If you have feedback, requests for new features or bugs to report, please send me a comment or an email. You'll find contact information in the help file.

Enjoy! :)

-Øystein

Welcome

2007-10-16T17:59:00.000+02:00

Hi, and welcome to my new blog!

I guess I can start by telling a little about myself.

I work as an advisor in a norwegian IT-company. I mostly develop applications using .NET and Sql Server. I have been DB admin in many of the development projects I have taken part in during the years, so I have grown a special love for the Sql Server, especially after version 2005 arrived.

Even if Sql Server is a very good product, I have missed some features or tools to help me do the tasks I needed to do. Some help I've got from tools found on the web, but in some cases I've had to implement the tools I've been missing myself, in my leasure time.

I've decided to publish a few of my tools so others can take pleasure of using them. So, that is why I've made this blog.

Perhaps I'll get some feedback and ideas to improve my applications, too. :)

At this point I've bundled together 3 tools into a small application, and you are all free to download it from my web site. It's freeware!

You will get more information in my next blog post where I will tell you more about it in detail.

Cheers!

-Øystein