Episode 59: Mirroring, Availability Groups, Replication, and Log shipping

Episode 59: Mirroring, Availability Groups, Replication, and Log shipping

Episode 59: Mirroring, Availability Groups, Replication, and Log shipping 560 420 Carlos L Chacon


Welcome to episode 59, Compañeros! Have you ever wondered what the best option is for making your data available in your environment? Listener Chris Hendon asked us to talk about differences between Availability groups, mirroring, log shipping, and replication. Today on the SQL Trail Steve and I chat with Andy Mallon and Mariano Kovo about these topics. We cover each topic in detail and explain the positives and negatives of each one.  Join us, for another great episode.

Episode 59 Quote:
“I would say that some of the best advice I ever got is that the more you screw up, the more you’re going to learn. But, when you screw up, admit it, fix it, ask for help if you need it.” – Andy Mallon on his best piece of career advice

Listen to Learn…

  • How to use Mirroring to save money for your shop
  • The difference between Mirroring and Availability Groups
  • Why would I consider using Mirroring
  • The unique benefit of Replication
  • Why Log Shipping and Disaster Recovery work well together

Show Notes

About Mariano Kovo
Mariano Kovo is an Argentinian SQL Server professional with Precision IT in Argentina. Read his blog (in Spanish) on SQLPass.org. Follow him on Twitter @Mariano_Kovo.

About Andy Mallon
Andy Mallon is a SQL Server DBA. He’s also the co-organizer of SQL Saturday Boston and co-leader for the New England SQL Server User Group. Follow him on Twitter @AMtwo and LinkedIn. His blog The Impatient DBA covers DBA careers, SQL Server, and T-SQL topics.

MSDN: SQL Server Replication
MSDN: Always-On Availability Groups
MSDN: Log Shipping
MSDN: Database Mirroring
Log Shipping FAQ
The Impatient DBA
Mariano Kovo: @Mariano_Kovo
Andy Mallon: @AMtwo
Steve Stedman: @SQLEMT
Carlos L Chacon: @CarlosLChacon

Carlos Chacon: Gentlemen, thanks for coming on the show today with us. Why don’t we take just a minute and introduce ourselves? Andy, why don’t you go ahead and go first?

Andy Mallon: Sure. I’m Andy Mallon. I’m a database architect at a large management consulting firm. I’m been using SQL Server now for about 13 or 14 years for tech support, and eventually becoming a DBA.

Carlos Chacon: Mariano?

Mariano Kovo:
[00:01:00] My name is Mariano Kovo. I work as a database consultant of SQL Server since [inaudible 00:00:48] five, I think, or oldest. I’ve been working in Microsoft three years, here in Argentina subsidiary, and basically work with replication since the first [date 00:01:04].

Carlos Chacon: Very nice. The way we’re approaching this, is we’re each going to talk about one of the different options of Mirroring, Availability Groups, replication, and log shipping. Those are where our focus will be. Then, each one of us will introduce that topic. Then, we’ll get into the pros, cons, differences between each of them. Let’s go ahead and start with Mirroring. Andy, you want to take that one?

Andy Mallon:[00:02:00] Sure. Mirroring has been around for a while. It’s deprecated, starting in SQL Server 2016, but it’s still fully functional, up to and including 2016. It’s not going away any time soon. What it is, is it’s a high-availability option between two servers. It’s database-scoped, so you get everything inside the database, and nothing more, kept in sync between two servers. It’s synchronous, or asynchronous. Asynchronous is an Enterprise-only feature. Even without Enterprise edition, you can use it in synchronous mode. You can have it as automatic failover, or manual failover. It’s a really flexible option for your DR and HA needs. The best part is, from a dollars-and-cents perspective, is if you have software assurance, you don’t have to pay for that second server, if all it’s doing is being a secondary mirroring server.

Carlos Chacon: There you go. A way to save a few pennies there.

Andy Mallon: Absolutely. Everyone’s looking to save few pennies.

