Separate databases for configuration, stored procedures, and data

Question

Separate databases for configuration, stored procedures, and data

Walter Levy

Mr or Mrs. 500

Points: 540
More actions
October 13, 2014 at 12:58 pm

#312180

I am working on a new data warehouse project with complex validation and transformation requirements for which we store a lot of metadata used to build dynamic SQL strings. The ETL database, therefore, holds all of the logic and metadata. Environment-specific configuration data, ETL batches and auditing data, is stored in a separate database, and staging and ODS data in their own databases.
In my view this allows for a dead-simple deployment simply by doing a backup and restore of the ETL database which is essentially the source code.
I'm getting a lot of push-back on this design. The client wants to keep the configuration data, ETL batches, etc., together with the ETL database. Because of this they want us to deploy updates using SQL scripts which would include synchronizing changes to schemas, programmable objects, and metadata.
Client is concerned that separating the configuration data from the ETL, ODS and DW databases will impact performance, but there isn't much of it and I don't think it would be a problem at all.
Am I off base with this design?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734542 More actions · Answer 1

No, your design is not off, but you should consider the case where I separate a mart or ODS from the ETL store or other DW. In that case, do I want config information in a separate instance/db?

I tend to like databases to be fairly self sufficient where I can. I think I might keep the setup information with each db, but maybe I'd have a source database where I keep this and use some job/query/replication to push it to other locations.

Walter Levy Mr or Mrs. 500 Points: 540 More actions · Answer 2

Thanks for your feedback, Steve. (BTW, I've been following your work on SSC for a long time.)

I probably won't prevail in this disagreement with the client, but it's good to have some validation that I'm not going off the deep end in my design. At least I'd like to keep this separation during the primary development phase where there are likely to be substantial changes... it would be a simple matter to bring the external stuff back into the ETL database when we're at a more stable point. Nevertheless, I'm looking at Redgate SQL Compare for migrating changes to schema, SPROCS etc., and data. I've never used it but it looks like that would be the best tool to generate change scripts.

Steve Jones - SSC Editor SSC Guru Points: 734542 More actions · Answer 3

Good luck, and if things don't split, you'll be fine either way.

SQL Compare does a great job, and good luck with it.

Disclosure: I work for Red Gate, which makes SQL Compare.

Grant Fritchey SSC Guru Points: 398711 More actions · Answer 4

I also work for Red Gate. You can throw my opinion away as appropriate.

I'd also suggest looking at SQL Source Control to get your database into source control as a management mechanism for your deployment processes.

By and large, I agree with you and Steve on the design. Especially any kind of ETL or staging processing, keeping that in a separate database is a good idea.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Walter Levy Mr or Mrs. 500 Points: 540 More actions · Answer 5

Thanks, Grant and Steve. I'm trying to get SQL Server source control implemented here and Red Gate looks like a solid choice.

I was able to convince the client to go with my design, so for the moment a scripting solution isn't critical for this project. It's a fair amount of extra work to make almost everything dynamic but I think in this case it makes sense.

I had some concern that we'd take a performance hit on the reporting side if using dynamic SQL. To avoid that I plan to propagate some tables to the ODS and create the reporting SPROCs there.

Thanks again for your advice!

Steve Jones - SSC Editor SSC Guru Points: 734542 More actions · Answer 6

Steve Jones - SSC Editor

SSC Guru

Points: 734542

October 15, 2014 at 8:39 am

#1752565

Good luck, on both projects.

And you're welcome.

Grant Fritchey SSC Guru Points: 398711 More actions · Answer 7

What he said.

And, you know where to go if you get stuck.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning