May 29, 2009 at 2:55 pm
Good afternoon all,
I have been tasked with upgrading from SQL Server 2000 to SQL Server 2005 (about time, huh?) and on SQL Server 2000 we have several databases (hosting Project Server 2003 data), but on SQL Server 2005 they want all those databases combined into one, I'm guessing separated by schema (none of them know how it's done, they just want it done).
Has anyone experienced this before? If so, are there any helpful scripts/tips/suggestions?
Thank you,
-BJ
May 29, 2009 at 6:51 pm
Eek! What is the rationale behind combining the databases? Are they all related somehow?
May 30, 2009 at 2:07 am
Haha, I said the same thing, "Eek!" The intention is to have a single point of connection, for example, say we have 10 different instances right now, users access their Project Web Access through the URL of their specific instance (i.e. https://projectserver/programA, or https://projectserver/programB, etc.) because each instance has its own respective database in SQL Server 2000. The goal is to have every user access https://projectserver/ and have their rights/associations direct them to whichever program/project to which they are assigned. I'm not too worried about the PWA side of things yet as I don't think that will be the big hurdle, I think the SQL 2000 -> SQL 2005 migration/merge will be the big hurdle...unless anyone thinks otherwise.
Thanks for the reply, hope that helps explain a little more!
-BJ
May 30, 2009 at 2:36 am
It seems to make sense, I suppose (no point me asking why it wasn't done that way from the start).
Are the tables/fields in all the databases the same? Are you looking to combine them all, or merely (as you suggest) to create multiple schemas - effectively keeping things separate?
Option 1 is the more appealing from a db purist point of view, but one hates to imagine what sort of PK/FK issues you would run into.
Option 2 sounds fairly simple, but the benefits of having a single multi-schema database are dubious (to me anyway).
There are others in this forum who will have more useful comments on this, I am sure. You should consider posting in the 'Administering' forum too - this is not really about SSIS ... yet!
Phil
May 30, 2009 at 10:53 am
Yes the fields in all the tables are the same, however, if all the information from each table were the same, just from prior experience with Project Server and the way it stores data I'm not sure (without having a test bed yet) how it would handle the separation.
Thanks for the insight, I will post on that forum too!
-BJ
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply