Consolidate databases in to single SQL database

Question

Dear All,

I am an Oracle dba and new to Ms SQL. I have to do database consolidation job. I have number of Ms SQL 2005 servers and each has around 20 databases. Our goal is consolidate all these databases in to single instance.

01) Is this possible to do ?

02) If yes, In which I need to start?

03) what are general issues I have to face with this task?

Highly appreciate your advice.

Thanks and regards,

Manjula

Answer 1

There is a lot of material available on this topic. Here are some links you can refer:

http://technet.microsoft.com/en-us/library/ee819082(v=sql.100).aspx

http://blogs.msdn.com/b/mvpawardprogram/archive/2012/01/09/consolidating-sql-server-databases.aspx

http://www.mssqltips.com/sqlservertip/1320/sql-server-consolidation-pros-and-cons/

http://www.youtube.com/watch?v=8X3cEhfN8Hc

---

- Vishal

SqlAndMe.com

Answer 2

Certain steps you have to prepare before migration

Backup of databases (Also What if multiple server has same DB name)

Logins :-  http://support.microsoft.com/kb/918992/en-us

Jobs

SSMS->click on jobs->press F7 for object explorer-> select required jobs -> right click and script it

special handling like

Linked servers / SSIS  connectionstrings if used in any of current servers

Replication/mirroring/logshipping if anything in use

This will give you a good start and if there are any minor setting you can handle it later on while testing.