Changing System ID SQL 2005

[homer02]

Hello Friends,

I have a question about SQL 2005 and hopefully you guys can help me out with this.

I installed sql 2005 server to win2003 server and used this as the base image to make ghost for another, say, 20 machines to use. The problem is that although after we changed the system ID for the machine that get the ghost image of windows 2003 and sql 2005, the case sensitive command does not work. if we install sql 2005 clean, then the case sensitive works fine (by default, sql 2005 work with case-insensitive) Please help.

Thanks

 

[Catadmin]

Did you install 2k5 as case sensitive or case insensitive on your original installation?



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[homer02]

By default, it is case-insensitive. So, the answer to this question is that I installed sql 2005 with case-insensitive.

 

[Catadmin]

Then I would think that case sensitive would not work for the other servers considering you are ghosting off a case insensitive image. Maybe you need to reinstall the original image as case sensitive and see if things work then?

Or am I completely missing something?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[homer02]

Well, the case-sensitive work well if we do clean installation without getting the system from ghost. By default, SQL 2005 installed with case-insensitive but should work well with case-sensitive if we run come cammands. But in this case, after we get the system from ghost, the case-sensitive no longer work when we run commands that make the case-sensitive work. Again, this case would not happen if we do clean installation. So, I think that because of ghosting and changing system ID process, it causes the problem when we try to run command for case-sensitive.

 

[Catadmin]

Oh. Sorry. It took me a while to understand. I haven't actually worked with SQL 2005 outside of a sandbox. There's a specific command for turning a machine case sensitive in 2005?

Assuming you've already turned your main box case sensitive before ghosting the image over to the other boxes, my thoughts are that the Ghost image software doesn't allow those kind of modifications. Another thought would be since SQL is supposed to be able to use the same collation that the Windows OSes use that you might want to check the server collation (not sure how) to see what it might be running as.

BTW, found the following quote out on the internet regarding SQL Server 2005:

http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

You must change the collation of the server while installing, if you want case sensitiveness. But the good news is, SQL Server 2000 lets you specify collation at the database as well as column level. That means, you can have a default SQL Server installation with case insensitive collation, but a specific database or a set of columns can have case sensitive collation.

Sorry I can't be of more help.




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[homer02]

Thank you very much for your input. We try to figure out why the process of changing system ID causes this kind of problem to the SQL 2005 server. When I checked the registry key, I still saw the old system name (the name before we ghosted the system,) not the current name. So , I think this may cause the error to the system.

 

[mrdenny]

When you change a servers name you need to change the local entry in the sysservers table.

You will need to run this code in SQL.

exec sp_dropserver 'OldServerName'
go
exec sp_addserver 'NewServerName', local
go

Then restart the server. This will correct the name change piece.

When you right click on the server and select properties and is listed next to the Server Collation?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[homer02]

Thanks for your input. I did this before, but this command did not change the system registry. This command worked well with SQL 2000. I guess, I need to automate the steps of changing the registry key when the system boot up the first time. Under security, login from the SQL Server management studio, I still can see the old server name, not the current one. So , I think this still causes the problem.