Migrating smaller DB's onto one server

Question

I have many small in size SQL servers that we are considering migrating onto one server, for cost savings.

Before migrating these onto one box, I thought it best to do some performance metrics over time to make sure I can combine all of these smaller db's onto one server without performance issues.

What would be the best way for me to insure that I can migrate sucessfully?

---

qeqw

Answer 1

Hello,

I would use stored procedure “sp_monitor” to verify on each instance where those database reside if the instances are read intensive (use total_read) or write intensive (use total_write). Hopefully you will have a mix of read-intensive with write-intensive databases.

Use system monitor to measure the following counters on all those instances:

SQLServer:Locks -> Average Wait Time (ms)
SQLServer:Databases -> Transactions/sec
Network Interface -> Bytes Total/sec
SQLServer:General Statistics -> User Connections
SQLServer:Access Methods -> Page Splits/sec

The following query will let you know the amount of RAM each database uses:

SELECT            CASE database_id

                                    WHEN 32767 THEN 'ResourceDb'

                                    ELSE DB_NAME(database_id)

                        END AS 'Database Name',

        COUNT(*) AS 'Cached Pages Count',

                        COUNT(*) * 8. / 1024 AS MB

FROM sys.dm_os_buffer_descriptors

GROUP BY DB_NAME(database_id) ,database_id

ORDER BY MB DESC

Hope this helps.   
 
Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

thanks alberto!

---

qeqw

Answer 3

Performance is actually one of the easier problems to solve when consolidating databases. With the computing capacity you can get out of a commodity server today, a 2 socket pizza box can give you as much computing power as an enterprise class server as recent as 4 years ago.

The one area in performance that hasn't gotten easier to deal with is storage so pay careful attention to the IOPS and MB/s requirements of the databases. HBAs are expensive little critters if you're using a SAN and if you're using only local storage, you run a real risk of running out of storage capacity (IOPS and MB/s, not space) long before you hit your server's ceiling on CPU or memory.

There are papers on consolidation on www.sqlcat.com and msdn/technet. Harder problems to solve are security requirements, namespace clashes, availability SLAs, event maintenance jobs. If you have access to TechEd or PASS summit conference materials, there are presentations there too that will be useful.

 

---

No great genius has ever existed without some touch of madness. - Aristotle