Migrating SQL 2000 to SQL 2005, any risk I change db collation too?

29 Nov 2008 7:38 PM

serge

Hello,

I am restoring SQL 2000 databases to a SQL 2005 SP2 where the
SQL server's collation is Latin1_General_CI_AS.
The SQL 2000 database backups that I am restoring have their collation
as SQL_Latin1_General_CP1_CI_AS.

Other than running SQL 2005 Upgrade Advisor, I scripted out the
SQL 2000 database, created an empty SQL 2005 database and
then ran the generated scripts to catch any errors. Some of the
errors were about:
"Cannot resolve the collation conflict between
"SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS"
in the equal to operation"

Ok, maybe when I create the blank dbs in SQL 2005 I can have their
collations be SQL_Latin1_General_CP1_CI_AS and then run
the SQL 2000 generated scripts, this would give me no errors related
to "cannot resolve collation..." unless the T-SQL code was maybe using
tempdb objects.

In any case, is there any risk that after I restore these SQL 2000 databases
I simply change the database collation to become Latin1_General_CI_AS
so I standardize and avoid running into collation conversion errors?

I found these links:
http://www.examnotes.net/archive79-2002-8-54071.html
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305704&

It seems there's little risk to change the collation from
"SQL_Latin1_General_CP1_CI_AS" to "Latin1_General_CI_AS",
is it pretty safe to change the collation, are there anything else I need
to be concerned about?

What does happen when I change the db's collation?
SQL will change all text/ntext, char/nchar, varchar/nvarchar actual stored
data
to be stored using a different collation and then change the columns'
collations
afterwards?

I believe I would also have to drop the objects like UDFs, SPs that have
collation
conversion issues before I can change the db's collation. After changing the
collation
I re-create the dropped objects.

What's the worse that can happen, go wrong when changing the db's collation?

Can I switch back the database back to
"SQL_Latin1_General_CP1_CI_AS" from "Latin1_General_CI_AS"
in case I ran into problems?

Thank you

29 Nov 2008 9:14 PM

John Bell

Show quote Hide quote

"serge" <sergea@nospam.ehmail.com> wrote in message
news:ucMAKolUJHA.4372@TK2MSFTNGP04.phx.gbl...
> Hello,
>
> I am restoring SQL 2000 databases to a SQL 2005 SP2 where the
> SQL server's collation is Latin1_General_CI_AS.
> The SQL 2000 database backups that I am restoring have their collation
> as SQL_Latin1_General_CP1_CI_AS.
>
> Other than running SQL 2005 Upgrade Advisor, I scripted out the
> SQL 2000 database, created an empty SQL 2005 database and
> then ran the generated scripts to catch any errors. Some of the
> errors were about:
> "Cannot resolve the collation conflict between
> "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS"
> in the equal to operation"
>
> Ok, maybe when I create the blank dbs in SQL 2005 I can have their
> collations be SQL_Latin1_General_CP1_CI_AS and then run
> the SQL 2000 generated scripts, this would give me no errors related
> to "cannot resolve collation..." unless the T-SQL code was maybe using
> tempdb objects.
>
> In any case, is there any risk that after I restore these SQL 2000
> databases
> I simply change the database collation to become Latin1_General_CI_AS
> so I standardize and avoid running into collation conversion errors?
>
> I found these links:
> http://www.examnotes.net/archive79-2002-8-54071.html
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305704&
>
> It seems there's little risk to change the collation from
> "SQL_Latin1_General_CP1_CI_AS" to "Latin1_General_CI_AS",
> is it pretty safe to change the collation, are there anything else I need
> to be concerned about?
>
> What does happen when I change the db's collation?
> SQL will change all text/ntext, char/nchar, varchar/nvarchar actual stored
> data
> to be stored using a different collation and then change the columns'
> collations
> afterwards?
>
> I believe I would also have to drop the objects like UDFs, SPs that have
> collation
> conversion issues before I can change the db's collation. After changing
> the collation
> I re-create the dropped objects.
>
> What's the worse that can happen, go wrong when changing the db's
> collation?
>
> Can I switch back the database back to
> "SQL_Latin1_General_CP1_CI_AS" from "Latin1_General_CI_AS"
> in case I ran into problems?
>
> Thank you
>

Hi Serge

Just changing the database collation will not change the collation for
existing columns so you may get conflicts if you join new tables to old ones
as well as tempdb!! You need to go though each column and alter them as
well. Depending on the size of database it may be easier to do the scripting
side (taking the default collation) and then sucking the data across,
although that may increase the risk!!

John

30 Nov 2008 6:30 AM

serge

Hi John,

> Just changing the database collation will not change the collation for
> existing columns

It's good to know this. I think I will need to evaluate my options
and the work involved and decide what to do.

Thanks

30 Nov 2008 10:30 AM

John Bell

"serge" <sergea@nospam.ehmail.com> wrote in message
news:OJPGUUrUJHA.584@TK2MSFTNGP06.phx.gbl...
> Hi John,
>
>> Just changing the database collation will not change the collation for
>> existing columns
>
> It's good to know this. I think I will need to evaluate my options
> and the work involved and decide what to do.
>
> Thanks
>

I assume that you have not standardised on a given collation and the new
instance has been set up wrong?

What you could do is create a SQL 2000 instance with the new collation and
move over to that before upgrading to SQL 2005. To actually change the
columns collation is quite straight forward and the scripts to do this have
been posted many times by myself and others. The issue you may have is that
constraints on character columns will need to be dropped before changing
collation. This is not such a big issue with PKs, Unique constraints... as
you may want to rebuild indexes anyhow. What may be an issue is the space
needed to do this and the growth you may see.

At this point in time you should be considering moving to SQL 2008 to avoid
another migration in the not too distant future.

John

30 Nov 2008 2:31 PM

serge

> I assume that you have not standardised on a given collation and the new
> instance has been set up wrong?

No this is not the case. The person(s) who had installed the SQL 2005 boxes
had chosen SQL 2005's default collation but probably having checked the
"Accent Sensitive" check box as I believe that check box is not a default
check box (I might be wrong). And I am getting rid of a SQL 2000 server
by migrating the databases over, so I do want to keep the "new" SQL 2005
Latinxxx collation rather than use the old SQL_Latin...

> What you could do is create a SQL 2000 instance with the new collation and
> move over to that before upgrading to SQL 2005. To actually change the
> columns collation is quite straight forward and the scripts to do this
> have been posted many times by myself and others. The issue you may have
> is that constraints on character columns will need to be dropped before
> changing collation. This is not such a big issue with PKs, Unique
> constraints... as you may want to rebuild indexes anyhow. What may be an
> issue is the space needed to do this and the growth you may see.

I had already taken a script from somewhere online that was building me the
script to alter the collations. And I was writing the DROP INDEX statements
for the ones I was getting errors for, altering the collation and then
re-creating
the indexes. Space is not an issue in this case as there's ample room. A
couple of
days ago I was searching again and I read someone saying to change the db's
collation, so I thought about that, I ran the script and I got an error
about
object dependency. I dropped the function that I had previously seen as
giving collation conversion issue, alter the database's collation without
any error
and then re-created the dropped object.

So I had some concerns to reduce SQL 2005 migration issues, whether
I only alter the collations on the columns affected (where I was getting
conversion
errors) or I'd simply do alter all the columns of the whole database, and
most importantly
wasn't sure if I could without any fears simply alter to a different
collation and
not break something in terms of data having changed. I guess other than the
accent sorting matter, everything else should be Ok. So I will change the
collation
of all the columns and also the database.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305704&

> At this point in time you should be considering moving to SQL 2008 to
> avoid another migration in the not too distant future.

I have to convince others that it's safe to move these databases to
compatibility
levels of 90 so imagine that there are people who still want to wait for SP1
before
using SQL 2008. My plan is to push SQL 2008 after I finish the migration.

Thanks again

30 Nov 2008 8:47 PM

John Bell

Show quote Hide quote

"serge" <sergea@nospam.ehmail.com> wrote in message
news:Ob5bQhvUJHA.5200@TK2MSFTNGP05.phx.gbl...
>> I assume that you have not standardised on a given collation and the new
>> instance has been set up wrong?
>
> No this is not the case. The person(s) who had installed the SQL 2005
> boxes
> had chosen SQL 2005's default collation but probably having checked the
> "Accent Sensitive" check box as I believe that check box is not a default
> check box (I might be wrong). And I am getting rid of a SQL 2000 server
> by migrating the databases over, so I do want to keep the "new" SQL 2005
> Latinxxx collation rather than use the old SQL_Latin...
>
>> What you could do is create a SQL 2000 instance with the new collation
>> and move over to that before upgrading to SQL 2005. To actually change
>> the columns collation is quite straight forward and the scripts to do
>> this have been posted many times by myself and others. The issue you may
>> have is that constraints on character columns will need to be dropped
>> before changing collation. This is not such a big issue with PKs, Unique
>> constraints... as you may want to rebuild indexes anyhow. What may be an
>> issue is the space needed to do this and the growth you may see.
>
> I had already taken a script from somewhere online that was building me
> the
> script to alter the collations. And I was writing the DROP INDEX
> statements
> for the ones I was getting errors for, altering the collation and then
> re-creating
> the indexes. Space is not an issue in this case as there's ample room. A
> couple of
> days ago I was searching again and I read someone saying to change the
> db's
> collation, so I thought about that, I ran the script and I got an error
> about
> object dependency. I dropped the function that I had previously seen as
> giving collation conversion issue, alter the database's collation without
> any error
> and then re-created the dropped object.
>
> So I had some concerns to reduce SQL 2005 migration issues, whether
> I only alter the collations on the columns affected (where I was getting
> conversion
> errors) or I'd simply do alter all the columns of the whole database, and
> most importantly
> wasn't sure if I could without any fears simply alter to a different
> collation and
> not break something in terms of data having changed. I guess other than
> the
> accent sorting matter, everything else should be Ok. So I will change the
> collation
> of all the columns and also the database.
>
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305704&
>
>> At this point in time you should be considering moving to SQL 2008 to
>> avoid another migration in the not too distant future.
>
> I have to convince others that it's safe to move these databases to
> compatibility
> levels of 90 so imagine that there are people who still want to wait for
> SP1 before
> using SQL 2008. My plan is to push SQL 2008 after I finish the migration.
>
> Thanks again
>
Hi

