SQL High Availability omhandler hvordan du sikrer at du altid er i drift med din SQL database.

Database mirroring–survival kit #4

This is the final part of database mirroring, and here I want to give you some nice tips and also some links to get you a little bit further.

First of all. Showing you the videos are very easy, because I am used to do this. When you start working it can be a challenge for you, but I suggest that you setup a virtual machine and do a snapshot once in a while.

When setting database mirrorring up in a production environment, then be aware of the following:

  1. Security. I did just click next –> next –> next, but you must think about security, and eventhough it is irritating, then make sure that you think about the principle least privileged.
  2. Endpoints must be configured correctly. Do you need encryption? (yes of course Smiley), what ports are you going to use and what credentials are you using?
  3. Make sure that ports are open in the firewalls for the ports you are using.
  4. Do we need high safety or high performance.
  5. Do we need automatical failover or not?

If you want to learn more about database mirroring, then I can recommend the following link:

http://msdn.microsoft.com/en-us/library/ms189852%28v=sql.105%29.aspx

if you get into troubles, then I would suggest:

http://www.sqlskills.com

http://www.sqlservercentral.com

http://www.sqlauthority.com

 

That was the final step in this survival kit for Database Mirroring.

<—Go to step 3

Database mirroring–survival kit #3

Now it gets a little bit tricky, because how will you handle databasemirroring and replication together?

Let’s imagine a scenario, where we want to replicate a table from a mirrored database to another database. If we failover, then replication should continue to work. Is that possible?

Yes, and it is fairly easy to setup, what you need to do is:

  1. Setup the mirroring.
  2. Setup a distributor and publication on the current principal server(think about having the distributor on its own server).
  3. On the mirrorserver setup distribution and use the same distributor as on the current principal server.
  4. Add both the distributor and mirrorserver to the replication monitor.
  5. Set –PublisherFailoverPartner property for the agents (in my demo it will be the snapshotagent and the log reader agent.
[youtube=http://www.youtube.com/watch?v=JNb5dNqWj4c&w=448&h=252&hd=1]

<—Go to step 2      Go to step 4—>

Database mirroring–survival kit #2

So, in the previous article about database mirroring, we saw how to setup database mirroring, in this short demo, I will show you how to create a very small .net application to utilize from database mirroring.

The problem is that two instances of a database either has two different IP address and two different names, and earlier a developer should use a lot of time to configure that.

Now we can add some settings to a connectionstring to define the failover partner.

Normally a connectionstring to a SQL Server looks something like:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

and now we can simply change it to:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=True;Failover Partner=myMirrorServerAddress;

And now database mirroring is working in the application.

Let me show you how it works.

[youtube=http://www.youtube.com/watch?v=nqyUHjV0c1E&w=448&h=252&hd=1]

<—Go to step 1      Go to step 3—>

Database mirroring–survival kit #1

This blog is intended to give you a short introduction to Database mirroring. Database mirroring is a high availability functionality, that are easy to setup, reliable and has two features that I like:

  1. It makes it easy for developers to create software against a mirrored database (I will explain that later).
  2. It has automatic failover.

Basically the question is: What is database mirroring?

Imagine that you have a database that are critical for you. It could be a paymentDB, and it is used by a website to track customers payments. If the database is down, then your customers can’t pay and therefore you loose money!!!!

Today a lot of important software and websites have a setup like this:

image

 

You have a website and a single point database that are running on a server. Problem is that if your paymentDB is offline for some reason, then your website does not work (and yes, I have seen that a lot of times, and when I talk to customers about that, then they always says that “our database is never offline”
 Well!! I can guarantee you that, their database will be down sometimes).

 

So, what we want is a setup, that could look a little bit like this, where the webapplication is communicating with the RunningDB, but if that fails, the the database that are “Ready to take over”, becomes the runningDB.

 

image

That is the basics of Database mirroring, so lets see what to do.

First of all, you must at least have two instances of Microsoft SQL Server installed. They can be on the same server or on two different servers. There is a lot of benefits on having it on two servers, and basically, you are more protected and have a better chance for have a good uptime.

In this demo, I just have some instances on the same machine.

So to get started, then you must:

  1. Have at least two instances. If you want to have automatic failover from the one database to the other (from one instance to the other one, then you must also have an instance that becomes the witness).
  2. Have a database that meets the requirements for database mirroring.

So, lets try to see how that works in.

[youtube=http://www.youtube.com/watch?v=Q8Su4RvObDM&w=448&h=252&hd=1]

 

It is quite easy, but unfortunately there are some important things that you need to know, and the two most important are:

  1. What did I configure when I clicked Next –> Next –> Next
 We talk about the endpoints.
  2. Operational modes. What does the three modes mean.

I have attached the video below, so that you also can get an understanding about that.

[youtube=http://www.youtube.com/watch?v=85zfWgDEpO0&w=448&h=252&hd=1]

In survival kit #4, we talk a little bit more about security, because when you have all your instances running on the same server, then it is very easy. It becomes complicated when you want a more complex setup.

Go to step 2 –>

Merge replication – a beginners guide

If you want to share data between multiple databases, then replication could be an alternative, and there are four types of replication you should know to be able to choose the best option:

  1. Snapshot replication
  2. Transactional replication
  3. Peer-to-peer replication (a kind of transactional replication)
  4. Merge replication

In this video I will show you how to setup Merge replication.

 

[youtube=http://youtu.be/VxeUMlLW4rc]

Peer to peer replication – a beginners guide

If you want to share data between multiple databases, then replication could be an alternative, and there are four types of replication you should know to be able to choose the best option:

  1. Snapshot replication
  2. Transactional replication
  3. Peer-to-peer replication (a kind of transactional replication)
  4. Merge replication

In this video I will show you how to setup Peer to Peer replication.

[youtube=http://www.youtube.com/watch?v=Ld_bwHe8kcQ]

Transactional replication – a beginners guide

If you want to share data between multiple databases, then replication could be an alternative, and there are four types of replication you should know to be able to choose the best option:

  1. Snapshot replication
  2. Transactional replication
  3. Peer-to-peer replication (a kind of transactional replication)
  4. Merge replication

In this video I will show you how to setup Transactional replication.

[youtube=http://youtu.be/mW1c7QgiEJ0]

Snapshot replication for beginners

If you want to learn a little bit about one of the types of replications, that can be used to distribute data to another database/instance/server, then you should see this video about Snapshot replication.

Imagine that you have a server in one location and a server in another location. They have both a SQL Server installed, and on the first server you have a database called CRM. From that database you want to copy one of the tables, called Customers to a database called CUSTOMERS on the other server.

You want it to be done once a week and there are only 1.000 customers.

In that situation a Snapshot replication could be a good choice. In snapshot replication you:

  1. Create a publication (Meaning: Customertable from CRM database is now an article in the publication).
  2. You send it to a distributor that will deliver it to
  3. A subscriber on the other server.

In the video you will see how a snapshot replication is created.

[youtube=http://youtu.be/HatnWBU4V9o]