x

Sql server redundancy

How do I build redundancy in sqlserver, I have sqlserver 2005 and 2008. I know we can build redundancy in mysql, and how do I build that in sqlserver. Any documents that I can read will be helpful.
more ▼

asked May 03, 2012 at 09:38 PM in Default

Chitrarekha gravatar image

Chitrarekha
140 32 42 43

I am trying to protect against data loss and I have maintenance plan built that does fullbackup, differential backup and transactional backup. All these backups are stored in san resources. However we would like to have other alternative where we can bring the application as well as the database available if the primary install hardware fails.
May 04, 2012 at 01:10 PM Chitrarekha
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

OK, so you're looking for high availability. In that case, the technologies you should be considering and investigating are:

  • Failover Clustering
  • Database Mirroring
  • Log Shipping
  • Replication

Which particular one you go for depends on your own requirements in terms of performance, cost, uptime, etc. There's a basic overview at SQL Server Central that applies to SQL 2005, and the rest of the first page or so of the appropriate Google search is also worth reading.

I would recommend Allan Hirt's book on the subject, Pro SQL Server 2005 High Availability.

more ▼

answered May 04, 2012 at 03:01 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left
As well as the options that @TimothyAWiseman suggests, there's also database mirroring to consider - http://technet.microsoft.com/en-us/library/cc917680.aspx
more ▼

answered May 03, 2012 at 10:50 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

That one slipped my mind, thanks for mentioning it.
May 03, 2012 at 10:51 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

It depends on what you mean. If you mean that you want redundant data, you may want to evaluate carefully whether you really want that. Redundancy in data generally violates the normal forms and risks an inconsistency in your data in addition to storing more data and using more space.

Depending on why you want redundancy in data, there is almost always a better way to achieve the goal. For instance, if you are trying to protect against data loss than backups and log backups will help insure you do not lose data and provide point in time restore options. If you want redundancy for performance reasons then you are probably better off using more efficient, optimized queries or looking at indexed views

If you mean you want redundant hardware for high availability purposes then there are a variety of options depending on your exact needs. [Clustering][3] is in one option, but log shipping can also be used. I have also heard of replication being used for that purpose, though that is a bit of a stretch and can be somewhat awkward.

Hopefully this will help, but if this does not answer your question it might be helpful if you could expand the question slightly.

[3]: http://msdn.microsoft.com/en-us/library/ms179530.aspx
more ▼

answered May 03, 2012 at 10:19 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

I wish I could give another +1 for starting your answer with "it depends"...
May 03, 2012 at 10:50 PM ThomasRushton ♦
Heh. That is the traditional answer with SQL Server questions, right? ;-)
May 04, 2012 at 04:38 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945

asked: May 03, 2012 at 09:38 PM

Seen: 979 times

Last Updated: May 04, 2012 at 04:38 PM