We have a mutli-tenant shared schema database architecture. As a form of load distribution we have spread the tenants/companies across 6 SQL 2014 servers and 20 databases. All DBs are virtually the same except they hold about 10K different companies combined. We need the ability to extract entire companies out of one database and move them into another – some 200 tables. Ideally, we would like to be able to move them with minimal downtime. I am exploring ways to make this happen short of just writing custom scripts to copy every single table.
I learned you can setup replication with a filter based on a view (selectively extract company x data), but then I would have to setup replication for 200+ tables, which sounds cumbersome and could adverse impacts on production. Partitioning can do wonders, but not every table has a neat CompanyID reference. We don’t have AG but are planning on it and thought we could then use the replica to extract the company data as to not impact production.
Best Answer
Sounds like a job for SSIS and the Transfer SQL Server Objects Task https://msdn.microsoft.com/en-us/library/ms141134(v=sql.120).aspx
Make sure you install and configure SSIS, and then you should be able to do this with ease.