Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Tools for managing DDL for multiple dbs

Author  Topic 

Lavos
Posting Yak Master

200 Posts

Posted - 2012-09-18 : 10:52:00
Hello,

Long time reader that's been absent for a few years.

I've semi-inherited the care and maintenance for some databases used for an application that are in some need for a lot more oversight. (Care and maintenance in the sense of support work and application design. I'm not an actual DBA.)

The application serves multiple clients, and functions nearly identical for all of them with variations as to specifics.

The design has several central databases for core application functionality with a seperate database for each individual client. The reason they are seperate is because of the ease of keeping each client's data seperate, and each client may want functionality to work differently from each other.

I have two major issues which seem insane to me.

1) The DDL for these databases isn't under source control.
2) All the deployment scripts for DDL are created by hand, with the transactional parts of the deployment script usually just slightly off.

Now we have an awesome deployment manager that has been able to keep them organized so there haven't been any major disasters, but the situation is pretty much unacceptable and I'm looking for some solutions

What I have done in the past is just import the database into a VS.Net DB project, commit the files to SVN, and then use VS.Net's schema comparison tool to generate the scripts we deploy through our regions.

I would do the same for this application, but it's not uncommon to need to deploy a procedure change that's in each of our client databases.

I'm thinking it would be obnoxious to run the schema comparison tool for multiple (slightly) different databases to generate multiple scripts.

Does anyone have any suggestions or experiences for any tools for managing the DDL for multiple databases as a whole?

Thanks in advance,


----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-18 : 12:12:49
Well...

Having worked for a company that had a strikingly similar setup (based in Alpharetta, GA, so if that rings any bells, just run, dump 'em, move on, you won't fix it, save your sanity)...
  • Find out from your business stakeholders what's more important: serving the clients, or making the internal process manageable, thereby serving the business. Where I worked, the former was more important, and caused the latter to suffer (and ultimately certain clients who dumped)

  • The previous question is not a joke. Unless you get specific buy-in from management, to the effect of "Yes, we are willing to delay client requests in order to get our internal organization straightened out, and management will deal with their complaints and let you work", you are going to have a VERY DIFFICULT time accomplishing what you describe. Be prepared to be sabotaged at every turn by your client reps and managers, since clients are noisy but pay, and developers/DBAs are noisy and cost money, and are paid to do what they're told.

  • The mechanics of synchronizing the source are actually a minor issue. The politics, restructuring and re-educating of your developers are going to take a lot more time and effort. They have to be untrained from doing shoot-from-the-hip, ad-hoc, right-now-instead-of-right-way development. And again, if they don't sign off, including their managers, you won't get very far. Don't think they'll actually change just because management tells them to. You have to demonstrate that the new process is better (especially if they make out-of-band production changes. You'll need to stop that immediately.)

  • If your team gets feature requests verbally or by email from client reps, you have a project management problem, and unless you get that fixed, the rest is irrelevant.

  • You can put a snapshot of DDL into source control, just for safety, but it should not yet become the gold standard for code. Your team will have to decide if and/or how restructuring the codebase will make it more manageable. Client-specific differences may warrant a full, separate project, or simply a client branch with just their changes (hopefully). Deployments may need to be a 2-step process: base/gold code first, then client-specific code.

  • Part of the previous item is discussing whether each client has to be in a separate database. What's really different about each client, and how similar are those differences? (are you repeating the same feature for multiple clients?) Can the differences be managed via data-driven logic? How much time is spent managing the differences vs. managing the common code?

  • If they still need separate databases, can you move common data to a single database, and use synonyms or views to access it in each client database? The same applies to stored procedures, UDFs, and such. If every object is accessed using synonyms, then changes can be a lot simpler just by changing the synonym's reference (default reference to gold code/database, and modified as needed to reference the client's local copy during deployment). You also save space by not duplicating common data, and time by not having to keep it in sync.

  • These last two items are not minor changes, and warrant significant testing. But done correctly it can vastly simplify deployment and hot fixes.

  • Ultimately you'll want to avoid doing schema changes for deployment. All deployments should be done straight from source control, period. Client-specific changes are part of the deployment and managed by their branch or project. It's sometimes too easy to change a compare setting and accidentally drop things (this happened to me with several foreign key constraints, lots of orphaned accounts, etc. ensued) This can certainly be a transitional process, but should be the goal, because...

  • Once you get all of this figured out, you can look at automatic deployment and continuous integration, including test-driven development. I know everyone says this is hard, and they're right, but the ones that are really doing it say it's the best thing ever, and they're right.