Carlos Chacon: [00:03:00] That’s right. I’ll take Availability Groups. The idea of Availability Groups is like mirroring. You have multiple replicas. These replicas have their own data stores. They have their own copy of the database, which means if you have 100 gigabyte database, now you have 200 gigabytes of space. You have to allocate one for each copy of the database. We’re going to add a Window clustering layer on top. You have the database, you have the Windows clustering component. There’s some integration with that, that you’d need. That cluster component has to be there, before you can create the Availability Groups. Then, like the name implies, you have this group so you can actually put databases together, in a group, so they’ll failover, or all be on the same server at the same time. A good example is SharePoint. SharePoint has multiple databases. If, for whatever reason, you’re using multiple drives, and one of those drives were to come available, you’d want all of those databases to move over. You can create a group, or multiple groups, on that server to move them back and forth. Then, you also get an enhancement. You have some enhanced options for those secondary replicas. Now, you can take backups from those secondaries. You can also do some reporting, or querying on them, so it almost makes this second copy. You can offload some of that CPU, or memory, if you have intensive queries for that application. This was introduced in 2012, as an Enterprise-only feature. In 2012, you could have four replicas, so four total nodes. In 2014, they increased that. You can go to eight replicas, although all of a sudden, that sounds like a lot of extra data. They also introduced the wizard that will enable you to replica to the cloud, to Azure, the IAS, the Infrastructure As a Service offering. You have a VM in Azure. You could then run a replica, in asynchronous mode, obviously, because you wouldn’t want that delay, over the internet. You can have that available.

[00:06:00] Then, in 2016, they introduced, you can have nine replicas now. I’m not sure why they only added just the one. They also introduced a feature for standard edition. As Andy mentioned, mirroring has been deprecated. Availability Groups is going to be taking their place. You can have now, in the Standard edition, a replica, but it has some of the same limitations as you do with mirroring. You can’t take backups from that replica. You can’t do reporting, you can’t run DBCC commands, things like that. That’s Availability Groups. Replication, let’s talk about replication, Mariano.


Mariano Kovo: Okay. I think it’s one of the oldest technologies that we have in SQL Server, for copying and distributing data, and various objects. Basically, there are three types of replications. There is the very initial, and the first one you have to do. There’s natural type, when you copy all the object and the data. When you move on, you have to do transactional replication that each transaction made, and the publisher. It’s replicated to the subscribers. It’s like when you buy a magazine, the architecture. You have the publisher that makes the magazine, and all the subscriber that holds the data to itself. I think the replication is not a mechanism for HA or DR. I think it’s a great mechanism to distribute [DHM 00:07:20], but not being considered as a high-availability mechanism.

Mariano Kovo:[00:08:00] A highlight point of replication, that is you can replicate data to SQL servers and database servers that are not from Microsoft, like Oracle. [inaudible 00:07:52]. I think that the negative side of replication is that you have to maintain and keep an eye on it every time it is working. It usually happens that the applications do modifications of data, and there, perhaps, is not, how can I say it? It’s not supported by replication. You’ll have to maintain those changes.

Carlos Chacon: Ultimately, I think replication has adopted that, they call it “the newspaper mentality”. You have a publisher, a distributor, and a subscriber.

Mariano Kovo:[00:09:00] And a subscriber. You can have multiple subscribers, and you can handle with different [inaudible 00:08:46] to push the data to those subscribers, so you can manage the delays between those subscribers. The most complex scenario is the merge replication that allows you, that subscriber can make changes to data, and that changes go back to the publisher. Every server in the architecture becomes a publisher, basically. You have to maintain all possible changes with the data.

Carlos Chacon: That’s probably one of the unique facts of replication, from some of the other items that we’ve talked about, is that you have two sources where you can make changes. Two, or more, where you can make changes. That doesn’t apply to any of the other areas.

Mariano Kovo:[00:10:00] Exactly. The other great thing that you have, was that all the servers participating in this architecture, publishers and subscribers can be different versions of SQL Server on different editions. You don’t have to maintain all the servers like [inaudible 00:10:02].

Mariano Kovo: You can have a SQL Server 2016 and a publisher, and a 2008 as a subscriber.

Log Shipping

Steve Stedman:[00:11:00] Log shipping is one of probably the least glamorous ways of moving data, in a redundant scenario. It’s also one of those that’s fairly robust, and it’s been around for a very long time. It was first introduced, I think, in SQL Server 7, unofficially, through some undocumented calls. Then really, in SQL 2000 and beyond, it was a regular feature. The way log shipping works, is you go through a process where you start with a full backup. Then, you have transaction logs that are being run at a specific time interval. It just continues to copy those transaction logs, and then replay them on the destination server, or multiple destination servers. With this, you get an entire database. There’s no option to pick just a subset of tables, like you could with replication. There’s a couple different ways to do it. Really, an automated way, or a manual way. The choice there really depends on your specific environment.

