June 18, 2007 at 12:29 pm
There's been some discussion on this, but I'm looking for some additional guidance.
I have a MSSQL 2005 Standard engine that I installed with 'Latin1_General_CI_AS' as the engine character set. One of my apps needs 'SQL_Latin1_General_CP1_CI_AS' or it won't install. I think I made this choice during install, and it seems like I probably should have left well enough alone as I believe the latter is the default, and they're probably functionally equivalent in my context.
From the docs, I see that I can do this via an unattended installation (http://msdn2.microsoft.com/en-us/library/ms144259(SQL.90).aspx):
start /wait
What I'm wondering are the following:
1. The article above says the following:
"When installing SQL Server 2005, use the correct collation settings. You can change collation settings after running Setup, but you must rebuild the system and user databases and reload user data if you do."
Does the installation utility do this as part of this process?
2. Some of my applications use hard-coded application-specific usernames/passwords as part of their operation (I run in mixed-mode). How do I preserve these?
3. Would it be simpler just to uninstall/reinstall? If so, how do I preserve my credentials so that my apps continue to work?
4. Other things I should be thinking about?
June 18, 2007 at 10:46 pm
You can have the database that your apps uses to use the collation instead of going for the server level default collation. You can create your database specifying the colllation it should take so that all tables and character columns will have that collation defined within the table. Or if its like you need to change the server collation rebuild your system and recreate users DBs and import your data.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 19, 2007 at 5:39 am
If I detach, change the server collation or just reinstall, and then reattach, do the user DBs retain their original collation settings (and can I reattach them)? I'm assuming that the collation information, etc. is part of the database file metadata?
How do I preserve the user credentials? I have third-party applications that use their own internal usernames/passwords that I don't necessarily know -- is there a straightforward way in 2005 to export that information from the system databases and import it into a fresh install?
June 19, 2007 at 6:25 am
If you detach, reinstall and then reattach, then the database will retain the collation. I meant that you can create a database with a new collation other than the default server collation. In your case i hope you can rebuild the server, create a database with collation you want and then move the data to the new database.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 19, 2007 at 6:28 am
Thanks for the response. I'm still looking for some specific guidance on the userid/password question -- otherwise I only have part of the solution.
Looks like this might be the answer:
June 19, 2007 at 6:31 am
Regarding the user id/password part, if you rebuilt the server all logins will get dropped and recreated. Hence you will be losing the same. You have to recreate the user and give proper permissions and credentails.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply