47 Things You Incorrectly Assume About Shipping Database Changes #tsql2sday

For this month’s T-SQL Tuesday, James Anderson’s chosen theme is shipping database changes.

Hoo, boy, have I got some scars to talk about.

I’ve been a developer who had to deploy changes, a DBA who had to deploy other peoples’ changes, an open source maintainer, and even been a software vendor who had to hand other people scripts to deploy in their own environment.

Here’s some of the things I assumed over the years – only to learn the hard way that they weren’t always true:

1. The deployment script you were given is the most recent one.
2. The deployment script will work.
3. The last deployment to the same database finished successfully.
4. All of the indexes you deployed last time, still exist.
5. If you add a unique constraint or index, the existing data doesn’t already violate that constraint.
6. All of the tables you deployed last time, still exist.
7. Your tables haven’t been moved into another database, with a view or synonym left behind in its place.
8. No one has added columns to your tables.
9. If you want to add new tables, views, procs, or functions, someone else won’t have already added an object with that name, but a different purpose.
10. No one has added triggers on your tables.
11. No one has changed the contents of your stored procedures, functions, or views.
12. If your objects have been changed, the business wants you to put them back to your own logic, because they didn’t need whatever business logic they’d injected into your code.
13. No one has set up a job to run every 15 minutes, check to see if any changes have been made to their (your) stored procedures, and if so, overwrite them with their own custom business logic.
14. No one has encrypted your stored procedures or functions.
15. No one has added indexed views, stored procs, or functions with schemabinding atop your tables.
16. SQL Server Management Studio won’t crash.
17. The account you use for deployment will have permissions to change tables.
18. The transaction log file will be large enough to let your transactions finish.
19. The data file’s drive will be large enough to let your transactions finish.
20. No one else will kick off database changes or jobs at the same time as your deployment.
21. No one else from your company is trying to do the same deployment you’re trying to do, to the same server, at the same time.
22. No one will kick off a Profiler trace to log every one of your statements.
23. If they do start a Profiler trace, they won’t do it from a massively underpowered desktop, thereby slowing your deployment to a crawl.
24. At the start of your deployment, no one will have an open transaction that blocks you.
25. If there’s an open blocking transaction, your work will be more important than theirs, and you won’t have to wait until theirs finishes.
26. If there’s an open blocking transaction, and no one knows whose it is, you will be able to kill it.
27. If you kill an open blocking transaction, the rollback will finish before your maintenance window ends.
28. No one else will kill your session mid-deployment.
29. If your deployment script includes multiple transactions, all of them will succeed.
30. The deployment person wouldn’t dream of only highlighting some of it and running it.
31. The staff who were supposed to work with you during the deployment will be available.
32. The staff, if available at the start of the call, will be available during the entire call.
33. The staff won’t come down with food poisoning halfway through the deployment call, forget to mute their home office phone, step into the bathroom, and leave the bathroom door open.
34. The available staff will have the necessary permissions to do the deployment.
35. The staff assigned to user acceptance testing will be able to complete their work in the time allotted.
36. The staff assigned to user acceptance testing won’t say they see a problem that they never saw before during the tests that led up to this deployment.
37. Midway through the rollback/undo, the user acceptance staff won’t say, “Wait, my bad, it’s fine, go ahead and keep the changes.”
38. No one has added replication, synchronous database mirroring, or synchronous AGs into your database.
39. If there’s replication or sync mirroring/AGs, they will not affect the performance of your deployment scripts.
40. No one will restart the SQL Server instance during your deployment.
41. Your connection to the SQL Server will be steady during the deployment because no one will have scheduled firewall work to be done during the same window.
42. No one will restart all of the Active Directory domain controllers at once during your deployment, thereby taking your server offline.
43. You will not discover corruption in the database during the deployment.
44. You will not be blamed for that corruption.
45. If you need to do a rollback, the rollback/undo script works with the edge case you ran into during the deployment.
46. In the event of a deployment problem, there will be valid backups for rollback.
47. Someone who has the permissions and power to do the restore will be available.

Database deployments remind me of the old saying:
In theory, in theory and in practice are the same.
In practice, they are different.