Solved

MSSQL High Aviabalability questions?

Posted on 2012-03-28
5
317 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

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.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
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…

773 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