The ideal is to make a change and see that change deployed to production, in a perfect world we would be told to work on something, write the code + tests, deploy to a test environment, prove it works and deploy - this is the cycle time and the faster you can get this the easier many things become.
The cycle time is easy to measure - it is the time the ticket arrives in the backlog to the time it moves to the done column, if your issue tracking system can't tell you this easily then use something else! The tickets are moved into the "Done" state when they have been deployed into production - if you do nothing but investigate and try to reduce your cycle time you will make a massive difference to your development process.
There have been a few discussions on stack overflow recently about how to manage deployments in uncontrolled environments, specifically data migrations. The questions were from an SSDT perspective, I don't think that SSDT is a great choice for these uncontrolled environments and there are some additional requirements for these uncontrolled environments that need some additional thought and care when creating release scripts (whether manually or using a tool).
What is an uncontrolled environment?
I define it as a database that is managed by a customer, typically a vendor sells a product and it includes a database. The database is on a customer server and the customer is sysadmin and can make changes.There is a difference to databases where customers are allowed to make changes and ones where they are not allowed - but in either case you still need to take extra care, even if it is only to add additional logging and warnings to the output so any upgrade scripts help your support diagnose issues rather than displaying an error like "DBUpdate Error" - yes I have seen that with a vendor product once!
When you own and manage the deployment for your database application you can do these things because you can take for granted:
| You can | Because |
| Drop objects not in source code | If it isn't in your source it does not exist |
| Rely on scripts generated / created by your dev team | If someone wants to create an object called X they can see if an object called X already exists or not |
| Ensure each deployment happens successfully | Run each deployment, run tests and verify the results |
| Write data migration scripts using accurate data | You have the data |
| Use truncate on tables to clear data | The script author knows there are not any foreign keys pointing to the table and that the data can be restored by backup rather than a transactopn |
If you do not control the environment then you cannot do these things because:
| You can not | Because |
| Drop objects not in source code | Who knows what the user has changed |
| Rely on scripts generated / created by your dev team | Users may have made non compatible changes, you want to create a new view called "users_blah"? Well it turns out they have a audit stored procedure called users_blah |
| Ensure each deployment happens successfully | Run each deployment, run tests and verify the results |
| Write data migration scripts using accurate data | You have the data |
| Drop objects not in source code | If it isn't in your source it does not exist |
| Use truncate on tables to clear data | The script author does not know there are not any foreign keys pointing to the table and that the data can be restored by backup |
So what can we do?
I really don't think that there is a 1-sized fits all solution here so you will need to look at your database and what changes you need to make but some randomish thoughts are:
- Compare / Merge type deployments will drop any changes the customer has made - that is bad
- If you had each version of the source, you could verify whether there have been any changes before deploying - that sounds good but potentially a support nightmare
- The migrations approach sounds better but you need to ensure that every change is actually deployed
- Adding additional logging and verification code is a must, instead of "truncate table" then "print truncating, check for things that will block this, truncate" - making sure that a un-reversable command like truncate has forced the user to backup or at least make sure the users understand that they need a back (even in 2016 this isn't guaranteed!)
- Taking simple precautions like not using "select *" and using column lists and "order by column" rather than "order by ordinal" will help you in the long run with odd issues that will be hard to disagnose!
I guess the end answer is to offer a hosted solution and move to continuous deployment in a controlled environment as that actually makes a lot of these things simpler!
