locked
Move all SQL server databases to one SQL server RRS feed

  • Question

  • Good Day Friends,

    I need your help since i am not SQL guy but i think with your help i can manage my case...

    We have 3 SQL servers 2008 R2 Std installed with their Application on 3 stand alone servers on WORKGROUP, A. each server has his own databases (arround two deferent instance per each) B. Each database has sql server authintecation with deferent user and password.

    My plan is to have One SQL server (2008 R2 std) with 1. centralized 2. on Hyper V virtualization 3. Joined to domain... 4. I need ur recomendation for the type of authintication.

    so i need to move the databases from 3 workgroup servers to my new VM domain joined sql server.

    Any Ideas?!

    Thx

    Ahmad


    Ahmad Taha

    Thursday, December 13, 2012 7:07 AM

Answers

All replies

  • Use backup and restore to transfer the databases?

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, December 13, 2012 8:18 AM
  • More details please... currently manual backup is performed.


    Ahmad Taha

    Thursday, December 13, 2012 9:41 AM
  • What more details do you need about backup & restore?

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, December 13, 2012 9:43 AM
  • ok,,, for the three servers there are t databses inside, you mean i have to take backup for each server and restore all the backups on new "one" sql server, what about the authintecation? what abut database admin accounts? what about master database? servers on work group and new one on domain is this will cause any issue using backup and restore? what is the next step after restore since i will uninstall the sql from the three servers and keep only the apps there!

    Ahmad Taha

    Thursday, December 13, 2012 9:48 AM
  • You should ask those questions in an Administration forum, not in a SSIS forum.

    There are too many unknown factors: how is authentication defined on the 3 source servers. Is there encryption? Are there cross-database dependencies?


    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, December 13, 2012 9:50 AM
  • Authintecation i aready mentioned it sql authintecation, no encryption, all databases independat some itegration on application level only

    Ahmad Taha

    Thursday, December 13, 2012 9:52 AM
  • With SQL Authentication you'll have to be carefull you don't get orphaned users. You'll have to move the SQL logins as well.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, December 13, 2012 9:57 AM
  • Hi Ahmed

    There are many factor that need consideration.

    But its best to use backup restore for database move & windows authentication in place of sql authentication. Windows authentication is more secure.

    You can have 3 windows users for connectivity, also remember to remove junk users.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, December 13, 2012 10:33 AM
  • Could you please provide me a good way or instructions of how to take a backup with the logins and all needed for moving to new server with also windows authintecation not sql authentication


    Ahmad Taha


    Thursday, December 13, 2012 10:41 AM
  • Hi VedPrakash786, 

    We can design a package contains 7 containers and each container has contained 3 tasks: a Data flow task, A ForEach Loop task, and a Execute SQL script task to achieve your target, detail steps please see:

    Automatically Transfer and Synchronize SQL Server logins, SIDs, Passwords and Permissions:
    http://www.msbicoe.com/post/2012/04/05/Automatically-Transfer-and-Synchronize-SQL-Server-logins-SIDs-Passwords-and-Permissions.aspx
    Thanks,
    Eileen


    Eileen Zhao
    TechNet Community Support



    • Edited by Eileen Zhao Friday, December 28, 2012 8:20 AM
    • Marked as answer by Eileen Zhao Thursday, January 3, 2013 12:56 AM
    Friday, December 28, 2012 8:18 AM