Solved

MSSQL High Aviabalability questions?

Posted on 2012-03-28
5
315 Views
Last Modified: 2012-04-21
We are using MS SQL 2008 with R2 in Windows 2008 server

1). I am planing to implement HA to my company. I know in MS SQL, we can do mirroring, log shipping and replication. Can someone tell me what is the difference among them? What advantages and disadvantages for them?

2). Can we do replication for system database? what is the per-requsite for doing transaction replication?

3). After the log shipping is done, can we create tables or add field to table in standby server. Do it cause the log shipping job down if I do so?

4). What mode should I use when doing log shipping?
0
Comment
Question by:wasabi3689
5 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 50 total points
ID: 37780756
you need to be more specific with your requirements
by ha you mean:
1) you want an additonal stand by instance you will be able to fall back to when the main instance fails
2) you want to add more instances to spraed the work load since a single instance can't handle all the work
3) where would the different nodes be physically (same lan, same san etc...)
4) how much latency between the nodes can you efford if any at all?
5) how fast should the switching between them be?
0
 
LVL 10

Assisted Solution

by:sqlservr
sqlservr earned 100 total points
ID: 37781366
1).it depends upon on your requirement , if you want aotomatic failover , you can user Mirroring,
manual Log shiping is good.
2). Can we do replication for system database? what is the per-requsite for doing transaction replication?
no we cant do replication for system databases,
pre requist
1_ both the db should be in FUll recovery mode, suggested to be
2) good power full NIC cards
3) plan how you RAID are perofrming.

3). After the log shipping is done, can we create tables or add field to table in standby server. Do it cause the log shipping job down if I do so?
asolutely no
as the databases are in the standby(readonly) and no recovery mode, additions are not allowed

4). What mode should I use when doing log shipping?

Full recovery mode
and both the Servers principle and standy should be started with the same user account,
0
 

Author Comment

by:wasabi3689
ID: 37782423
One more question about replication.

Can we do a replication for a table without any key set and index?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 37785166
Can we do a replication for a table without any key set and index?
No.  The table requires a Primary Key.
0
 
LVL 10

Assisted Solution

by:sqlservr
sqlservr earned 100 total points
ID: 37785717
no we cant to transactin replciation, withour a primary key in
.

we can do merge replication for   that
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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

16 Experts available now in Live!

Get 1:1 Help Now