Migrating SCCM database to a new Server with different collation

Posted on 2010-01-06
Last Modified: 2013-12-01
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
Question by:TallKewlOnez
    LVL 13

    Expert Comment

    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.
    LVL 3

    Author Comment

    You can't change the db collation for SCCM because a number of database objects are schema bound.
    LVL 3

    Author Comment

    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.  
    LVL 13

    Expert Comment

    TallKewlOneZ -

    Have you tried simply restoring it to a new server in a test to see what happens?
    LVL 3

    Author Comment


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

    see this article...

    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.
    LVL 13

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Title # Comments Views Activity
    SQL Server Fine Tuning 7 38
    SQL Query 18 62
    SQL Database Mail Setup 1 19
    import issue in bit values 3 31
    I previously wrote an article addressing the use of UBCD4WIN and SARDU. All are great, but I have always been an advocate of SARDU. Recently it was suggested that I go back and take a look at Easy2Boot in comparison.
    Know what services you can and cannot, should and should not combine on your server.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now