These days I'm doing 'Development' Database Administration. And I'm posting about updates to QA Databases.
We're a development department. We have several Development Databases and several QA Databases. It's all for the same Application but a few different Databases for a few different Customers. We currently have QA Databases 'locked down' i.e. Developers can not modify Stored Procedures in QA. They must modify them in the Development Database and then they notify me to promote them.
We used to be in more of a "build" cycle, i.e. several developers would modify several stored procedures in dev and about once a week, a new build of the Application would be created and I would move all the modified Stored Procedures to QA so that the new build could be tested. And that was working well. Now we're in more of a 'patch' cycle, i.e. I get about 5 requests per day, each to move just 1 Stored Procedure to QA, so just that fix or change can be tested. So this has become very tedious and distracting, having to drop what I'm doing to carry out too many small requests.
Was hoping for feedback or suggestions. Any ideas for me? Are your QA Databases locked down? Should I consider lifting the permissions on the QA Databases so that Developers can promote the Stored Procedures themselves?
I am the push point here.
They email me the change control number and I push from dev to prod. No problem as I have SSMS up anyway and am logged onto both systems. (Registered servers is handy to connect)
There are only two others that can push the code, my backup and the CIO who knows his limitations (small shop). When I go on vacation I give a senior dev permissions to push (no sysadmin) and then remove it when I return.
We generate a Patch Script using a batch file. It concatenates together all the .SQL file that have changed "since last time". We don't do it anything like as often as "daily", but I can;t see why it wouldn't work, if we needed to do it very often.
Each SProc / View / Trigger / Function etc. is maintained in a separate .SQL file (and they are all stored in Version Control system [SVN in our case])/ I suppose we could extract them direct form SVN if we choose to, for us it is just that we have "always" been using the Batch file, so we haven;t considered changing it!
We have specific .SQL files for DDL changes (which also includes any SQL statements to "modify" the data. So, for example, we might add a column to a table and perform some sort of UPDATE statement to populate the new column. Those operations would be in a .SQL "Patch File" and the BATCH file would include them in the Patch Script.
One snag: if A needs to be created before B. We fix this, manually, my moving objects around the Patch Script so they are created in the right order. That might be a pain if you have to do it every day ... although perhaps 99% of the time?? the order would not be relevant. Particularly if all SProcs already exist, and you are just ALTERing them
We also have some additional steps we perform when we move from DEV to QA. We minify the SQL code (remove comments and white space etc. This is to reduce the size of the script (and perhaps save some SQL Buffer space, but also make it somewhat harder for competitors to reverse engineer our stuff if they stole it ...)
We also have some comments in the code to remove some DEBUG statements which are only required on DEV. We also set WITH ENCRYPTION (which is disabled on DEV). So, for us, those things also happen at the Patch Script creation stage (they are just some Edit Macros which make the global Find & Replaces)