I'd like to say this wasn't based on personal experience, but it was. Fortunately I didn't have to deal with much of the horror as I worked in another division, but I dealt with lots of people who did.

20+ clients, each with separate DB structures and codebases. 90-95% identical in every way, and 90% of the effort went towards the differences, which really weren't all that different. Each team was client-focused and rarely communicated with other teams, so lots of duplicate effort. Every DBA who tried to consolidate ended up quitting, even several hired SPECIFICALLY TO DO THAT. Developer turnover was so high that almost all area recruiters no longer tell their applicants to work there.

Somewhere north of 30% of their client base dumped in the past 2 years, including their largest single client, so >50% of total business lost. And this is a company that 5 years ago was the industry leader in revenue and client base. They just hired a new CEO and several rather expensive consultants to rework all of their development, and I'm still skeptical they'll actually fix it.

This isn't meant to scare you off (well, somewhat) but it can serve as motivation for your team as to potential side-effects of the current process. Bad code management can sink a company, but it will take the entire company to fix it. Serving clients at all costs will ultimately cost exactly that. I'm utterly convinced that if they consolidated earlier and allowed clients to be secondary to internal process improvement they'd be far better off.
Go to Top of Page

Lavos
Posting Yak Master

200 Posts

Posted - 2012-09-21 : 16:37:51
Hello, thanks for the advice. I haven't finished digesting it yet, but I'm slapping myself for not thinking about synonyms before. It's been a long week.

The real stumbling block for any large upgrade is that it's a "finished" application that we get by with just billing our clients with minor upgrades here and there.

I've only recently really convinced some of the people here that adding (NOLOCK) to every select clause is one of the worst ideas for "resolving" deadlocks that I can think of.

We are replacing some lovely error prone and hard to diagnose DTS data loads and moving it all onto a common architecture shared between clients instead of our current process of hand jamming a different process for each, so there is hope maybe.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-21 : 17:01:33
quote:
I've only recently really convinced some of the people here that adding (NOLOCK) to every select clause is one of the worst ideas for "resolving" deadlocks that I can think of.
Oooooh, if you've got a solid argument please send it my way. I've given up where I currently am.

Since it's a "finished" app it sounds like a good refactoring candidate. Just very simple, testable changes, nothing that affects functionality. Switching to synonyms can be made easier by also using schemas, i.e. transfer all dbo objects to an "ABC" schema, then make a synonym with the same name under dbo that reference the ABC object. You can generate the SQL to accomplish this fairly easily.

We had the same issue re: importing external client data too. They did have some generic structure to it, but each data load was also hand-crafted even though 50% of them used the same original data format.
Go to Top of Page

Lavos
Posting Yak Master

200 Posts

Posted - 2012-09-21 : 17:25:45
I don't have a good strong generic argument, but in our particular environment without strong DBA oversight we have things like tons of page splits going on. (My kingdom for sane fill factors.)

The last best example was an investigation into some monthly balances and finding out who sent out an update that caused a miscalculation. I mean, to find the miscalculation so we could fix it...

The person running it had some sanity checks to make sure some queries actually were equivalent and balanced, but instead got back a page of rows that didn't satisfy what we thought was a safe assumption.

Of course, investigating each row individually showed them to be perfectly valid, and rerunning the sanity query showed a different page of rows.

So yeah. NOLOCK (or rather read uncommitted isolation level) led to missing rows due to page splits moving things around.

Not being able to balance things properly was a good example of why it's so bad.

I'd ask about how you felt about snapshot isolation as a replacement for the nolock crutch, but then I'd have to admit to being lazy and not searching the forum first.


Will definitely have to read up on synonyms and write a proposal up.


----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-21 : 18:03:04
Read committed snapshot isolation was my preferred substitute. However...our boss is an actual technical wizard, but unfortunately he's a former machine language programmer. If he senses anything that might use more than 10 clock cycles to complete, we can't change the system without EXTENSIVE testing to show that IT WILL NOT impact performance. Snapshot does have some impact but I can't quantify it to the level of detail he'd want. Maybe someday.
Go to Top of Page
   

- Advertisement -