Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2202
  • Last Modified:

Migrating SCCM database to a new Server with different collation

I need to migrate our SCCM database from an older SQL 2005 server to a new 2005 server with a different collation.   The new server hosts a number of OPS type databases and as such, the collation cannot be changed.   The research I have done thus far tells me a few things:
1) The collation of the SCCM db and TempDB must be the same making it impossible to just detach/attach the db.
2) the collation of the existing SCCM db cannot be changed without significant work (and possibly not at all)
3) While a named instance could be added to the new server with a different collation, SCCM works best when installed into the default instance.
4) it is best to start from scratch, and re-install SCCM.

I would like to do this with the least number of points of failure.    Currently, my thinking is to re-install SCCM, pointing the system to store the data on the new server, therby create a new SCCM db with the proper collation.   Following this step, importthe data from the existing db into the new one.

Can anyone suggest the best way of going about this.
P.S. the old collation is Latin1_General_CI_AS and the new is SQL_Latin1_General_CI_AS
  • 3
  • 3
1 Solution
I don't know how much difficulty a new instance really is for that app other than the default but if you could split some data files out (like put tempdb for this instance on it's own drive, etc) and have the system resources, I would think that a new instance may not be that difficult. Don't know how many places the connection string is hard-coded into SCOM but you'll have to change it anyway since you are going to a new server.

How old is that older server? The default in most SQL environments nowadays (Post 2000? or perhaps even post 7.0?) is SQL_Latin1. In fact our SCOM databases live in SQL_Latin1 as far as I know.

Are you able to do a quick test and see if just changing the DB Collation will fix this easily? Since these two collations function mostly the same and the new server is in the default collation (which I am fairly certain SCOM supports) and both collations are very similar with no funky sort orders/etc. You might have luck just altering the database to change it's collation. If you could do a quick test of this it might be worthwhile. (Keeping the old DB live just in case).

Changing the collation from books online (

Changing the Database Collation

Before you apply a different collation to a database, make sure that the following conditions are in place:

   1. You are the only one currently using the database.
   2. No schema-bound object depends on the collation of the database.
      If the following objects, which depend on the database collation, exist in the database, the ALTER DATABASE database_name COLLATE statement will fail. SQL Server will return an error message for each object blocking the ALTER action:
          * User-defined functions and views created with SCHEMABINDING.
          * Computed columns.
          * CHECK constraints.
          * Table-valued functions that return tables with character columns with collations inherited from the default database collation.
   3. Changing the database collation does not create duplicates among any system names for the database objects.
      The following namespaces may cause the failure of a database collation change if duplicate names result from the changed collation:
          * Object names such as a procedure, table, trigger, or view.
          * Schema names
          * Principals such as a group, role, or user.
          * Scalar-type names such as system and user-defined types.
          * Full-text catalog names.
          * Column or parameter names within an object.
          * Index names within a table.
      Duplicate names resulting from the new collation will cause the change action to fail, and SQL Server will return an error message specifying the namespace where the duplicate was found.

I don't know if any of those conditions are true in SCOM but it might be worth a shot. You might even just try restoring it and see what happens since, if the collation isn't specifically specified, you may be all set.

The last option of creating new and copying a lot of data over seems to be the most time consuming and complex of the options to me and you'll bump into order of move issues with foreign key constraints/etc.
TallKewlOnezDatabase AdministratorAuthor Commented:
You can't change the db collation for SCCM because a number of database objects are schema bound.
TallKewlOnezDatabase AdministratorAuthor Commented:
This issue is still outstanding.   At this point, if I don't hear back I will need to create a new SQL Server VM or a new instance with the old collation.  
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

TallKewlOneZ -

Have you tried simply restoring it to a new server in a test to see what happens?
TallKewlOnezDatabase AdministratorAuthor Commented:

I have not done that, because I know the database collation does not change during a restore.

see this article... http://msdn.microsoft.com/en-us/library/ms190725(SQL.90).aspx

This is a specific issue with SCCM requiring thwat the collation for TempDB and the SCCM user db be identical.  

Exerpt for SCCM article...
SQL Server

Configuration Manager requires that the SQL Server database collation settings of the tempdb database and site database are the same for daily site database operations.

Normally, I would just change the collation of the server and be done with it.  However, the server already hosts other dbs, that are also collation dependent.

Thanks for your help so far MikeW.
Ahh yeah well that one is tough, then. I would say that leaves choices 3 and 4...

Named instances can work, just requires more configuration. We have our SCOM database and reporting database both setup on named instances.

Really your two choices at this point are install it someplace new (either as a named instance or on a virtual or separate physical machine - I would even consider putting it on it's own database instance anyway, it can be piggish with it's queries and activity depending on the size of what you are managing and monitoring).


Attempt to change the collation by creating a new empty database with the right collation and then figuring out how to move all of the data into that new database. This is very difficult with an app like SCOM/SCCM because of the proprietary nature of the application and the installation process and dealing with the data moves.

Unless someone out there disagrees, I can't really see any other option left. It isn't a pretty answer as it requires work and potentially additional expenses (though a named instance saves the license cost of a new sql license and OS license)...

The only other suggestion I would think of is to contact Microsoft Support to see if they have any utilities or methods for more easily migrating the data to a new database.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now