different physical servers (High availbility + FULL transaction safety +
Automatic failover with a third server as witness) and simultaneously set up
network load balancing between these 2 servers (option with Windows Server 2003
Enterprise) ?
If yes, which type of licenses do I need and how
many (for SQL server 2005 and Windows Server 2003) with such a configuration
?
- 4 physical servers in total : A, B, C and
D
- load balancing between A and B (same application)
- load balancing between C and D (same application)
- A hosts principal DB X, B hosts mirror of DB
X
- C hosts principal DB Y, D hosts mirror of DB
Y
- D is witness for A and B
- B is witness for C and D
- A and B : only internal clients <
25
- C and D : both internal and external clients
(internal clients <25, number of external clients is unknown (>25)
)
Hoping this description will be helpful enough for
you...
(I am looking for a very high availability
system)
Thank you in advance for your support.
This licensing question is pretty much urgent (for
a bid) : a quick answer would be very appreciated...
Not going in details of your design.
There is one thing I know for sure. MS is considering a "mirror" as 1 license. Confirmed by MS "presales and licensing division". So, whatever license you will bye (Enterprise or Standard depending on hardware you are going to use) - you need two licenses for MS SQL for 4 servers listed in your design.
|||The database on mirror server is always in the "Restoring" state, and you can't connect to this database. I didn't get how you plan to achieve load balancing with this.
|||The load balancing is set up for automatic total load transfer on one server when the other one is lost (high availability, no time to activate the back-up server).
When 100% load is taken by one server, I need this server to access (read & write) to the back-up SQL database that shall be a real mirror of the one located on the lost server.
I am not familiar at all with such systems that's why I need validation of the concept I imagined.
According to your comment, mirror database is only here to be able to restore the lost database but, in any case, it could be used as a replacement of the lost one. Am I right ?
Could you recommend me a redundant system architecture maximizing availability ?
Thanks in advance for your expertise.
Yann
|||Database mirroring allows you to maintain a hot standby of your database.
The mirror database is always in restoring "state", and not available for user connections. When your principal server (the primary server serving the application) fails, you use the mirror to failover. Please note that you don't do a "restore" with a mirror as you mentioned, but failover to the mirror so that the database becomes available to your application.
I suggest reading the following:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx
SQL Server Books Online for Database Mirroring.
|||Tom Rizzo has a chapter in his book "Pro SQL Server 2005" from Apress on database mirroring.
He discusses the ability to use High Availability Mirroring (i.e. automated failover) while using the second server for queries. A quick synopsis is:
A Mirror solution only runs as fast as the slowest database server. So, when you write to the primary database, and the mirror is working, then the time to execute that write is the slowest time of writing to the primary database or the mirror database. So, if you are taxing the resources of your mirror database with other kinds of activity, it can slow down your overall application because the primary server won't commit a transaction until the mirror has also committed the same transaction.
That being said, you can still use the mirror for read only access of the data being mirrored throught the use of Snapshots. As I recall, snapshots are only available in the Enterprise version of SQL Server.
Even though Tom only has one chapter on Mirroring, I found it VERY helpful, and a lot easier to wade through than the MS documentation; at lease for establishing a base understanding of the technology.
Cheers,
Ben
|||When the database mirror is only used for a standby in the case of emergencies, it does NOT need to be separately licensed. If the mirror is used for other purposes, including reporting, then it must be licensed.
Regards,
Matt Hollingsworth
Sr. Program Manager
SQL Server High Availability
|||Thanks for your reply.
I have then others questions :* what could I really do with the mirror database after failover of the main database ?
* Could clients still enter new entries or make some modifications on the database ?
* How does the procedure to come back with the main database occur ?
Thanks in advance for you additional information.
Yann
No comments:
Post a Comment