August 26, 2010 at 3:46 pm
Here's our situation, we currently have multiple databases that sit inside one instance. They are "organized" by subject matter. Our data model is extensive; 1100 tables. So let's assume a fictious database name called CUSTOMER inside that sits tables related to this subject. We also have a database called ORDERS. So today I query:
select * FROM CUSTOMER.DBO.MY_CLIENTS
select * FROM ORDERS.DBO.DOMESTIC_ORDERS
With 2008 comes some improved schema management features and I can start to think about migrating from the current approach to leverage schemas. One database to back up now with multiple schemas; i.e.
select * FROM MY_DB.CUSTOMER.MY_CLIENTS
Now we have customers with a 1TB+ database. As part of this rollout I'd need to think about how we migrate from:
CUSTOMER.DBO.MY_CLIENTS to MY_DB.CUSTOMER.MYCLIENTS
Is there any easy way to do this? I really don't want to have to reload data into tables.
Any advice would be greately appreciated.
August 26, 2010 at 3:56 pm
The short answer to you question is NO.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 26, 2010 at 4:12 pm
typically these kinds of operations require data copy. Furthermore there will be code changes and permissions changes. This should require substantial planning.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2010 at 4:17 pm
Thanks for the quick fire responses. We're in the financial services software business so most things do require a lot of planning 🙂 Code wise we've got some 10,000 procedures and functions to review.
I've also pinged our Microsoft channels to see if there is anything out there that might help...
Thanks again.
August 26, 2010 at 4:23 pm
To move tables from one database to another you will need to create new tables and copy data over. There's no 'renaming', the tables in different databases will be in different files, their structure stored in different system tables, data location tracked by different allocation structures.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2010 at 4:59 pm
You're just going to have to do the work. You will be able to code & automate some of it, assuming your db & schema names map reasonably well. But there's no getting away from the work. The really nasty part isn't even moving 1000 tables. It's going to be updating 10,000 stored procs.
You might want to take a look at Visual Studio 2010 database projects (using what used to be called Data Dude). It has a rename refactor that will find all the instances of an object and change the name appropriately. That'll help (although, you have a ton of coding in front of you). But you'll still have to do the data migration.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2010 at 5:24 pm
Others have already mentioned there's no easy way to do this and you have a lot of work in front of you. I'd like to add that you should take this opportunity to look at all your databases and redesign the system. If you just move everything and use schemas for separation, you're likely going to have the same information stored in many places at the same time, giving rise to corruption and errors. With 1100 tables, I'm betting you could cut that down by a sizeable amount, making the resulting database less complex and more usable. Look into leveraging newer features and datatypes. If you take this opportunity to move to SQL Server 2008 (if you aren't already there) there are some nice space-saving features there.
Since this is going to take a lot of time and code-review anyway, the extra time for the redesign won't add too much to the project and you'll end up with something much better than just consolidating all the databases into one.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 27, 2010 at 7:53 pm
Thanks for everyone's responses. Definitely agree there is work ahead but the cost savings of managing single digit instances of SQL Server across our R+D teams is signficant.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply