Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MSSQL High Aviabalability questions?

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Nested Case statement 4 37
sql server query 12 24
SQL Quer 4 21
SQL - Simple Pivot query 8 11
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.

808 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