1

From blog.sqlauthority.com:

Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such as Siebel and SAP, they are tuned for specific application access paths by using many customized indexes. Ad hoc access or alteration to the underlying base tables of these applications can severely impact performance of queries and the application itself. Using schemas, a developer can logically group objects and even create custom objects without running the risk of messing up the underlying ISV database

I have a vendor MS SQL Server 2008 R2 database that I need to add tables to in order to track timestamps. However, I am not allowed to modify the vendor's schema in any way, which is the "dbo" schema.

Would creating a schema new schema in the same database sufficiently isolate my proprietary objects from the vendor's so as not to interfere with things like vendor updates?

5
  • 4
    This is a question to be asking the vendor. Jul 23 '15 at 20:48
  • I second Skagalak's comment. In a lot of cases schema means database structure, all tables and views regardless of what SQL Server schema they are in.
    – Bob Klimes
    Jul 23 '15 at 20:51
  • Why do your tables need to be in the same database? Could you further elaborate what you mean exactly by "in order to track timestamps"? Jul 23 '15 at 21:04
  • I need to add tables to in order to track timestamps what timestamps you want to trak ? Table creation? Why don't you create utility database and then query what you want to track and dump the results in utility database. This way you don't mess up the vendor database.
    – Kin Shah
    Jul 23 '15 at 21:06
  • I have already spoken with the vendor, and they will not support the application if the dbo schema is changed, including the addition of tables. I need to track the last_modified, and create_date of the row to check if they are greater than the last run for an export routine.
    – jwc
    Jul 24 '15 at 0:02
1

You will almost always be susceptible to vendor updates when it comes to schema changes and their app, especially when it comes to ERP applications such as SAP Business One or it's bigger brother.

The issue is how will their update scripts react to your table being added/modified? Usually it's not a big deal for me but sometimes it could cause massive problems, up to silently causing issues which no one finds out until they realize their billing data is wrong or something of the sort. If you include FKs which you probably will, note that changes to one table might also affect updates.

Problem is you need to actually make changes like this. My suggestion would be to be open with the vendor, tell them you need this functionality so they add it to their requested features and in the meantime try to get their support on making your own tables in their app's db. Their answer will most likely be 'we don't recommend it and we don't support it'. Thus you should always have steps on handling this data before and after upgrades. You could have processes based on how/when the data is accessed where you could remove the table or FKs and recreate after the update. You'd need to track the changes diligently.

Edit:

*and hire a QA contractor 1 time or utilize a good one to have tests written before/after upgrades that cover many if not all edge cases if it's really important.

Not the answer you're looking for? Browse other questions tagged or ask your own question.