[00:12:00] The automated way, you just go through the SQL server dialogues, and setup through SQL Server Management Studio, you can turn on log shipping, and it does the full backup for you. Then, it creates the jobs to do the copy of all the transaction log files. The scheduling is all done through that configuration. There’s also an option where, if you just have full backups, and regular transaction logs that are running, you can do a manual process, where you could just start by restoring a full backup on another server, and then have a job that copies and restores the transaction logs on a regular schedule. Effectively, that’s the manual version of log shipping. With this [crosstalk 00:12:02], most people start off with the automated one. There’s some special cases, which I’ll get to in a second, where the manual process may make sense. With this, there’s little-to-no overhead on the source database, with log shipping, once the shipping has been established.

[00:13:00] If you’re already taking full backups and transaction log backups, there’s no more work going on here, other than a tiny bit to keep track of what needs to be copied, and a little bit of bandwidth. Those would be copied off the server already, if they were backups. It’s very light on the primary server. Then, because of that process of just copying those backup files, and getting them restored on other servers, you can effectively do that to any number of SQL servers. Oftentimes, it’s just done from one source to one destination. It is possible you could do that from one source to 25 destinations, if you wanted to. Part of the process with log shipping, since it uses your backups, is that you can use your existing backup location, if it’s somewhere on the network that’s shared between both the source and the destination servers, as the place to hand off, or move, the data between the servers.

Once you get the log shipping database onto the destination server, there’s two options for how that database will look to the people looking at that secondary server. One, is that it’s going to be in a standby option, where you have to actually do a restore with recovery to get that database to come online on the second server. The other, is you can have it copy and go with a read-only option. One of the negatives on that read-only option, though, is that while the next restore is going, that database goes away, and it’s bot read-only. You can’t see it at all.

Carlos Chacon: Yeah. You’d get blocked, basically, when you try to connect to it, because it says, “Hey, I’m in the middle of a restore.”

Steve Stedman:
[00:14:00] Yep. Now, with log shipping, it’s not a great high-availability option, or even medium high-availability, but it is a great DR scenario, or DR option, so that if something catastrophic happens to a primary data center, and you need to get the SQL server running on an secondary server, it’s a great way to bring it back online, which a little bit a downtime.

Carlos Chacon: Sure.

Steve Stedman: One of the scenarios that, I know it’s been around for a long time, and it’s not that glamorous, but one of the more interesting scenarios, that I was part of recently, was a project where we used log shipping to move from SQL 2008 servers, up to Azure VMs that were running SQL 2014. That’s one of the nice things with log shipping. You can go from the version of SQL Server, to the same version of SQL Server, or to any newer version of SQL Server, with the log shipping. You could go form 2008, up to 2016, if you wanted to.

Carlos Chacon: Right. It will take care of any updates. It’s basically just adding data, at that point, with the transaction.

Steve Stedman:
Yeah. Then, part of that move to Azure, where we used log shipping to get the databases there, was it took us more than a week to get all the data copied over on those initial backups. There were lots of databases that we were doing. We were able to use log shipping to keep it caught up, over time, while all those other databases were being copied. It was really, really, incredibly useful to get into an Azure VM, and do a limited-down-time switch over.

Carlos Chacon:

[00:16:00] Right. As we’re talking about these different options, I see this trend. Mirroring and Availability Groups is at the transaction level. That’s the smallest delta, if you will, that it has. Log shipping can, if there are transactions there as well, but then you get to specify a certain time period. I want 15 minutes’ worth of transactions, an hour, a day. There’s some wiggle room, if you will, as to how many transactions are going to be [separate 00:16:04] your source and destination. Then, replication, that same idea-

Mariano Kovo: You have to pray to the gods that the data is replicated.

Steve Stedman: You know? I’ve had that experience.

Carlos Chacon: Yeah. In fact, do that first.

Mariano Kovo: I know that, perhaps in the United States, it’s not a problem. Here, in Argentina, the links between the subscribers and the publishers, perhaps, the internet connection is not quite good. Sometimes, we’ll have days without connecting. The data becomes not updated, and you have to restart all the process.

Carlos Chacon:
[00:17:00] Yeah. It can be not very forgiving there, either. We talk about things go “oops”, and you have to get back to where you were. I have found the replication to be difficult, sometimes, to make that happen. Is that a fair assessment? Don’t everybody jump in at once.

Steve Stedman:

[00:18:00] I would say that replication is probably the hardest to dig yourself out of a hole, when something goes wrong, out of all of the options. Additionally, replication is probably the most expensive, time-wise, on an ongoing basis, to keep it stable. Whereas, with the log shipping option, and I’m not an evangelist for log shipping here, I’m just filling in the details, log shipping is pretty robust, when it comes to a flaky internet connection. If it’s copying a log file, and the connection goes down for a few minutes, to a few hours, or even a day, when it comes back up, it’ll just get caught up. May take a while to get everything caught up, but it will eventually get caught up, and bring all that data up-to-date, as long as you have all those transaction log files still around at the time the connection comes back.

Carlos Chacon: Yeah. That would be the big caveat there, is making sure you have all the transaction logs still available. If your clean up is sometimes, that window is too tight, then if you deleted them, they basically, you have to start again.

Steve Stedman: Yep, absolutely.

Carlos Chacon:

[00:19:00] One of the things, I guess let’s continue with this replication conversation. Mariano made the comment that he didn’t think replication was not a high-availability, disaster-recovery type scenario. I’m going to mix it up here a little bit. My thinking, and maybe this has changed with Availability Groups, but at least in the older versions, my thinking was that replication was really the only way to provide those four nines, five nines if you will, if you wanted your data to truly be available, because you had so many different options with it. Thoughts?

Andy Mallon:

[00:20:00] I would say that it’s not necessarily the only one that you can achieve five nines with. When I worked an e-commerce company, we use mirroring for our high-availability. Ultimately, we had … Our teams’ goal was to hit five nines, but it wasn’t necessarily the goal of the company. We came pretty close. Five nines gives you about five and a quarter minutes of downtime per year. We did a bi-yearly patching. Or, I should say semi-yearly patching. Twice a year, we took production down to patch the production databases. We kept the site up and running within that time frame, using database mirroring.

Carlos Chacon: Now, I’m interested. In that mirroring option, how long were your normally seeing the failover process take?

Andy Mallon:

[00:21:00] That’s the catch process. With mirroring, it will basically constantly be rolling forward and rolling back the transactions on the secondary, to try to keep the secondary up and going for failover. What we found was that each individual database would failover in a few seconds. It depends on how busy it is at the time, how many transactions there to roll forward, to roll back. We found that how recently the database has been checkpointed affected how quickly it would failover. We added a stat for plant failovers, where we would checkpoint the database. Then, do the failover. Even at, say, five or 10 seconds per database to failover, if you’ve got a dozen databases, you very quickly turn that 10 seconds into a couple of minutes.

Carlos Chacon:

[00:22:00] Sure. That seems pretty fast. Of course, I am going from memory here. For mirroring, it seemed like I was in the 15 to 25-second range. Some of those points that you made, as far as checkpoint, I think it would be worth discussing again what that means. Basically, you’re telling SQL Server that if I haven’t written this out to disk, now is the time to go ahead and do so.

Andy Mallon: Exactly. We never quite figured out why that would affect the failover, since in synchronous replication, regardless of whether it’s written to disk, whether it’s been checkpointed, if it’s committed, then that transaction exists on both servers. It’s synchronous. We couldn’t quite figure out how being in synchronous mode depended on it being checkpointed, but we did find that our overall failover time was just a little bit quicker. That could be one of those side effect-type of things, where checkpointing also make X, Y, Z happen, and X, Y, Z was the reason why it was faster. That was our shortcut for figuring it out, just as anecdotal evidence.

Steve Stedman: Back on the five nines comment there, Carlos, one of those with log shipping, is that it’s very unlikely that you would be able to keep a five nines environment with log shipping.

Carlos Chacon: That’s necessarily the purpose for log shipping. If you’re looking for that, that’s not really a candidate.

Steve Stedman: Right. The reason for that is that normally, when a failure occurs with log shipping, it’s a manual process to do that activation on the secondary server. Or, you could have it scripted, but it usually requires human intervention to step in and trigger the fact that we’re going to turn on that second server. Most of the time, when a server goes down, somebody has to call you. Or, you get a notification. You have to connect, and make all that happen. Even in the best scenarios, you’re going to be three to four minutes before you can even start the switch over. Sometimes-

Steve Stedman: Sometimes, it’s more like 15 to 20 minutes. Then, that doesn’t even cover the case for being able to have maintenance windows, and things like that, where you have to switch to the other server. Log shipping is certainly not something you want to consider in a five nines environment.

Carlos Chacon: I know we really haven’t brought the application into play either, because the log shipping doesn’t provide, necessarily, any logic on top for an application to say, “Oh, I’m A. Oh, now I’m on B. I’m over here.” Then, there’s that logic that you’d have to bake in, as well, if you were going to have an application failover, or switch over, switch databases in that event.

Steve Stedman: The way I’ve made that work is through DNS entries, where you have a single DNS entry that you can change to point to server A, or server B when you do the switch over. Again, that takes time, when you do the DNS change, for that switch over to occur, as well.

Carlos Chacon: Right. You have to wait for the timeout, or whatever, for it to flush and pick up the new address.

Steve Stedman:
Yep. Definitely not a five nines options with log shipping.

Mariano Kovo: I’m sorry. [inaudible 00:25:10] was talking about [siv 00:25:12]. In replication, the unit is a table. You have to think that the developers, or the application, creates new objects. You have to add them to the replication scheme. In the case of a failover, or the server go down, you won’t have those new objects, or tables, synchronized.

Mariano Kovo: You have to not forget the security logins, users, permissions. All that, you don’t have it on replication.

Andy Mallon:

[00:26:00] Yeah. I think that brings up your point about just the maintenance of replication. That’s one of the differences from replication, from the others, is that the others are at the database level. Once you make a change in the database, any change, it’s going to get propagated out. With replication, you’re looking at the object. You get to pick and choose what goes and what doesn’t go.

Mariano Kovo: Exactly.

Why Consider Mirroring?

Carlos Chacon: We’ve brought up mirroring. Actually, Andy, one of the reasons I invited you on was a tweet you had put a long time ago. There was some discussion on Twitter about high-availability options. You [inaudible 00:26:34] for mirroring. What would we consider, again, I’m assuming obviously, it’s going to be deprecated in 2016. Beyond 2016, I’ll be deprecated. Under what circumstances, or why would why still be considering using mirroring, if I’m using an older version of SQL Server?

Andy Mallon:

One of the reasons that … There are few scenarios that Availability Groups just don’t fit the bill, or don’t quite fit the bill yet, unless you’re on 2016. Availability Groups require Enterprise edition, or “Expensive edition”, as many people like to all it. If you don’t have deep pockets, then Availability Groups are out. Availability Groups also require a Windows Server failover cluster, which are also a bit more complex. If you’re in a smaller shop, if you don’t have a team of sys admins to help you with getting that Windows cluster setup. In those scenarios, I think having the Windows cluster, whether you’re doing a failover cluster instance, or an Availability Group. This cases, if you don’t have the expertise to support a Windows cluster, you’re actually going to get more downtime than you’d benefit from. There’s lots of switches to flip, lots of knobs to turn. I certainly am no expert on that part of things. From those regards, you might just not have the manpower to support it.

[00:29:00] Another spot where mirroring really shines is cross-domain, or domainless. You can mirror between two servers that are not on a domain, or two servers that are on separate domains. Dependent, again, not having a domain is one of those smaller shop type of scenarios, where you’ve got less infrastructure. Getting mirroring setup is really straightforward in those scenarios, and offers pretty good failover time. Again, I mentioned that, at the eCommerce company, we were doing failovers at about 10 or 15 seconds per database. A little bit longer on busier databases, a little shorter on less-busy databases. If you’re talking failover cluster instances, or log shipping, some of these other options people might look to, the failover time is always going to be much higher. That was actually the primary driver at the eCommerce company, was being able to get completely failed-over in seconds, rather than minutes. If they had used a failover cluster instance, then you’re restarting an entire instance. If you’ve got a lot of memory, starting up the instance can take a while.

Carlos Chacon:
Another little caveat there, is that if a sys admin is connecting and running some query, it will wait for that to complete, before it starts to failover. You could increase your time, potentially, there.

Andy Mallon: Right. You can have scenarios where your looking to a problem can actually make an outage worse.

Steve Stedman: Yes. The watcher effect.

Andy Mallon: Yes.

Steve Stedman: That’s right.

Carlos Chacon:

[00:31:00] I think you make a good point about the Windows cluster component. I won’t claim to have vast Availability Group experience, but I’ve setup several of them now. I agree that the majority of my problems from the Availability Group has come from the Windows side. Or, if you have something going wrong with the cluster, and it freaking out. Then, my database is affected. Versus, my database having something wrong, and it the reason for causing the outage.

Andy Mallon: Right. I know, particularly in, I think it was in 2012, that first version with Availability Groups, they were particularly fussing about if you lose quorum on the Windows cluster, for any reason, then all of the databases go offline. Even your primary database that was up and running that server, still up and running, but your databases go into a non-writable state, because you lost quorum on your cluster, and everything panicked and shut itself down, just to be safe.

Carlos Chacon:

[00:32:00] It thinks there’s a problem, and it wants to eject. Eject, and then [the other 00:31:53] cluster comes down. Now, you’re stuck. That’s where you have to have good relationships with the Windows folks, because that’s not necessarily a database, per se, problem, if they come together. Traditionally, I have seen lots of shops where those two groups haven’t always seen eye-to-eye.

Andy Mallon:

[00:33:00] Yeah. With Availability Groups, in particular, you have such tight integration. In mirroring, if you want to have automatic failover, you just need a third instance that acts as your witness. That instance is basically the same thing as the quorum configuration in a Windows cluster. In Availability Groups, instead of it being a SQL instance, it’s within the DBA’s control. Now, it’s the cluster quorum. It’s a little bit further out of our touch. In order to really understand how that works, you really have to be a system admin, or a clustering expert. Not a lot of DBAs are, so we have to really lean on sys admins. We have to learn more ourselves, but we also have to lean on a sys admin that understands how clusters work, and how Windows work to make that work for everyone.

Carlos Chacon: Yeah. Or, even at a minimum, making sure that we have the right permissions to be able to create the objects, active directory, and all that good stuff, if they’re not going to help us do that. There can be some definite roadblocks there.

Andy Mallon: Exactly.

Carlos Chacon: I want to circle back to replication, just for a minute. We hit it a little bit, and picked on it. Are there good scenarios why we would consider replication now, particularly, [now 00:33:56] that we have got these enhanced options?

Mariano Kovo:

That’s a nice question. I try to avoid it in [every client 00:34:07], when I [heard 00:34:08] replication. There are still a bunch of client here that are really using replication. I was working last week, for [inaudible 00:34:21]. They are a telecommunication company that have multiple different units, like mobile phones, like land lines, and different applications working. Every department owns their servers. They do not share data. When they have to ask for some information between them, they [may 00:34:48] replicate those SQL servers between them. They are different versions, different editions of data. Perhaps, the models of the databases are different, too. When you have to distribute those information between different models of tables, or applications, I think there is a little chance to use replication still.

Carlos Chacon: Sure.

Steve Stedman: You know, one area-

Carlos Chacon: Go ahead.

Steve Stedman: One area with replication, that I’ve seen it used recently, successfully, is with SQL Server, standard edition. You don’t have all the other options available that you would have with Enterprise edition. You need to offload some of your reporting work to a secondary server. Replication can be setup to move just a subset of the tables, off to a secondary reporting-type server, to remove that load from your primary production server.

Carlos Chacon:

[00:36:00] That was my thought, as well. If there was, for whatever reason, you could pinpoint … You have a large database, let’s say 1,000 tables, but your main reporting was only 10 tables, then replication could be pretty attractive, potentially, to just handle that data.

Steve Stedman: Yep. That’s one that, as long as that reporting system can somehow deal with the time it takes to re-initialize one of the publishers, if it needs to be re-initialized. If you can’t live with that, then replication is not a good option.

Andy Mallon:

[00:37:00] The other spot where replication stands out to me as being a great option, is the scenario where maybe, again, in a reporting environment, or for some sort of additional read activity, you want to maintain a longer history of data. With replication, you can replicate just inserts and updates, and not replicate deletes across to it. In a scenario like that, you can have maybe a reporting database, or a data warehouse, that has an indefinite history of data, but on production, you keep just 90 days, or a year. That’s a scenario where you can’t really do that with any of the other technologies that we’re talking about.

Carlos Chacon: That’s an interesting point. I think, however, I would still potentially attack that as moving over the data. I’m assuming, let’s just say it’s at the transactional level. I’m moving it at least every day, and I only need to keep five days’ worth on the source, or something, I’m thinking that I would just do those deletes … I guess not transfer them over. Log shipping, the deletes would come over. Availability Groups, it would come over. Mirroring, it would come over as well. Yeah. You’d have to have two separate … If you’re moving that data over, to your point.

Steve Stedman:
Another way to accommodate that would be with change data capture, or something like that. That’s always a mess to use.

Carlos Chacon: Right. Introduce another functionality there.

Steve Stedman: Yep.

Mariano Kovo: I have some client that replace replication with some [ETLs 00:38:20] to make this offload of the data, and don’t have to use the replication anymore. That’s an [inaudible 00:38:31] that we have, I think, in the standard edition, and enterprise as well. [crosstalk 00:38:36] to work with ETL and [inaudible 00:38:42]. Of course, you have to maintain it, too, and develop the ETL packages.

Carlos Chacon:

[00:39:00] Right. Going back to Andy’s point, if you have the infrastructure, and could support the ETL, then yeah, it makes a lot of sense. If you’re looking for something out-of-the-box, if you will. That’s true.

