can i ignore certain db objects?

merk

Unfortunately i have a few stored procs which differ from the dev database compared to the production database. So, i'd prefer it if sqlcompare would ignore these, since i absolutely do not want to copy the dev version into the production db.

So i'd like to know if i can tell sql compare to ignore changes - preferably ignore them just when on a specific db. i.e. if sp_mySproc changes, notify me when i an looking at the dev database, but don't tell me when i am looking at the production db.

Thanks

David Atkinson

You have posted to the SQL Source Control forum. Could you confirm if your question is about SQL Compare or SQL Source Control?

In SQL Compare you can use the Filter panel to exclude objects. You can save the filter settings with a saved project.

This isn't yet possible in SQL Source Control.

David Atkinson
Product Manager
Red Gate Software

merk

I am indeed talking about sql source control. Is this something that's going to be added in?

David Atkinson

Yes, this is on the roadmap for hopefully later this year. It would help us when designing the feature to understand why you have objects that differ that you wouldn't want to commit to source control.

David

wtjones

The main reason I want this is because we develop our own objects on a vendor-created database and are not as concerned with source controlling their code.

It would not be a huge deal to have then in SC except that we have a large number of objects in the db and I suspect this as the reason for slow performance.

merk

For me, in this instance, i have linked servers and the server names/db names differ between dev and production. So my stored procs will always be slightly different between dev and production. I couldn't find a way to use some sort of aliasing system so that i could keep the stored procs in sync. So right now i always have some stored procs showing up as needing to be committed, or updated, since they have different linked server names in them.

i also occasionally comment out sections of code in my dev or prod stored procs since some of it's for debugging purposes.

David Atkinson

@merk - that's useful information. You're using filtering to ignore known stored procedure differences as a workaround for linked server name references. This is an issue we anticipated, but so far we've not had anyone bring it up just yet. I'd be very interested in hearing from any other users who are experiencing this. Clearly, if this is a pervasive issue, we should be looking at addressing the root cause and devising a mechanism where these different server name references aren't regarded as actual differences.

David

merk

David Atkinson wrote:

@merk - that's useful information. You're using filtering to ignore known stored procedure differences as a workaround for linked server name references. This is an issue we anticipated, but so far we've not had anyone bring it up just yet. I'd be very interested in hearing from any other users who are experiencing this. Clearly, if this is a pervasive issue, we should be looking at addressing the root cause and devising a mechanism where these different server name references aren't regarded as actual differences.

well ideally, it would be nice if i could specific a string that would get auto-replaced when moving from one server to another.

ie on dev it's linkdevserv. DevDb.dbo.devtable
and when i'm pushing changes to the production db, it should automatically convert linkdevsrv to linkprdserv and devdb to proddb, and should ignore differences in that text when doing a compare.

I didn't think that would be something that would be included in sql source control, which is why i asked about ignoring files, which is obviously an imperfect workaround.

David Atkinson

Some sort of server name mapping or parameter substitution could maybe used to solve this issue. I expect we'll get an increasing number of questions about this over the coming months. At least you'll have some sort of workaround once we've added object filtering!

David

jim.whaley

We are running into an issue with synonyms.

We have a reporting database that looks at a replicated (MSSQL replication) copy of our production database.

We use synonyms in our reporting database to point to the replicated production database.

I don't really want or need the synonyms in source control. When we deploy a new report database, we simply run a proc that automatically creates all of the synonyms.

Sure would love the ability to exclude things from source control! :-)

Jim

David Atkinson

We're working on adding the same filtering screen you currently get in SQL Compare. This will filter the Commit list to exclude objects you specify. The idea is that the filter is saved as a per-project setting, which means that all users connected to the same repository will inherit the filter.

Comments on this approach are most welcome! As soon as we've got something ready, we'll release this as an Early Access build.

David Atkinson
Product Manager
Red Gate Software

ahmannj

As long as you're looking for feedback... I'd love to exclude Users, Permissions and Role Memberships - since they always differ between our development environment and our higher test and production environments. I'd prefer to exclude them from our source control system, rather than from the sync process during promotion to the higher environments.

David Atkinson

Thanks for the feedback. Let me know if you would like to be added to our SQL Source Control early access notification list? This way you’ll be notified when we have a Beta build to try out, and you’ll have the opportunity to let us know if it meets your needs.

Kind regards,

David Atkinson
Product Manager
Red Gate Software

ahmannj

I'm on the list already - thanks!

bradtoast

Could you please add me to the list? I would also be interested in having the ability to have a config file where I could set variables to be used in creating synonyms that reference other databases where the names might change from environment to environment.

bradtoast

Forgot to add that this is one of a few things that caused us to go with DB Projects in VS 2010 rather than Red-Gate SQL Source Control at this time. Of course there are a few other things related to Circular Reference (Multiple Databases that all have Synonyms that reference each other) that has even been difficult to overcome on VS. If I get some time I will try and document all the issues we have ran into and the workarounds we came up with to overcome those hurdles. I would love to use SQL Source Control instead, but it's just not quite there for our complex needs.

David Atkinson

@bradtoast - I've added you to the list. Regarding supporting variables, it's something we're actively considering. We're thinking hard about the possibility of supporting the database project as a back end, which would mean also supporting features such as variable substitution. However, we don't have any hard dates just yet. We'll keep you informed now that you're on the list. Please feel free to ask any further questions.

Kind regards,

David Atkinson
Product Manager
Red Gate Software

davec

Hi,

We have a replicated database bound to source control and are running into a similar problem by not being able to exclude the replication elements that are created dynamically for each instance.

We have 3 different development sql servers in 3 different companies all using the same database schema through TFS and Redgate source control.
It's not going well at the moment.

Are there any tricks you can share for when you bind a replicated database to source control?

Thanks in advance.

David Atkinson

Source controlling replicated databases may be problematic. Which flavor of replication are you using? Could you post an example of the replicated objects that are problematic?

David Atkinson
Red Gate Software

davec

Hi David,

We are doing merge replication and the objects that are giving us grief are all the triggers, etc that include a GUID supplied by the server when replication is turned on.

i.e.
create trigger [MSmerge_del_6B0A0FF8ADDE416C9BF3853E8769F39A] on [server].[dbo].[tablename] FOR DELETE AS
declare @is_mergeagent bit, @at_publisher bit, @retcode smallint

Replication also adds constraints, indexes and statistics. These also need to be filtered out for a replicated database to be bound to source control when shared.

David Atkinson

Have you tried the Ignore Replication Triggers option in SQL Compare?

davec

But this issue is with SQL Source Control...

Am I missing something?

David Atkinson

Ah... sorry, my bad. You're right.

We're hoping to add the SQL Compare options to SQL Source Control at some point. We don't have any precise details right now, but we'll respond to this thread when we've done the work.

David

davec

Do you have any recommendations on how to deal with replicated databases in source control in the mean time?

Should we have one database that isn't replicated and bind that to source control, and have another replicated database that isn't bound to source control. Than keep the unbound db up to date using SQL Compare...

Is that the best we can do for now?

David Atkinson

I suspect that you're right and you simply have to not have replication in source control as you suggest.

I'd appreciate it if you could try this out and let us know if this is a viable workaround, as it's not the first time we've been asked this!

Many thanks,

David