Solved

SQL Server 2008 R2

Posted on 2014-01-12
8
331 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

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 …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

840 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