May 26, 2009 at 9:31 am
I would like to hear if somes of you had had that situation and what strategies were used to overcome the problem. I've posted here for a strategy even if the SQL version that will be used is 2008.
There's a central sql server (workgroup edition, 2008) which gather data from multiple other sql server (workgroup edition 2008 also) instances. Both the central one "publisher" and all "subscribers" send data to each other ("subscribers won't exchange data amongst them"). Publisher and subscribers will also be used locally to enter new data in their respective database.
The main problem point is subscribers database version could be a different version from the publisher. In other word the publisher will always get the latest version of the deployed database modifications but subscribers might not and this would have to be managed somehow when exchanging data between them. Yes a column could be available in one database and not the other, this is a scenario that I need to plan.
SSIS within workgroup edition functionalities could be used, custom .Net and or T-SQL could also be used.
I would like to propose a WSUS architecture like or an auto-updater to manage application and database deployed version but I believe they will be rejected as this is a strong business requirement to manage multiple version.
Thks
May 26, 2009 at 12:38 pm
Megistal (5/26/2009)Yes a column could be available in one database and not the other
Starting on SS2K5 all DDL is replicated by default so publisher and subscribers would automatically have identical table schemas.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 26, 2009 at 12:47 pm
Hi PaulB
I've used the replication terms of publisher and subscribers to help understanding what will go on only. The workgroup edition does not allow any publisher to be setup, only subscribers. Therefore, the replication mechanism will mostly be custom.
What I'm looking for is ideas / structure / POC / tests (even failed one to not reproduced those errors) on how to handle multiple database version in the stated architecture.
What I could add to help understanding is there's an application that run on top of each databases and that application need to be in sync with the DB structure underneath. Having the database structure changed without notifying the application or updating it would be a scenario to avoid at all cost.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply