moving a database

If we move our databases from one server to another and rename the new server to what the old one was and give it the same IP and then take the old one offline, will this have any impact on the applications that the databases connect with? Both Servers are running Windows 2003 Enterprise and SQL Sever 2005. Will the applications the databases connect with see it as the old server?

Also, will this move be an issue for reporting services? Will the report server see the reporting services databases as the same ones that were on the old server?

As long as DNS resolves the name to the IP correctly (which it should), that should work. One issue you will run into is the "orphaned user" problem (if you are using SQL Server "mixed authentication"). The SQL Server login on the new server won't be able to talk to the existing SQL Server database user in the database(s) that you move to the new server unless you fix the problem by running this command:

sp_change_users_login @.Action='update_one', @.UserNamePattern='usernameindatabase', @.LoginName='loginnameonsql';

You will have to do it for each user in each database, so hopefully you are using a single "application" user (to take advantage of connection pooling).

http://glennberrysqlperformance.spaces.live.com/

|||

Thanks for the tip. My understanding is that the copy database wizard will move the windows logins without an issue. All of our logins except for one are windows integrated logins, not sql logins.

Also, would you use the copy database wizard when transferring the databases between servers?

|||I would not need to worry about using a single connection (user) if all our logins our windows? correct?|||

it again depends upon whether u have Active Directory or not. If users are domain user you will not have any problem...

Madhu