Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

SQL Server 2008 R2

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
willie0-360
Asked:
willie0-360
5 Solutions
 
piyushranusriSystem Cloud SpecialistCommented:
0
 
willie0-360Author Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Eugene ZCommented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
willie0-360Author Commented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
willie0-360Author Commented:
I will go with Log Shipping.   Thanks a lot for all of your input that helped me make a decision.


--Willie
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now