Steve Stedman: One of the things this really comes down to, is none of these options are really particularly good, or really particularly bad, generally speaking. Some of them may be great, and some of them may be horrible, based off your specific scenario, your environment, or what it is you’re trying to accomplish. It really just-

Carlos Chacon: [crosstalk 00:39:20]Steve Stedman: Go ahead.

Carlos Chacon: No, finish your thought. I’m sorry.

Steve Stedman: They’re really just tools in your tool belt that you can choose from. Depending on what you need, Availability Groups might be the best. Replication might be the best. Log shipping might be the best, and mirroring. Any of them might be the best, depending on that specific environment.

Carlos Chacon: Having said that, I think it was Mariano, or somebody said, when they hear replication in the client environment, “Ugh!” They internally groan. “Ugh!” True or false: that is the way you feel about replication. Steve?

Steve Stedman:
I’ll cringe a little bit, and get some goosebumps thinking about replication sometimes, and not want to deal with it. In the end, I’ll jump in and deal with it, no matter how much I like it or not.

Carlos Chacon: Of course. Andy?

Andy Mallon: True. Replication is definitely not high on my list of things that I want to look at, personally. When it breaks, it’s always a learning experience.

Carlos Chacon: Also true. Mariano, you agree?

Mariano Kovo: Yeah, totally agree with that.

Carlos Chacon: There you go. That might be worth something. We have seen situations where it has worked well. I guess I’d just say that my experience, mostly it’s been yeah, when those bumps in the nights do come, it’s a big bump, and it’s going to take a lot of my time.

Male: That’s probably the biggest issue.

Carlos Chacon:
Awesome. Last thoughts? Things that anybody wants to come back around to, before we jump into SQL family?

Steve Stedman:

[00:42:00] The other thing that I’ll say, advocating from my corner, and talking about mirroring, I do have to say that mirroring is my favorite way to do a migration to new hardware, or to a new version, because you can get the server setup, initialize mirroring to get your data moving across. You can mirror to a higher version. Once you failover, there’s no failing back, but you can mirror to that new version. Then, when it comes time to do that cut over, the amount of work that I have to do, as the DBA, is really just to do that failover, and make sure that the connection strings get updated, or DNS entry gets updated to that new server. The amount of work that I have to do, in that migration window, is really minimal. I can have all of my logins created, all of the data there, all of the jobs staged. I just have to turn them on. It’s just a matter of hitting that failover button. That’s the other spot that I think all of the HA technologies are able to [help 00:42:18], is with those one-time server failovers, when you want to move to another server. If downtime is a consideration, I always pick mirroring as my first choice.

Carlos Chacon:

[00:43:00] That’s a good point. Even in the Availability Group side, the OS has to be the same. SQL Server, of course, has to be the same. In 2016, they did introduce features where you could use a different version. Again, you have to have Windows 2016 to be able to do that. It really hasn’t even come out yet. I think it’s supposed to come out here, at the Ignite Conference. That’s really in the future, so to speak.

Andy Mallon: Right. If you’re on a current version, and using Availability Groups, there’s really no good way to get to a new cluster, and a new version. There’s a little bit of pain that’s going to be involved there, until that new version of Windows comes out, and you can get to that version. Then, you’ll have the tools to migrate to new hardware after that.

Carlos Chacon: That’s right.

Steve Stedman: Similar to what Andy said, on migrating to new hardware, if I’m migrating to new hardware a distant location from the primary, with possibly flaky internet connection in between, that’s where I would lean on log shipping to get there, where it may take a week to get all of the data copied over, before you can activate it on the other end, and have it catch up along the way.

Carlos Chacon: Sure. You could potentially have pauses in the middle there, as well.

Steve Stedman:
Yep. Just my preference, and it’s worked well in the past.

Carlos Chacon: Okay, very good. Should we do SQL family?

Andy Mallon: Let’s do it.

Carlos Chacon: As technology changes so fast, and even part of the discussion, as things continue to evolve and migrate, how is it that you guys keep up with technology, stay engaged, and understand what’s going on? Mariano, let’s start with you.

Mariano Kovo:

[00:45:00] Okay. I usually find myself constantly watching [inaudible 00:44:40] podcast on trying [inaudible 00:44:42]. Unfortunately, I don’t have the time to do as many as I want. I think the online resources are a very good point to start. With Azure, or platforms like that, or [inaudible 00:44:57] from Microsoft, or something like this, you can try new technologies. Of course, I am a user from a local chapter of [PASS 00:45:10]. It’s a great service of information, when you can share these technology changes, and discuss them.

Carlos Chacon: Very good. Andy?

Andy Mallon:

