Managing Database Changes
-
I'm getting ready to start a new C#/WPF project for a client. The database will be SQL Server. Myself and another developer will both work remotly. So there's two development PC's, each with SQL on them. Then my server will be used to test, so there's a copy of the DB there. Then there's the production DB on the client's server. I expect that I'll be creating, editing, and deleting DB objects continuously. I will probably use scripts for these changes so I can then run the scripts on either the test or production servers. Also, other developers will need to run them. The question is about how to manage the changes/scripts. What process do you follow for this? Is there a naming convention? How do you keep everything in sync? Thanks
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
I'm getting ready to start a new C#/WPF project for a client. The database will be SQL Server. Myself and another developer will both work remotly. So there's two development PC's, each with SQL on them. Then my server will be used to test, so there's a copy of the DB there. Then there's the production DB on the client's server. I expect that I'll be creating, editing, and deleting DB objects continuously. I will probably use scripts for these changes so I can then run the scripts on either the test or production servers. Also, other developers will need to run them. The question is about how to manage the changes/scripts. What process do you follow for this? Is there a naming convention? How do you keep everything in sync? Thanks
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
In a previous position, we used the following DB script pattern: 1) For each major release, have a set of "Create" scripts. a) Create Table_1.0.0, Create_Index_1.0.0, Create_Sysdata_1.0.0 (or something like that) 2) Each time there is an upgrade, create a set of scripts a) Upgrade_Table_1.0.0_1.0.01, Upgrade_index_1.0.0_1.0.1 (and so on) Make sure you are using a source code control system where you would be checking in not only your source code, but your SQL scripts. When you have a "build" you will also pickup the correct version of database scripts. Also, you should have a Build / Deploy box where you can Create or Upgrade databases anytime for QA testing. It is always a good test to be able to take a backup of your client's database and run the upgrade scripts to prove them out. The only version control system I've used is Subversion. If you are not familiar with them, check them out and setup a proper team development environment, you will save yourself lots of headaches. Just my 2 cents. David
-
I'm getting ready to start a new C#/WPF project for a client. The database will be SQL Server. Myself and another developer will both work remotly. So there's two development PC's, each with SQL on them. Then my server will be used to test, so there's a copy of the DB there. Then there's the production DB on the client's server. I expect that I'll be creating, editing, and deleting DB objects continuously. I will probably use scripts for these changes so I can then run the scripts on either the test or production servers. Also, other developers will need to run them. The question is about how to manage the changes/scripts. What process do you follow for this? Is there a naming convention? How do you keep everything in sync? Thanks
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
General concept - Database itself has a version - Wrap the changes into an application that applies those to the database - Use a table in the database that tracks the current version. An existing tool for this is liquibase. Seemed decent when I used it. You can roll your own as well. And additional feature that I consider essential but it adds complexity is that the application also tracks the version of the database it expects. If the database is the wrong version (table above) then the application will exit on start up.
-
I'm getting ready to start a new C#/WPF project for a client. The database will be SQL Server. Myself and another developer will both work remotly. So there's two development PC's, each with SQL on them. Then my server will be used to test, so there's a copy of the DB there. Then there's the production DB on the client's server. I expect that I'll be creating, editing, and deleting DB objects continuously. I will probably use scripts for these changes so I can then run the scripts on either the test or production servers. Also, other developers will need to run them. The question is about how to manage the changes/scripts. What process do you follow for this? Is there a naming convention? How do you keep everything in sync? Thanks
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
We use Red-Gate SQL tools against the UAT server. We also make a strong use of schemas, common objects use dbo, specific object use a different schema, a developer generally works on one schema at a time and dbo changes are discussed before implementing. Scripts are run against the UAT server using SQL Compare. Master table data is synched via SQL Data Compare. PS I do not work for Red-Gate but have been using their paid for tool set well over a decade.
Never underestimate the power of human stupidity RAH
-
I'm getting ready to start a new C#/WPF project for a client. The database will be SQL Server. Myself and another developer will both work remotly. So there's two development PC's, each with SQL on them. Then my server will be used to test, so there's a copy of the DB there. Then there's the production DB on the client's server. I expect that I'll be creating, editing, and deleting DB objects continuously. I will probably use scripts for these changes so I can then run the scripts on either the test or production servers. Also, other developers will need to run them. The question is about how to manage the changes/scripts. What process do you follow for this? Is there a naming convention? How do you keep everything in sync? Thanks
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
This is how I've done it for years....right after login, the application compares db version to app version, applies changes (logic in the app itself) if needed. If the version changed, the db version is updated to reflect that. In addition, I also use a special database table that records schema changes as they are made. If it happens that the database version is higher than the app, the app prompts for an update. If the user declines, the app complains and exits. btw, comparing version numbers is much easier if you convert them to long ints using something like this: intAppVersion = (major * 100000) + (minor * 1000) + revision This also makes it easier to run a loop through db version checks until you hit the current app version.
"Go forth into the source" - Neal Morse
-
This is how I've done it for years....right after login, the application compares db version to app version, applies changes (logic in the app itself) if needed. If the version changed, the db version is updated to reflect that. In addition, I also use a special database table that records schema changes as they are made. If it happens that the database version is higher than the app, the app prompts for an update. If the user declines, the app complains and exits. btw, comparing version numbers is much easier if you convert them to long ints using something like this: intAppVersion = (major * 100000) + (minor * 1000) + revision This also makes it easier to run a loop through db version checks until you hit the current app version.
"Go forth into the source" - Neal Morse
That sounds very similar to Entity Framework Code First Migrations[^]. :) However, I don't think that's a good approach. Apart from your application needing to check the database version every time it connects, it also has to connect as a user which has permission to modify the structure of the database. That always seems like a violation of the principle of least privilege[^] to me. 99% of the time, your application doesn't need to modify the database structure, so it shouldn't have permission to do so. You might be able to work around that by using a second login with the elevated permissions. But in most cases, I think it's easier to move the database upgrade code outside of the application, and manage it as part of the upgrade process.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
That sounds very similar to Entity Framework Code First Migrations[^]. :) However, I don't think that's a good approach. Apart from your application needing to check the database version every time it connects, it also has to connect as a user which has permission to modify the structure of the database. That always seems like a violation of the principle of least privilege[^] to me. 99% of the time, your application doesn't need to modify the database structure, so it shouldn't have permission to do so. You might be able to work around that by using a second login with the elevated permissions. But in most cases, I think it's easier to move the database upgrade code outside of the application, and manage it as part of the upgrade process.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Richard Deeming wrote:
That sounds very similar to Entity Framework Code First Migrations
Maybe they got it from me? :laugh: I've been using this approach since about Y2K. :) You do have some good points regarding principle of least privilege, and I wholeheartedly agree with that philosophy/architecture for some applications...most of the ones I deal with demand simplicity and the ability for an end user to install a module without help from IT, hence dbo. :)
"Go forth into the source" - Neal Morse
