Solved

SQL Server 2008 R2

Posted on 2014-01-12
8
329 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 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