Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1878
Members Online:
2
Guests Online:
68

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
1
New Topics:
0
Active Topics:
1

Our newest member
kowilmar1990z

Other

FAQ
All times are UTC [ DST ]

Google Ads

Hijacking HA for Easier Deployment

Please wait...

Hijacking HA for Easier Deployment

Postby AlexCuse on Wed Feb 13, 2013 2:22 pm

A little background - our application runs in a small web farm. When deploying we typically take one server offline and deploy to it, then test locally to validate the deployment. Once validated we bring this server back online and take the remaining servers offline until they have gotten the latest code as well. This works well enough but of course we have problems deploying breaking database changes.

We currently have the DB running on a single server (SQL 2008) but we're working on getting the customer's other SQL server online so that we can use it for HA. Writes in this system are infrequent, but they do come in through a service that runs as part of our application so it would be nice if we could write to either server and have the change show up on the other as well but I'm sure we could work around this requirement if needed. But I'm wondering if any of the HA solutions available could help with our deployment woes.

Assume we have DB servers X and Y, and application servers 1,2,3 and 4. I'm imagining a scenario where application server 1 points to a database on DB server X, and remaining application servers point to a copy of the database hosted on DB server Y that is kept in sync via replication (not necessarily using SQL server replication but something that gets us the same end result). What I would like to be able to do when deploying is pause "replication" and deploy my code changes to DB server X / app server 1 and then test locally. Once the deployment is confirmed, I'd like to resume "replication", relying on it to move my database changes to the other environment while code changes are deployed to the remaining servers. While this seems like it would be a bit outside the scope of what HA solutions are typically meant to provide, a process like this would be easier to automate than anything else I can think of, and help our deployments go a lot smoother.

Before I go too far off into the weeds on this I thought I'd see what the real DBA's think of this idea, mostly whether or not it seems completely insane. And if it doesn't seem completely insane, is there a particular HA strategy that would work better for what I've got in mind.

TIA,

Alex
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: Hijacking HA for Easier Deployment

Postby SQLDenis on Wed Feb 13, 2013 2:31 pm

All the shops I worked at,the developers never had DDL access, all had to be done via submitted scripts, tested on staging and then moved into production by DBA team

I use replication for data (2 way replication) for hot hot servers and mirroring for rest
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: Hijacking HA for Easier Deployment

Postby SQLDenis on Wed Feb 13, 2013 2:55 pm

The replication (I assume you use transactional replication) scenario should in theory work. Make sure to test it out first, be aware of gotchas where it tries to drop the object first and then does a bcp of all the data after it recreates the table

Are you using one way replication or do you replicate both ways?

Ted can chime in since he has more knowledge about replication than me
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: Hijacking HA for Easier Deployment

Postby AlexCuse on Wed Feb 13, 2013 3:07 pm

We're just exploring our options at this point. I figured instead of bothering Ted on IM I would post here so he can get some shiny stars. You know, since thats the only reason anyone would ever answer a question ;)
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated

Re: Hijacking HA for Easier Deployment

Postby SQLDenis on Wed Feb 13, 2013 3:09 pm

Yes we all know Ted is a SWtm :-)
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Hijacking HA for Easier Deployment

Postby onpnt on Wed Feb 13, 2013 3:33 pm

Sorry guys, been real busy lately.

Problem you will have is, what happens if the deployment needs to role back? Replication will have to be bi-directional (merge) or anything that happens on the subscriber while you are testing, won't go back to the DB you just upgraded with changes. However, that isn't real bad but does add a layer of complexity and level of skills needed to make sure merge is running ok. Since you don't have much changing on the DB, that will be a lot less than the other side. The more that changes, the larger and more maintenance is needed on the merge tables, essentially.

If the databases aren't all that big, you can also work with how things are setup for merge to make it pretty fast and recovery from a small DB is typically easier with a reinit.

Mirroring doesn't do much for you but SQL 2012 availability groups sounds way out of reach. So that means you'll want to set the replication up in a multi-publication setup. Since 90% of all updates to a DB are code, you'll want one publication that is composed of just routines and then the other publication(s) made up of metadata and then user data (data that changes often).

Something you will need to consider: GUIDs will be added to the tables you enlist in merge replication. schema changes do replication but if you mess something up bad, there is no going back and you'll need to reinit the subscribers to get them back online. Distance is a concern. If they are in the same DC, no worries but if you are replicating 30GB to Europe (example) then a reinit takes on an entirely new meaning.

All-in-all, I'm with Denis on replication being your native, cheap and effective way to set this up. Just remember, it's not a Next, Next, Finish wizard setup. Need to think about the design of the merge publications, even more with multiple going to one DB so you retain integrity on the both sides (example: table a has the PK, table B has the FK...need to replicate table A first :) )
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI

Re: Hijacking HA for Easier Deployment

Postby AlexCuse on Wed Feb 13, 2013 7:22 pm

Thanks - glad to know its not a completely crazy idea. I'm sure I will be back with more questions if we do decide to go that route. IMO there are some significant schema changes that would be needed before we can setup replication sanely, but we'll cross that bridge when we come to it.
Say what you like about the tenets of National Socialism Dude, at least it's an ethos
User avatar
AlexCuse
LTD Admin
LTD Admin
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031LTD Gold - Rating: 1031
LTD Gold - Rating: 1031
 
Posts: 5523
Joined: Tue Oct 09, 2007 5:26 pm
Location: Pennsylvania, US
Unrated