Partial source control or code only database

jl@energidanmark.dk

Hi

We have some databases from vendors, that isn't controlled by us.
But we do have code as views, udf's and sp's on these databases.
Some of the code are very complex, and we want to source control it, but it doesn't make sense to source control the complete database.

As i se it we might have to possibilities:
1)
Source control of everything with specified schemas on vendors database. If possible?

2)
Make a new "integration" database on same SQL instance as vendors database.
This database will be source controlled, and just contain code.
The code refers to data in the vendor database.
But what about performance in this scenario?

I hope some of You guru's can help us in the right direction.
Thanks in advance.

Jesper

Rob C

Hi Jesper,

You can use filters to ensure that only objects of a specified schema are included. Here's a screenshot that shows how to set up the filter rules - replace schemaName with the name of the schema you want to source control and only objects belonging to that schema will be considered.

jl@energidanmark.dk

Thanks Robert,

This is very nice to know.

However, there is a reason more for option 2.
Security, "noob" SQL developers having to many rights on a production database.
Development of some Stored Procedures needs high privileged rights.

Any comments on option 2?

/Jesper

Rob C

Hi Jesper, apologies for the delay in getting back to you.

Having a separate database on the same instance is difficult would work, but it's awkward because you would now have related objects in two separate databases. I'm not certain what the performance hit would be, but that's more likely to be something that's determined by SQL Server itself than our tools.

Have you thought about having a separate development instance that is a copy of the production database? That way, developers can work on it without fear of breaking the production database. When you are ready, you could deploy the changes using our SQL Compare tool.

jl@energidanmark.dk

Hej Robert

We have tried source control on a named schema.
And we are not convinced.

Then we are using objects not in the schema, source control keep asking, if these objects should be source controlled.
Then we have to remove a check mark and press ok, some day someone forgets this.

We have searched for how to change the default behavior, without luck.

/Jesper

Rob C

Hi Jesper,

You can set the behaviour of this checkbox by editing your %LocalAppData%Red GateRedGate_SQLSourceControl_CommonUI_UIOptions.xml file. Between the opening and closing UIOptions tags, include the following element...

<IncludeDependenciesCheckbox>False</IncludeDependenciesCheckbox>

This will set the default behaviour of the checkbox to be unselected. Be careful when not committing dependencies as if it makes the repository invalid, it could break the get latest tab for any other developers.

I hope this helps.