November 10, 2010 at 11:19 am
Hello,
We are working on a development of building a middleware/staging application which will be used for send and receive data to/from different systems (AS/400,XML,SQL). For the staging we will be creating,
1. Windows service for XML processing and database updates.
2. SSIS packages (called from SQL jobs) for data transfer.
3. Cross datatabse stored procedure execution for database transfer.
We are using SQL server 2005 for development. In production it will be Enterprise edition. I am new to SQL server deployment and hence need help in giving overview of overall deployment architecture and archieving policies. Can you please look in below questions and try answering them?
1. Since it is an OLTP, we need to DB to be up and running all the time. What is the best method Clustering / Mirroring?
2. Since I am having SQL jobs as well running in the DB what extra care I need to take care?
3. Cost involved in each option
4. Advantages and disadvantages over each other.
Thanks for your help.
Vishal
November 10, 2010 at 1:05 pm
With clustering you have 2 or more physical SQL Servers accessing a shared disk but appearing as one. In the event of one server being unavailable the cluster will failover to the 2nd node and this failure will be transparent to the application.
You configure Windows cluster services and install SQL Server over the top of it.
With mirroring you have two separate servers and two separate databases. If you want automatic failure then you will need a machine to act as a witness.
The application will need to have a connection string with a failover partner argument. In effect the connection string tells you want the principal server and mirrored server are.
Without the witness failover has to be carried out manually.
The downside to mirroring is that you double your storage requirements and the IO so on high transaction systems this can be a big problem. The plus side is that you have not only a 2nd data store but also a 2nd server where as clustering you have a 2nd machine but still the same data store. If you have a good disk system such as a SAN then the data is pretty resilient anyway.
Clustering can be active/passive, in which case you license the active nodes only.
In Active/Active you have to license both nodes.
Its been a while since I read the M$ recommendations but at one point they were recommending mirroring clustered servers for ultra-resilience. Expensive and quite probably overkill for most DBS.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply