January 30, 2012 at 7:01 am
Hi,
I am looking for a way of upgrading a our customers sql databases to the latest schema without the need to directly compare the database to a master copy.
unfortunately the customers database will be of an unknown version so I need to a scrip that checks to see if the object exists and it matches the one form the release schema and if not upgrades it's to match the latest schema.
anyone know of any tools that might be able to do this?
January 30, 2012 at 7:51 am
markd 82708 (1/30/2012)
Hi,I am looking for a way of upgrading a our customers sql databases to the latest schema without the need to directly compare the database to a master copy.
unfortunately the customers database will be of an unknown version so I need to a scrip that checks to see if the object exists and it matches the one form the release schema and if not upgrades it's to match the latest schema.
anyone know of any tools that might be able to do this?
Is this an "application" upgrade? You can query system tables agaisnt a list of expected objects then decide what to do depending on existance or not of specific objects.
_____________________________________
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.January 30, 2012 at 7:51 am
Ouch. Sounds like you have SDLC issues.
Is there a reason you can't use the Information_Schema and sys.objects tables / catalog views to do the checking with each addition / change / deletion you are coding in each individual release?
January 30, 2012 at 8:27 am
I'm doing this via an application, but I've always found that comparing a schema is not enough;it's too complex for a TSQL script to fix or recover form errors.
at least at our shop, if we added a new table to our releases, depending on their purpose (for example a new lookup table)
they also have default rows....so a program which just finds missing DDL items might put the table in place, but the the applications which use that database might not look right, or run correctly, since there would be a data that is assumed to exist, but isn't there...you end up with drop down menus with no values, etc.
it's gotta be a pretty robust solution to fix them all.
I do it by creating an xml file with the expected schema featuring a suite of items to review in it, as well as the expected rows of data for a given table...
my comparing application requires the DBA to make a connection to the Target Database, and the xml file is used to check for missing tables/columns/procs etc.
in that way, a client in say, California, only needs my application and the xml file to run a comparison, and not connect to my "perfect" database schema out here in Florida.
Lowell
January 30, 2012 at 8:35 am
PaulB-TheOneAndOnly (1/30/2012)
markd 82708 (1/30/2012)
Hi,I am looking for a way of upgrading a our customers sql databases to the latest schema without the need to directly compare the database to a master copy.
unfortunately the customers database will be of an unknown version so I need to a scrip that checks to see if the object exists and it matches the one form the release schema and if not upgrades it's to match the latest schema.
anyone know of any tools that might be able to do this?
Is this an "application" upgrade? You can query system tables agaisnt a list of expected objects then decide what to do depending on existance or not of specific objects.
yes and we are about to start a major new version and i am look for the best way to fix this issue going forwards.
January 30, 2012 at 8:37 am
If you're going to a latest schema, you could use SQL Compare from Red Gate. There's an API for scripting, and you could compare to a backup file or even series of scripts to check against the customer's database.
Disclosure: I work for Red Gate.
January 30, 2012 at 8:37 am
Brandie Tarvin (1/30/2012)
Ouch. Sounds like you have SDLC issues.Is there a reason you can't use the Information_Schema and sys.objects tables / catalog views to do the checking with each addition / change / deletion you are coding in each individual release?
we have had SDLC issues in the past but we know have some robust process in place so i can craete a script to upgrade version to version and we can track what version customers are on and this is one process that we are considering but it jsut looking for the best plan.
January 30, 2012 at 8:41 am
markd 82708 (1/30/2012)
Brandie Tarvin (1/30/2012)
Ouch. Sounds like you have SDLC issues.Is there a reason you can't use the Information_Schema and sys.objects tables / catalog views to do the checking with each addition / change / deletion you are coding in each individual release?
we have had SDLC issues in the past but we know have some robust process in place so i can craete a script to upgrade version to version and we can track what version customers are on and this is one process that we are considering but it jsut looking for the best plan.
i have your compare tool but i am unaware of the API? is this included as standard or is a dev lience required?
i am also look at you DB source control software to live a long side SubVersion that we are using for the application soruce control.
January 30, 2012 at 9:05 am
I thought the API was included, but it's separate.
There's an SDK
http://www.red-gate.com/products/sql-development/sql-comparison-sdk/
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply