Solved

SQL Server 2008 R2

Posted on 2014-01-12
8
328 Views
Last Modified: 2014-01-14
Hello Experts:

I need to set up a high availability infrastructure in SQL Server 2008 R2 on Windows Server 2008 R2.  

I am planning on either doing Replication with Transactional Logs or Log Shipping.  I would prefer Replication.  However, Replication does not publish or replicates any table that does not have a primary key column.  I do not know if the same is true for Log Shipping.

If the primary SQL Server 2008 R2, or publisher, goes down for good, can we get the subscriber to have the same data than the publisher even though tables that do not have a primary key column do not get replicated?

Does Log Shipping take into account those tables that do not have a primary key column as well as those that have a primary key column?

I hope I am explaining myself in a way you can understand what I am looking for.



Thanks.
--Willie
0
Comment
Question by:willie0-360
8 Comments
 
LVL 8

Assisted Solution

by:piyushranusri
piyushranusri earned 125 total points
ID: 39775858
0
 

Author Comment

by:willie0-360
ID: 39775935
piyushranusri:


Thanks for the links.  They provide some good background information.  

The primary server runs on a physical machine, while the secondary is on a virtual machine.  

I went through the articles the links refer to, but I am not still sure I can make a decision based on what I read regarding whether to go with Replication with Transaction Logs or with Log Shipping.

I need to know if the secondary database will have the same data or information than the primary one if Replication is used given the Replication does not transfer tables that do not have a primary key column.  I need to know what is the effect of leaving those tables outside the replication.

I think Log Shipping transfers everything.  I think it transfers all the tables regardless, but I am not sure.  I am not sure what the literature means by "objects."


Thanks.
--Willie
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 75 total points
ID: 39775957
Is mirroring not an option? Mirroring is the best of the three as far as HA goes. If not then log shipping should be the preference over Replication for a HA setup.
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 100 total points
ID: 39776038
it is not clear where is your 2nd server (s)
but as Ideas Logshipping it is: transaction log backup and restore to a "Standby" Server(s) ( can be more than 1)
it will take care of all DB in Full recovery mode...
and good for DR

more
/ http://technet.microsoft.com/en-us/library/hh393561.aspx /
The white paper Proven SQL Server Architectures for High Availability and Disaster Recovery2 shows the details of five commonly used architectures:

Failover clustering for HA and database mirroring for DR.


¿ Synchronous database mirroring for HA/DR and log shipping for additional DR.


¿ Geo-cluster for HA/DR and log shipping for additional DR.


¿ Failover clustering for HA and storage area network (SAN)-based replication for DR.


¿ Peer-to-peer replication for HA and DR (and reporting).
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 200 total points
ID: 39777458
Log Shipping is a copy of everything that happens (schema changes, new tables, everything) since the transaction logs contain a way to completely recover to any point in time.  The failover is manual as you must restore the tail from the main database to the log shipped copy to recover it. Benefit is you can have this secondary read-only.

Transactional Replication replicates all transactions but is a fairly heavy way to get HA. It can do schema changes and data changes, but if you add a table or a stored procedure it will not replicate that. For the new table you would have to create an article and subscribe to it, so the overhead of management is fairly high if you are strictly looking for HA.

Mirroring is like Log Shipping except it is automatic once set up and running. It has some requirements that must be met and some limitations. The requirements are more like side effects like in a mirror using synchronous (asynchronous only available in Enterprise Edition) when a transaction is applied to the Primary it must be applied to the mirror before it is considered complete. If you change schema, you must break the mirror and reestablish the mirror with a new copy of the database and logs then you restart mirroring. In order to have auto failover you have to have a witness.

Hopefully that gives you an idea of your options.
0
 

Author Comment

by:willie0-360
ID: 39779718
Hello to you all:

I am considering either Data Mirroring or Log Shipping.  I would like Data Mirroring better, but it does not mirror the system databases.  Also, I have read it has limitations as to the number of databases it supports on a 32-bit architecture.  I am running it on a 64-bit architecture, but I do not know what kind of limitations it may have there.  Please let me know, if any.

I think I will have to go with Log Shipping for now.

Is it possible to change the HA configuration once one is in place?
I mean, if I go with Log Shipping, can I later change it to Data Mirroring or a combination of Data Mirroring and Log Shipping?

Thanks.
--Willie
0
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 200 total points
ID: 39779763
You can change it from Log Shipping to Mirroring, as it is a different way, but uses backups and log backups.

As for the limit on different architectures, I am not sure that I have heard that other than limitations on RAM addressibility, but not the number of databases.

System databases are only really able to be HA if you use Clustering.
0
 

Author Comment

by:willie0-360
ID: 39780312
I will go with Log Shipping.   Thanks a lot for all of your input that helped me make a decision.


--Willie
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now