I originally posted this on stack overflow but was told that here is a better place to ask this kinda stuff. I couldn’t find any other similar answered question but if there is one please give me a link.
I am developing a software which uses a database that is hosted on a main server(MSSQL). Multiple clients can be connected to the same server at any given time. I am using Entity Framework with the Code-First approach.
My concern is how the database is going to be deployed and later updated. Since we are talking about a centralized database, automatic database migration being performed by any of the clients is out of the question in my opinion. I have been thinking of using a separate server configuration app that is going to connect to the server(database) and perform database creation(first time only) and migration when needed.
Also the reason why I think automatic database migration cannot work in my case is because whenever a client is run, it will check if it there is a migration available for the database and if there is, it is going to apply it. That, though, might result in problems for other clients which haven’t been updated yet. That is why I think there should be a configuration app that updates the database independently of any of the clients.
Do you think this is a good way to solve the problem or is there a better one?
2
Hey I know it has been a while since I posted this, but I noticed there are people still interested in the answer so I thought I should update with what I chose to go with.
I decided to use a self-hosted WCF service that clients connect to and get their data from. The service is responsible for database creation and migration and also notifies clients if they need to update in order to be able to connect.
I think this is the best solution for my case.