Best practices for extending third party databases?

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 issue开发者_开发百科s promoting database changes from test to production.

---

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.

---

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.

---

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.

---

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.