Solved

MSSQL High Aviabalability questions?

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 39
T-SQL Import data in Microsoft SQL Server Management Studio using INSERT 3 24
IF SQL Query 12 29
Connect Gridview column to Textbox in C# 2 40
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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

749 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