[00:46:00] For me, I really keep up probably primarily through Twitter. I follow lots of people that I think are much smarter than me. I see what they’re talking about, and I see what they’re posting about. Even if I don’t learn necessarily how to do things from their blog posts, I can always learn who is an expert in what area. Then, when something does come up, I have that bare minimum of know that if I want to find something on Azure, I know Grant [inaudible 00:46:00] has an article where he lists out other experts in subareas of Azure. Things like that, where I can learn a little bit about everything in little snippets. Slowly, all those little snippets that I get from Twitter, from linking to blog posts, and just seeing what other people are talking about, over time, those start to add up. Before you know it, you’ve actually learned something.

Carlos Chacon: There you go. There is a downside there, compañeros. If you’re too active in Twitter, then I’ll reach out to you and invite you on the podcast.

Steve Stedman: The next SQL family question would be if you could change one thing about SQL Server, what would it be? Let’s start with Andy on this one.

Andy Mallon: Oh, man. There are so many things that I would change.

Carlos Chacon: Keep mirroring around?

Andy Mallon:
You know? I would keep mirroring around for a little bit longer, definitely. If I had to pick one thing, I would change the default isolation level to be recommitted snapshot, what some people refer to as “recommitted with real versioning”. I think that that’s really the best way to get consistent reads. With a little bit of luck, if you’re using recommitted snapshot, you can convince fewer people to use No-Lock, which is one of my many pet peeves.

Steve Stedman: Nice. I like that one. That is one my peeves as well, with No-Lock. If you listen to, I think, last week’s episode, we ranted about that a little bit. Mariano, as far as changing one thing about SQL Server.

Mariano Kovo:

[00:48:00] Okay, there are many things, too. I was working in the [BI 00:47:51] side in the last months, too. I think that there are too many products in the same product. I don’t know if you mean what I am saying. In SQL Server 16, there are so many choices. You have to install one database server that you get crazy about, “What I have to tell? The R server, [inaudible 00:48:14] server, the integration server, the reporting server.” We have to limit that [things 00:48:23].

Steve Stedman: Maybe focus a little bit more on the core offering, and then spin some of those off into a separate product?

Mariano Kovo: Exactly.

Carlos Chacon: Or, even a more dynamic install. Almost like the [web 00:48:39]. “I want to install the database, I only see database stuff.” It’s not going to ask me about SSIS.

Mariano Kovo: Exactly.

Carlos Chacon: What’s the best piece of career advice you’ve ever received?

Mariano Kovo:
Difficult one. Any thoughts?

Andy Mallon:

[00:50:00] I have one. I would say that some of the best advice I ever got is that the more you screw up, the more you’re going to learn. But, when you screw up, admit it, fix it, ask for help if you need it. If you’re always timid about making a mistake, or always timid about not being the best, you never really get anywhere. Sometimes, you just have to get out there, whether you’re trying something new, or trying something different, get out there. Whether it’s a new type of job, if you want to be a DBA, and you’re not yet. If you want to use a new technology, try it out. If it breaks, it breaks. Admit it. Say, “I messed up. I’m working on it. This is the plan to fix it.” I think you get a lot further, both of this things. Making mistakes, and then owning up to it.

Carlos Chacon: Yeah. There’s no question.

Mariano Kovo: [inaudible 00:50:14]Carlos Chacon: Our last question today, gentlemen. If you could have one superhero power, what would it be, and why do you want it? Andy?

Andy Mallon: I would definitely be able to fly. I am incredibly impatient. I can’t stand waiting in traffic. I don’t like crowded subway platforms. If I could fly, I could get where I want to be quick, over the tops of the crowds, and never had to wait in traffic again.

Carlos Chacon: Very nice. Mariano?

Mariano Kovo:
I have my favorite superhero, that is Flash. Speed, it would be my best super power.

Carlos Chacon: There you go. For those who are afraid of heights, right? Speed is the alternative to flying. Gentlemen, thanks so much for being with us today. I appreciate the discussion. I think we had some good insights. We appreciate you taking some time to chat with us.

Mariano Kovo: Okay. Thank you very much.

Andy Mallon: Thanks for having me.

Steve Stedman: Have a good day, guys.

Carlos Chacon: We’ll put an ending on that, and we’ll wrap that bow up very nicely.

1 Comment
  • Podcast: Mirroring, Availability Groups, Replication, and Log shipping – Andy Mallon – AM² August 31, 2016 at 8:32 am

    […] Take a few minutes to listen to this episode of the SQL Data Partners Podcast. […]

Leave a Reply

Back to top