January 8, 2015 at 4:01 pm
Hi, I built software as website using VS2013 asp.net with sqlserver 2008 R2 and hosted on production server. Also, my customers are using the software for last one year. I usually deploy the new feature build in every month after 7 pm on last Friday of every month. I am planning to do the deployment during office hours(b/w 8 to 6) which should not impact the customers. I wanted to do the deployment and parallely my customers can use the website . I can create separate VD on IIS to have my latest code deployed and tested then i will point it to it when testing is done. Hope no issue will come on Application server side. But how do i maintain my database
because i should not execute the schema changes and other DDl and DML changed on the Production database as it is live and used by the customers.
What is the best way to achieve this scenario? How do i maintain the transaction?
Do i need to have another database instance and once the deployment over should i need to sink? I don't have any idea as of now how to proceed with this. Any suggestions please how to implement this.
how do i achieve this concept. please suggest me
[conceptually i am looking for how to achieve Zero down time deployment]
January 8, 2015 at 7:44 pm
Unless you have two systems where you can turn one off, deploy to it, and then do a switch to deploy to the other system, there's no guarantee that you won't have any downtime. You also need to develop a "backout" plan that can be implemented in a near instantaneous fashion if smoke testing after the deployment shows a fault even though you may have tested everything to death. And, yes, I would do the smoke testing on the first system you update before updating any of the others so that you can take the modified system back down and bring one of the others backup to have more time to do a backout.
I do single system updates a lot but I don't do them during normal business hours unless the changes are very small and have been tested for contentious behavior. It's just too risky on a single system. That's usually when the system has the highest load on it and any wholesale updates to the database, especially any DDL that modifies large or heavy usage tables, will surely meet up with some serious contention.
Bringing up a new product by pointing to a new system isn't so bad but you still need to have a backout plan for when something goes wrong... especially if the new system writes to the older ones for just about any reason.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2015 at 6:37 am
Hi Jeff,
thank you so much for your precious time on this post. You two systems we need for this. Does it mean two sql instances and one two application servers? Is it possible to sync the databases data after the deployment over.
or If this is not good idea, can this be implemented using Cloud computing?
[currently we have 5000 live users]
January 9, 2015 at 8:21 am
KGJ-Dev (1/9/2015)
Hi Jeff,thank you so much for your precious time on this post. You two systems we need for this. Does it mean two sql instances and one two application servers? Is it possible to sync the databases data after the deployment over.
or If this is not good idea, can this be implemented using Cloud computing?
[currently we have 5000 live users]
Cloud computing isn't a panacea. 😉 Just ask the folks that were using a part of the cloud that Verizon was providing. :crying:
When people need to keep something available all the time, they'll have two application servers. When it comes time for an update, they'll take one down during a "slow" time, upgrade it, smoke test it, and if everything goes well, then they'll do a switch and do the same to the other application server.
But, in many cases, people will only have one database server. That means, you need to either make sure that your promotion of code/data isn't highly contentious or you need a brief outage. Even if you have two "federated" servers and could do the same as the application servers, you might have the problem of resyncing the data between the two servers.
I don't know what changes you have in mind and, to be honest, I'm probably not the best person to ask because I have the luxury of not having a 24/7 set of applications on my boxes and I can have up to 3 hours to get a change in, smoke test it, and set it back online.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2015 at 8:45 am
I've done this in a few ways with one database server. However, it's always a custom solution, depending on the changes being made.
For everything, we need a
For procs/functions/views, we usually have the new code just run, and the old code ready to run in case things break. Since I might not rollback everything, depending on issues, I need to be careful with what I run.
In terms of schema changes, this is hard. I typically try to deploy schema changes in advance of proc/function/.NET changes, but your code has to handle that. If that's not the case, then you have a few options.
I have "copied" tables to a temp table as a pre-deploy step (minutes before deployment), which typically isn't blocking. This allows me to quickly switch back to the old table if I have issues, by renaming the changed/new table and then the old one.
For example, if I need to add a column to Customers, I'll copy this to Customers_New, then I'll add my new column (status) to Customers_New.
I can then rename Customers to Customers_Old and Customers_New to Customers in a script. This is fairly quick.
If I have issues, I rename Customers to Customers_New and then Customers_old to Customers (perhaps rolling back code).
January 9, 2015 at 9:44 am
Thank you Jeff and Steven for your thoughts on this. Really am little confused to proceed this process or not. Is there any documentation how to handle this situation on the internet? I tried Google and most of the sites are talking about Application server side events and not Database.
Can you please suggest me the article or any link which discuss about this elaborately will be great help to me to proceed on this.
January 9, 2015 at 1:54 pm
There isn't a lot on the Internet. It's one of the things we are working on at Red Gate, trying to get more information out there on how to do this and build tools to help, but really it's been a one-off for many people in terms of how they deploy the database.
There are some ideas in this book: http://www.amazon.com/Refactoring-Databases-Evolutionary-paperback-Addison-Wesley/dp/0321774515. It's more general in terms of patterns rather than specifics, but it does have ideas you can use.
January 11, 2015 at 4:02 pm
Thank you Steve for your reply and will browse through.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply