Best RG option for simple DB dev

PeterDanielsCRB · March 27, 2020 4:45PM

I've struggled with implementing SCA with a complex (>6000 programmable objects, >800 tables) vendor DB that our BI team adds objects to (e.g. views, sprocs, etc.). Main issues have been:

1) # of objects slows ALL process - source gen, verify, build/validation, release deployment, drift compare, etc. 98% of these objects I don't even care about as they are vendor controlled, but our user objects (e.g. views) depend on them.

2) Cross server and cross DB dependencies in programmable objects (esp views) forced me filter objects and hack the source to remove.

What I really want is a more simplified src solution and migration automator that allows me to skip all of the build validation/drift checks/etc and just write and source control simple migrations. I have some powershell that can do some of this already. It just feels weird to own a $5000 piece of software built for managing DB source that I just push to the side. I want SCA/SSC to work, but it may not fit. Any guidance?

James_Murtagh · March 31, 2020 4:33PM

Hi @PeterDanielsCRB just to jump in here as I'm the product manager for SQL Clone. As David says this is something we're working on solving by leveraging SQL Clone, I'll reach out to you by email to discuss further.

way0utwest · March 27, 2020 6:21PM

Use filters to remove some of the complexity for things you don't deal with.

https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployment-with-sql-source-control-projects/using-sql-compare-filters-with-sql-change-automation-powershell-module?_ga=2.180461031.1773567949.1585151048-1603661970.1473869934

A few articles on xdb stuff. It's both common, but not too common and it's a very complex issue. We are working on what to do here.

https://www.red-gate.com/hub/product-learning/sql-change-automation/database-build-blockers-cross-server-database-dependencies?product=sql-change-automation

https://www.red-gate.com/hub/product-learning/sql-change-automation/resolving-cross-database-dependencies-in-sql-change-automation-using-local-databases-or-clones?product=sql-change-automation

https://www.red-gate.com/hub/product-learning/sql-change-automation/database-build-blockers-mutually-dependent-databases?product=sql-change-automation

PeterDanielsCRB · March 28, 2020 4:22PM

Thanks, Steve @way0utwest . Filters and project file hacks have provided enough relief to get it to verfiy & build. However, I have been unable so far to come up with simple logic (regexp/filter) to say that "these objects we want" and "these we don't". Unfortunately, "our" objects are intermingled with the vendors within the dbo schema. Don't ask.

Question, though - if I do find a way to filter off the vendor objects, won't we run into issues during build if our views refer to the vendor object? I'll try a POC...

Also, I've read some of those xdb articles. They are awesome. Not sure if it's laziness or being overwhelmed that keeps me from fully embracing and implementing.

Cheers,

-Peter

PeterDanielsCRB · March 28, 2020 4:27PM

I'd also like to see a verify/build validation process that allows us to take a "blessed" snapshot of the DB or existing target as the "starting place", so we don't have to actually build the full DB each time we execute one of these processes. Something like SQL Clone could participate in this design. My container skills are weaksauce, but I've wondered if they might apply in some way here, too. Keep some images of baselined environments (perhaps multiple DBs) that can "seed" a build w/o having to build from src.

David Atkinson · March 30, 2020 10:21AM

@PeterDanielsCRB That's precisely what we're working on. we hope to allow you to use an image created in SQL Clone in place of your baseline. This would be leveraged by the shadow database (to calculate the changes as dev-time) as well as the build database (which is like a shadow, but used as part of CI).

PeterDanielsCRB · March 31, 2020 2:32PM

Excellent. Would it rely on RG clones specifically? Or, might we get an option to work with other, more budget conscious cloning technologies like psdatabaseclone? Don't get me wrong, I understand the benefits of RG SQL Clone/Provision - just wish there were a few more tiers in the pricing model.

PeterDanielsCRB · May 13, 2020 8:44PM

Quick follow up to this thread. I did work with James and the team to go through this new capability 9using clones for shadow and build DBs. It is still being refined, but the essence of it is there and works. This is a fabulous way to avoid dealing with cross DB references in DBs. Nice work, RG posse!

Best RG option for simple DB dev

Best Answer

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk