July 21, 2005 at 10:41 am
Due to space and age issues with our current SQL 2000 server, we purchased a new server with much greater disk capacity. Now I am tasked with moving all our databases over to the new server.
I have read all the KB articles relating to performing this process and decided that using the Detach/Attach method would work best inour situation. My question(s) are as follows:
Instead of using stored procedures or the GUI to do this, can I not just shut down SQL Server and copy the files to the new server?
I know this is going to create issues with user access because of the SID's so my next question is:
After attaching the databases on the new server, can I not just delete all the user logins in the individual databases and set up new logins?
My reason for wanting to do this is because there are many user accounts listed that no longer exist in our orginization because of 'poor houskeeping' on our previous Admin. and we are also using groups in AD to manage user access which is much easier.
I believe I can resolve the DB ownership issue by changing ownership to the 'sa' or another designated user account.
I know there are other issues to consider regarding moving our SP's, Views, and DTS packages but our DBA will take care of those.
Any assistance would be great, I am going to attempt this in the next two days.
July 21, 2005 at 12:14 pm
Instead of using stored procedures or the GUI to do this, can I not just shut down SQL Server and copy the files to the new server?
You can copy the database files, yes. As long as SQL Server was shutdown correctly there shouldn't be an issue here.
After attaching the databases on the new server, can I not just delete all the user logins in the individual databases and set up new logins?
This depends and why would you want to go through all that work? If users own any objects, no. You'd have to change the owner. Also, unless you've used database roles, you'd lose all your permissions.
My reason for wanting to do this is because there are many user accounts listed that no longer exist in our orginization because of 'poor houskeeping' on our previous Admin. and we are also using groups in AD to manage user access which is much easier.
Your best bet here would be to identify what logins no longer need to remain and see how they are mapped as users in the databases. Then verify those users don't own objects. If they don't, revoke access to the databases and then drop the logins. BTW, if you're using Windows logins (and that includes groups), the SIDs will match up as long as you're not talking about different domains.
I believe I can resolve the DB ownership issue by changing ownership to the 'sa' or another designated user account.
Yup. Run sp_changedbowner and you should be fine. The only caution I give you is if you're using cross-database ownership chaining. By doing this you might open yourself up to cross-database security issue. If you aren't using cross-database ownership chaining, no worries.
K. Brian Kelley
@kbriankelley
July 21, 2005 at 12:27 pm
Thanks for the infoo. I should have mentioned that we only have one server and no cross database chaining is going on. As I said earlier, We are moving to use AD Groups to give users access to SQL Databases and to sort out the individual users that are no longer here or need access would take about as long as creating new logins for the groups.
It is too bad MS didn't figure a way yet to import users and groups from AD, or better yet, like Exchange be able to create properties for a user/group for SQL access right in the properties at creation.
I am going to test this out tomorrow, so will let you know. Then once I get this piece done, I have to work on our third party application the have SQL databases.
Thanks again for the quick response,
July 21, 2005 at 4:30 pm
You might know of this already, but we went through a similar thing in March and found this info to be helpful:
August 22, 2005 at 10:00 am
You know, you can recreate those logins on the new server so that you don't orphan them. As long as your old server is still running, you can have it create a script to recreate all the logins and passwords. Then take that script to the new server and run it. It will recreate all the logins and then all you have to do is restore you databases. You shouldn't miss a beat. See this article.
http://www.sqlservercentral.com/columnists/glarsen/migratingloginstoanotherserver.asp
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply