I have a situation where our developers extended a Third party database (MS SQL) by adding tables, views, stored procedures, and functions. Recently when the vender issued updates to the database they dropped all of our custom objects. The question now is what are some best practices that will allow us to extend the third party database but keep our objects safe from future updates? My first thought is to create a separate database but then I’m stuck with fully qualifying all the references back to the original database which may cause issues promoting database changes from test to production.
A:
Either use the external database or try storing the custom objects in a new schema. Whenever you run a vendor supplied script, be sure you use an account which doesn't have rights on that schema.
Sam
2010-03-16 18:47:26
@Sam: a separate schema may help, but if the 3P is really against extension, they could remove all schemas but their own.
John Saunders
2010-03-16 18:48:25
Depends what security their account has.
Sam
2010-03-16 18:50:41
@Sam: I figure it's their database, their "install" account likely has full privileges to it.
John Saunders
2010-03-16 19:11:36
@John: No doubt you've seen the vendor install routines which require the SA login info. Lovely work some of these vendors do.
Sam
2010-03-16 19:16:59
@Sam: I've worked at a company whose product created the DB with a new username, and they properly used that new admin username, and they're exactly the type who would love to wipe all your customizations. I've seen their database slow to a crawl because of user customizations.
John Saunders
2010-03-16 19:41:49
@John: Sounds like a good 3rd party product.
Sam
2010-03-16 20:55:43
+3
A:
The best practice is to have a conversation with the developers of the Third Party product you purchased. They may not have designed their product to be extended. You need to talk to them to see if they can make extensibility a feature.
They will likely want to restrict the ways you can extend the database. Some of your extensions could cause problems with a carefully-tuned database.
John Saunders
2010-03-16 18:47:42
..and the next revision, pow! everything is obfuscated and/or encrypted! depends on if the vendor charges for custom changes, or just sells a canned product. They may void your tech support. I would not want to support a product where the customer was messing in my DB.
KM
2010-03-16 18:52:45
in this case we are only adding objects and NEVER changing any of the venders code
Eric Watkins
2010-03-16 19:01:44
@Eric Watkins, that is what everyone says just before the `woops, oh no, call support!`
KM
2010-03-16 19:09:25
@Eric: do any of your stored procedures reference vendor objects? Then your queries might cause deadlocks. The vendor did not tune his database with your changes in it!
John Saunders
2010-03-16 19:10:49
@John: As far as I can tell the Vender has no idea what optimization is :-) I have not come accross anything custom that does updates but I've also not seen a lot of With(nolock) either.
Eric Watkins
2010-03-16 19:33:20
+7
A:
The only safe way is with a different database.
You could try setting up permsiisons so they can't drop your items, but I'll bet as a vendor, they have SA access.
If you are on SQL Server 2005 or up, you could try using a different schema, but the developer could still drop all of your items.
KM
2010-03-16 18:48:19
+3
A:
You should check your license agreement with the vendor as well to make sure you're not violating the agreement. Some software vendors explicitly prevent clients from making schema changes in their license agreements. Many firms take the stance that you own the data, but the schema is their intellectual property. Further, even "simple" changes to the schema could result in issues within the application itself. The vendor has to have some level of assurance that the bits installed on your site are indeed the same bits they shipped and tested.
Tim Lentine
2010-03-16 19:05:28
In a lot of cases, your warranty / support agreement is voided if you make unauthorized changes to an off-the-shelf application
Raj More
2010-03-16 19:11:15