Monday, June 11, 2012 11:25 AM
I have SQL Server 2005 Workgroup Edition installed on a dedicated server in my domain, for performance and redundancy I would like to mirror or replicate the databases with another server running the same version of SQL Server.
I will have 2 x Dell PowerEdge 2650s running Server 2003 loaded up with SQL Server, while both servers are up I would like them to load balance queries and if one server goes down the other takes over.
Question is - Is this possible with my version of SQL Server or do I need to upgrade? How do I set up such a system? Do I need to use an "instance" in the same way DFS uses a name space?
Monday, June 11, 2012 11:29 AM
Mirroring is supported in EE and Std Editions.
Workgroup Edition adds limited publishing capabilities. It's able to serve five clients using transactional replication and up to 25 clients usin merge replication. It can also act as a replication client.
Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/
Monday, June 11, 2012 11:37 AM
Thank you for the reply,
Could you expand a little on your answer, what can I actually do with the version I have?
From you reply I gather mirroring is not supported, but I can perform replication. Will replication load balance and provide redundancy? do I need to use an instance?
Monday, June 11, 2012 12:26 PMModerator
>>Will replication load balance and provide redundancy?
There is a great deal of, "It Depends" that goes with this question. Some databases cannot handle a full replication scenario. This means you need to determine if the database can even support the type of replication you are looking for. For example, replication may need to alter a table to retain a guid or primary key and that table may be a HEAP and altering it could effect the applications consuming or changing the table.
For redundancy: no. Not out of the box. Replication is more of a business synchronization or distribution feature. This means, it is meant to distribute data to other business logical data areas. Now, replication can be used for some sort of sudo HA/DR situations. Be warned though, there is no built in failover mechanism or overall intelligence to the partnership. This means everything will need to be manually put together by you or another DBA to ensure when the time comes, and it usually does sooner or later, to have the business rely on that secondary instance or subscriber, that it ensures business continuity continues. Putting the cost to all of this setup and maintainability, the cost to move to standard may turn out to be less and more ROI.
do I need to use an instance?
A little confused on this question. The SQL Server is the instance. named or default to the machine. Mirroring or replication does not require the same level of configuration as clustering.
Ted Krueger Blog on lessthandot.com @onpnt on twitter
Troubleshooting SQL Server: A Guide for the Accidental DBA
Please click the Mark as Answer button if a post solves your problem!
- Marked As Answer by JayHaworth Tuesday, June 12, 2012 7:20 AM