Specify SQL Server collation settings for Windows SBS 2008 migration

  • Article

Updated: January 22, 2009

Applies To: Windows SBS 2008

It is recommended that you use the default settings for installing SQL Server, unless you need to specify a collation setting. You must specify a collation setting if at least one of the following is true:

  • You are using a line-of-business (LOB) application that depends on settings from a previous version of SQL Server. See the documentation for your LOB application to verify which collation settings are required.

  • You must match the Windows locale of another computer, or you must match the collation settings of another instance of SQL Server. If SQL Server is in a language that is different from that of the client computers that connect to the database, you must specify a Collation Designator, and then select the name of a specific Windows collation from the list. You can then specify a Sort Order to use with the Collation Designator.

  • For more information about which collation settings to use, see “SQL Server Configuration – Collation” on the Microsoft Web site (https://go.microsoft.com/fwlink/?LinkId=120785).

To verify the Windows locale of another computer

  1. On the Source Server, click Start, click Run, and then type Control to open Control Panel.

  2. Double-click Regional Options (also called Regional Settings or Regional and Language Options). A dialog box appears.

  3. Note whether the locale of the Source Server is different from the locale of Destination Server. If it is, use the table in the "Windows Collation Designators" Help topic to find the corresponding Collation Designator. The collation settings must match those of another SQL Server installation. To match an earlier version of SQL Server, select SQL Collations (used for backward compatibility with previous versions of SQL Server). The default setting for the locale of your operating system is in the Collation Settings list box.

Many server-to-server activities can fail if the collation settings are not consistent across servers. Although you can change collation settings after you finish migration, if you do so, you must rebuild the databases and reload the data.

To avoid issues later, it is recommended that you verify the necessary selections for Collation Designator and Sort Order with the collation settings for the installation of SQL Server on the Source Server.

To verify collation settings

  1. On the Source Server, open the SQL Server Management Studio Express (click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio Express).

  2. In the Connect to Server dialog box, select Database Engine as the Server type, select the name of the SQL Server instance for which you want to verify the collation settings, and then specify the authentication information for the connection.

  3. In Object Explorer, right-click the SQL Server instance, and then click New Query.

  4. In the query pane, type sp_helpsort, and then click Execute in the toolbar.

  5. The default collation settings of your server appear in the results pane of the query window.