Both your collations Latin1_General_CI_AS and as
SQL_Latin1_General_CP1_CI_AS
are accent sensitive. You'll get Latin1_General_CI_AS on a system if you go
through the installation on a new installation on Windows if you just keep
pressing next.

Another option would be to use the COLLATE clause in all statements that
will conflict use character columns in their where or join clauses and/or
when you create the temporary tables.

John
John

30 Nov 2008 9:09 PM

Eric Isaacs

You can mitigate the risk of changing the collation by not doing it to
your production database until you have a working script on a test
copy of the production database. If you change the collation on the
database and all the varchar/nvarchar/text/ntext/char/nchar fields in
the database, then add back in all the constraints and indexes and
primary keys that you had to drop, you should be good.

If you opt to go with adding COLLATE statements to all your SQL (which
is an option) I would suggest you collate to Database_Default instead
of a particular collation. If you do this, your database will be more
portable to other servers with other collations.

At the same time, if you're depending on another database for data,
it's a good idea to include COLLATE Database_Default, especially if
you don't have direct control of that other database's collation. If
you're interfacing with a third-party database or don't know for
certain what collation will be in use on the other database, COLLATE
Database_Default is definitely something that should be considered.

Copy that production database, work on a script to change everything,
test the changes, then backup the production database again and apply
the script, and then test in production. If you do that, your risk
should be minimized.

-Eric Isaacs

1 Dec 2008 7:35 AM

John Bell

Show quote Hide quote

"serge" <sergea@nospam.ehmail.com> wrote in message
news:Ob5bQhvUJHA.5200@TK2MSFTNGP05.phx.gbl...
>> I assume that you have not standardised on a given collation and the new
>> instance has been set up wrong?
>
> No this is not the case. The person(s) who had installed the SQL 2005
> boxes
> had chosen SQL 2005's default collation but probably having checked the
> "Accent Sensitive" check box as I believe that check box is not a default
> check box (I might be wrong). And I am getting rid of a SQL 2000 server
> by migrating the databases over, so I do want to keep the "new" SQL 2005
> Latinxxx collation rather than use the old SQL_Latin...
>
>> What you could do is create a SQL 2000 instance with the new collation
>> and move over to that before upgrading to SQL 2005. To actually change
>> the columns collation is quite straight forward and the scripts to do
>> this have been posted many times by myself and others. The issue you may
>> have is that constraints on character columns will need to be dropped
>> before changing collation. This is not such a big issue with PKs, Unique
>> constraints... as you may want to rebuild indexes anyhow. What may be an
>> issue is the space needed to do this and the growth you may see.
>
> I had already taken a script from somewhere online that was building me
> the
> script to alter the collations. And I was writing the DROP INDEX
> statements
> for the ones I was getting errors for, altering the collation and then
> re-creating
> the indexes. Space is not an issue in this case as there's ample room. A
> couple of
> days ago I was searching again and I read someone saying to change the
> db's
> collation, so I thought about that, I ran the script and I got an error
> about
> object dependency. I dropped the function that I had previously seen as
> giving collation conversion issue, alter the database's collation without
> any error
> and then re-created the dropped object.
>
> So I had some concerns to reduce SQL 2005 migration issues, whether
> I only alter the collations on the columns affected (where I was getting
> conversion
> errors) or I'd simply do alter all the columns of the whole database, and
> most importantly
> wasn't sure if I could without any fears simply alter to a different
> collation and
> not break something in terms of data having changed. I guess other than
> the
> accent sorting matter, everything else should be Ok. So I will change the
> collation
> of all the columns and also the database.
>
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305704&
>
>> At this point in time you should be considering moving to SQL 2008 to
>> avoid another migration in the not too distant future.
>
> I have to convince others that it's safe to move these databases to
> compatibility
> levels of 90 so imagine that there are people who still want to wait for
> SP1 before
> using SQL 2008. My plan is to push SQL 2008 after I finish the migration.
>
> Thanks again
>
Hi

You may want to use something like Red Gates SQL Compare where you can
compare the structure of each database ignoring collation. The databases
should match if you set the options to ignore collation.

john