• 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.

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.

---