October 20, 2016 at 8:04 am
This is solid gold stuff from you both, thanks for helping out so quickly.
How much cross-database querying is there? If little to none, yeah, keep 'em separate. If lots, it might make more sense to have them in separated schemas. Also, how is development on each done? Are they separate streams, separate teams, with different release cycles? If so, absolutely on separate databases. Worst mistake of my career was to cram five different apps from five different teams into a single database because there was some, but not much, cross-querying (which reduced as they built more services from each team). It caused nightmares for development, test and release.
That was actually part of my rationale for separating out into two databases. To reduce cross querying. At the moment two teams, with three business functions (ten people or so) are querying a single db all day everyday. My idea was to have the BI people doing BI stuff and the Order / Analysis people doing their stuff. (I would opt to split the Order / Analysis out into two db's, but a they both share an application as a data destination - so it seemed to be sensible to keep them together.)
I'm not a DBA, and am fairly new to the concept of overhead and demand on server resources, but it seems to make sense to keep people's work separated, and gives us the opportunity to have the functional db's populated with the relevant tables and fields, rather than everyone querying everything in the same place (which is how thing currently stand).
In terms of development I want them to be able to use a sandbox user db for their code workings (in source control), apply that to the deve staging db and or functional dbs and then pass that up through a QC person / team for approval and deployment into production (similar to / inspired by your process Brandie :))
Thanks again both
Lins
October 20, 2016 at 9:03 am
lindsayscott23 (10/20/2016)
That was actually part of my rationale for separating out into two databases. To reduce cross querying.
Lins,
I think you misunderstand what Grant meant by cross-database querying.
If you separate out the data into individual databases, how many queries will have to touch these multiple databases to get their results?
If the answer is "a lot" or anything higher than... say... 20%, you should probably keep the data in one database. If the answer is very little, then by all means separate them. But cross-database querying isn't the same as cross-chatter. It's all about what data / tables / functions & procs the devs for each dataset project are utilizing.
October 20, 2016 at 9:41 am
Brandie Tarvin (10/20/2016)
Lins,I think you misunderstand what Grant meant by cross-database querying.
If you separate out the data into individual databases, how many queries will have to touch these multiple databases to get their results?
If the answer is "a lot" or anything higher than... say... 20%, you should probably keep the data in one database. If the answer is very little, then by all means separate them. But cross-database querying isn't the same as cross-chatter. It's all about what data / tables / functions & procs the devs for each dataset project are utilizing.
I agree.
Although, if you have a discrete reporting area, or are making a discrete reporting area, that's a good reason to have a separate database.
"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
October 21, 2016 at 2:28 am
Grant Fritchey (10/20/2016)
Brandie Tarvin (10/20/2016)
Lins,I think you misunderstand what Grant meant by cross-database querying.
If you separate out the data into individual databases, how many queries will have to touch these multiple databases to get their results?
If the answer is "a lot" or anything higher than... say... 20%, you should probably keep the data in one database. If the answer is very little, then by all means separate them. But cross-database querying isn't the same as cross-chatter. It's all about what data / tables / functions & procs the devs for each dataset project are utilizing.
I agree.
Although, if you have a discrete reporting area, or are making a discrete reporting area, that's a good reason to have a separate database.
Brandie: Ah yeah you're right, I think I did misunderstand the difference between Cross-Querying and Cross-chatter. (Both new terms to me tbh!). That makes me think that I should try to work out which objects / data both teams would be hitting. My feeling is that they're quite separate really, but there is overlap.
Grant: My plan would be to move reporting into a discrete area yeah. When you say "that's a good reason to have a separate database".. why is that? (I'm kind of asking for my own understanding and because I just know that management will ask me "But why is it better?"!
October 21, 2016 at 5:51 am
lindsayscott23 (10/21/2016)
Brandie: Ah yeah you're right, I think I did misunderstand the difference between Cross-Querying and Cross-chatter. (Both new terms to me tbh!). That makes me think that I should try to work out which objects / data both teams would be hitting. My feeling is that they're quite separate really, but there is overlap.Grant: My plan would be to move reporting into a discrete area yeah. When you say "that's a good reason to have a separate database".. why is that? (I'm kind of asking for my own understanding and because I just know that management will ask me "But why is it better?"!
Because you can tailor the storage separately. You can also easily move it to another server if necessary. You can also mess with database settings independently (let's say you load it nightly and then reset the db to be read only during the day, stuff like that). It just gives you flexibility. However, you'd only want to do this if there is very minimal to no cross-database queries involved.
"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
October 21, 2016 at 6:00 am
lindsayscott23 (10/21/2016)
Grant Fritchey (10/20/2016)
Brandie Tarvin (10/20/2016)
Lins,I think you misunderstand what Grant meant by cross-database querying.
If you separate out the data into individual databases, how many queries will have to touch these multiple databases to get their results?
If the answer is "a lot" or anything higher than... say... 20%, you should probably keep the data in one database. If the answer is very little, then by all means separate them. But cross-database querying isn't the same as cross-chatter. It's all about what data / tables / functions & procs the devs for each dataset project are utilizing.
I agree.
Although, if you have a discrete reporting area, or are making a discrete reporting area, that's a good reason to have a separate database.
Brandie: Ah yeah you're right, I think I did misunderstand the difference between Cross-Querying and Cross-chatter. (Both new terms to me tbh!). That makes me think that I should try to work out which objects / data both teams would be hitting. My feeling is that they're quite separate really, but there is overlap.
Grant: My plan would be to move reporting into a discrete area yeah. When you say "that's a good reason to have a separate database".. why is that? (I'm kind of asking for my own understanding and because I just know that management will ask me "But why is it better?"!
Reporting typically puts a heavy workload on your server, which can affect other processes adversely, if server resources are shared. If you have a separate reporting database, you have a straightforward option available to move the database to another server, to solve this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 21, 2016 at 7:51 am
Hard to improve on the great advice Grant and Brandie have provided but I thought I'd add some thoughts based on my experience getting my firm's databases under source control:
This can be a bit of work to get set-up - all the engineers will need to develop some muscle memory for it to be successful - but it's well worth the effort.
As Grant mentioned all environments should be represented by branches in the repo. My firm ended up customizing GitFlow to match our specific environment set-up. You may want to check that out to see if it would work with you.
Best practice would be for the developers to always be working in feature branches on local dbs. When a feature passes unit tests, it gets merged into (and deployed to) Dev for integration and smoke tests, then maybe Test for QA, etc.
Hope you pardon the shameless plug, but we're using the Red-gate tools to facilitate this; they've been great!
October 21, 2016 at 9:11 am
Steve Thompson-454462 (10/21/2016)
Hope you pardon the shameless plug, but we're using the Red-gate tools to facilitate this; they've been great!
I don't pardon the shameless plug. I actively encourage it! Ha!
Good to know that Redgate tools are working well for you. Thanks.
"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
October 21, 2016 at 12:05 pm
To add my two cents, I just finished a good book that has some useful thoughts on the use of source control as the central tool when working with teams to move code (new or updated of course) from dev to live.
You can download it for free here on SQL Server Central >
http://www.sqlservercentral.com/articles/books/118586/
----------------------------------------------------
October 24, 2016 at 3:52 am
Thanks to you all for the great advice and guidance on this thread from the end of last week.
It's really helpful to have the reasons / rationale to back up the design proposal as well.
I passed a very top-level document to management on Friday and have had positive feedback so far. We'll see what senior management think next...
(and in the meantime I can check out that book on source control)
Cheers all!
Lins
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply