May 14, 2012 at 1:31 pm
Hi All,
I have a question which I don't seem to find proper answer. We have an application which uses SQL Server 2008 under development. Currently when we release new test version, we provide it to clients and they test it in their environment. Since app. is still in Development we have very frequent database structure changes and biggest drawback is clients lose their test data every time they update database. And I need to find a way to improve this process.
The most straight forward process will be to provide DB update script, and I know couple of ways to do this,
1) We have database under version control so it is very easy process to generate update script (just compare database with current build and have difference script generated in VS). But issue with this option is I don't have access to client's machine and if I use DB from some specific version and build update script based on that version then I might risk breaking something on client machine because it is very possible that they have changed something in their database.
2) Another method is to prepare empty database from current build and provide it to client and then I create some kind of script to find difference between old and new database and update old database structure. But issue with this method is, it requires lots of client side intervention and most of our clients don't have DBAs on site to help them.
Also, we can't use any 3rd party tool (free or paid) because again it will require us to have access to client database which is not possible.
3) I was thinking to have some way to script all old database objects out (other than data) and compare them with new database and then update old database (or generate script) based on differences. But I honestly have no clue how to achieve this in SQL Server.
So my question is, Is it even possible to do what I wanted to do in option 3 ? If so, does anyone has any suggestion ? If not, what other choices I have ?
Any suggestion is really appreciated.
Thanks,
Jack
May 16, 2012 at 7:09 am
Wow, not a single comment !! This looks like I am on my own :ermm:
May 16, 2012 at 7:18 am
The question you have to ask is this, if the database runs your application, should the clients be updating your application database? What if they drop a table which is critical to the operation of the app, or change a column from 1 data type to the other, how does the app handle this type of activity?
I appreciate the need for custom data as thats just the way it is, as each client has different data, but for them to be changing your schema, I would of thought what would of been a no. Granted you cant limit this on a clients machine as they have SA rights to which you cannot control, but you should put it in your terms and conditions / licensing documentation that changes to the schema are prohibited and could result in unexpected application behavour.
With this then in mind and you have cleared up that if the client does change the DB its their fault if the app breaks or the database cannot be upgraded then your into just create a SQL file which has all the changes between builds and ship it to the clients to run and upgrade their databases.
I have worked with a number of 3rd parties in my career and all of them will not allow you to make a change to their apps databases unless it is authorised by the 3rd party as you dont know what damage you can do to the application.
May 16, 2012 at 7:44 am
I think the problem lies in your point #1:
But issue with this option is I don't have access to client's machine and if I use DB from some specific version and build update script based on that version then I might risk breaking something on client machine because it is very possible that they have changed something in their database.
I am assuming from this that client(s) may have a different version of SQL Server from the one that you are developing on? Also, that different clients may have different versions? That seems like a fundamental problem to me.
Is there a minimum version they are required to have and are you developing on that version? i.e. Everyone must have at least SQL Server 2005 and you develop on that and also test in future versions to verify compatibility.
Also, if they have changed anything in their database schema, that will be a problem for any future updates to your application.
Jared
CE - Microsoft
May 16, 2012 at 10:34 am
Some good replies from Jared and Anthony. Here's my take.
I've mostly worked with third party software (like you develop) that did not allow any changes, even new indexes. However I have worked with some third parties that allow "new" objects, just no changes. If we implemented any FK constraints, we were responsible for removing them before upgrading. As Anthony mentioned, you have to write your terms into your EULA or contract with clients.
As far as your problem. Honestly, buy SQL Compare and work out a deal with Red Gate to drive it from the command line through the SDK for your clients. That's the easiest, and probably cheapest. Negotiate something with Red Gate. There may be other compare tools that have an SDK that I'm not aware of.
Disclosure: I work for Red Gate.
Why buy? Comparing objects is easy. Doing it thoroughly and in the right order for a change script? Hard, complex, lots of moving parts. A ton of work.
I'm unclear on one thing. Why do clients lose their test data? Are you dropping and rebuilding objects? I'd think that you could provide a script that does alters.
In the short term, here's what I think you should do. You ought to have copies of the "versions" of your databases live. Get them from VCS and then build out 4-5 of the last versions you have released. Then use SQL Compare (one copy) to build a change script to get from one to the other. Package all those scripts into an update, and use some code to determine which "version" the client is running, and run the appropriate script(s).
Not perfect, but nothing will be if you don't have some rules about the way you release changes and allow clients to alter databases.
May 17, 2012 at 3:36 pm
Thank You guys for your replies.
@anthony-2, I can understand your concern about having client SA level access. And it is true that they can modify that data. But as per our agreement with client(s), they are not allowed to modify existing database objects but if they think that they might see performance issue with something then we work with them to get is resolved, I dont think we have very rigid business model.
@Jared, you are correct. We have SQL Server 2008 or later (any edition) as requirement. And I think we can make sure that this requirement can meet.
@steve-2, I said client will loose data just to say that in our current test system we just restore database for clients to test (and so they loose data everytime we push new update, but I am going to create a script soon to prevent this) I don't have any issues with Red-Gate. In fact we do use few tools of red gate 😀 . But from my understanding each DB comparer tool compares two databases. What I need is a tool that can compare a database (which client will have) and a DB creation script (from our source control) and generate diff script based on them. Honest, I really dont know if such a tool exists or not.
Actually, we have an old application (that we are providing currently) is based on Foxpro (both UI and DB). And currently when we relese new database update, we use a 3rd party tool which just compares meta data of database and updates client database. I am hoping to find similar utility for our new in-development system (using SQL Server). But I don't think there is any way possible to compare metadata of SQL Server database without restoring it to SQL Server.
I know that we have very strange requirement and I am not hoping to find exact solution. I just wanted to know what others are doing to provide database updates to clients.
Thanks,
Jack
May 18, 2012 at 10:34 am
Hmm, I thought that Compare would work against a script. I see an option against source control, which doesn't help, but you can compare against a backup.
If you copied your dev database, deleted all data, and then ran a backup, you could drive the comparison against the empty backup (just schema) and get a change script.
I would ping the support people at Red Gate, or post in the forums there, and ask about this. I think you could do it with SQL Compare and they'd work with you on the licensing. Or if you have the SDK, it might not matter.
May 22, 2012 at 12:02 pm
If I am reading product feature correctly, it says it can compare "Folders of SQL scripts (for source control)" ...
Does it mean that it can compare with any version control database scripts ? Or just the one "SQL Source Control" ? (I guess I will post this question in RedGate forums ) .. also I need to test it in my test system and I will post my findings here
I will also need to understand how I will be able to distribute database updates to our customers using this tool
(all will be done once I get back from my business trip 🙁 )
Thanks,
Jack
May 22, 2012 at 2:47 pm
You can integrate VCS with SQL Compare (also from Red Gate) to build a set of change scripts from source control.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply