July 28, 2011 at 3:34 am
Hi All,
We would like to implement our own type of manual cluster solution due to cash constraints:
Hardware setup:
2 Servers running Win Server 2008 std + SQL Server 2008 R2
1 Shared Direct attached storage (Will hold DB files)
The DB files will be kept on the DAS and the 2 servers and SQL instances will have the DB attached.
Both servers will be running continually, but only one server will be used as the service provider and the other will be switched manually if the one crash.
We can manually attach the DB on the backup server once the one server is down.
What will happen if another SQL server instance try and use a DB already attached, eg. when the backup server is in use and the primary server suddenly goes online?
Our thoughts were, that the DAS provides us with the best availability of the data, and that managing the 2 servers can happen remotely, especial when disaster strike.
Please note that backups will be implemented for worst case scenarios.
Should we rather be looking at a different solution?
Any advice will be greatly appreciated 🙂
Kind regards
July 28, 2011 at 3:53 am
How would data get copied from one server to the other?
I think you would be better off dropping the custom idea, and simply implement database mirroring
July 28, 2011 at 4:03 am
The DAS enable each server to access the DB as if it was on a harddrive in the server it self.
Thus sharing the same data 🙂
July 28, 2011 at 4:06 am
ajsnyman (7/28/2011)
The DAS enable each server to access the DB as if it was on a harddrive in the server it self.Thus sharing the same data 🙂
I am not sure if I understand what you are saying.
do you mean that both servers will be using the same Data and Log Files?
July 28, 2011 at 4:08 am
Why not database mirroring ? its a high availability solution with automatic fail over ? i think it would work better for you than having to manually fail over , you could even use Replication if you planning to stick to manual fail over , the subscriber could even used for read or reporting in parallel.
July 28, 2011 at 4:14 am
Each server will have its own SQL instance, but only one SQL instance will have the DB attached.
So when the primary server dies, so we can just attach the DB on the backup server's SQL instance.
One problem is, what happens if the primary server comes online and try connecting to the DB while it's attached on the backup SQL instance.
Hope this helps 🙂
July 28, 2011 at 4:20 am
ajsnyman (7/28/2011)
Each server will have its own SQL instance, but only one SQL instance will have the DB attached.So when the primary server dies, so we can just attach the DB on the backup server's SQL instance.
One problem is, what happens if the primary server comes online and try connecting to the DB while it's attached on the backup SQL instance.
Hope this helps 🙂
I would not recomend this approach as you will have a lot of issues when the primary db crashes and there are open transactions and you will not be able to attach it to the secondary server.
seriously forget this approach and implement mirroring, its what you are trying to do anyway, but it has been fully implmented and tested also it will allow automatic failover for your .net clients which your approach wont
July 28, 2011 at 4:20 am
Jayanth_Kurup (7/28/2011)
Why not database mirroring ? its a high availability solution with automatic fail over ? i think it would work better for you than having to manually fail over , you could even use Replication if you planning to stick to manual fail over , the subscriber could even used for read or reporting in parallel.
This can work, we are concerned abt. the performance toll that mirroring will take on the server.
It also mean we have to duplicate the space required in both servers, where the DAS provide one consolidated storage.
July 28, 2011 at 4:27 am
ajsnyman (7/28/2011)
Jayanth_Kurup (7/28/2011)
Why not database mirroring ? its a high availability solution with automatic fail over ? i think it would work better for you than having to manually fail over , you could even use Replication if you planning to stick to manual fail over , the subscriber could even used for read or reporting in parallel.This can work, we are concerned abt. the performance toll that mirroring will take on the server.
It also mean we have to duplicate the space required in both servers, where the DAS provide one consolidated storage.
I think you are missing the point, if the server crashes then the mdf may not be usable at all leaving you up the creek.
July 28, 2011 at 4:28 am
steveb. (7/28/2011)
ajsnyman (7/28/2011)
Each server will have its own SQL instance, but only one SQL instance will have the DB attached.So when the primary server dies, so we can just attach the DB on the backup server's SQL instance.
One problem is, what happens if the primary server comes online and try connecting to the DB while it's attached on the backup SQL instance.
Hope this helps 🙂
I would not recomend this approach as you will have a lot of issues when the primary db crashes and there are open transactions and you will not be able to attach it to the secondary server.
seriously forget this approach and implement mirroring, its what you are trying to do anyway, but it has been fully implmented and tested also it will allow automatic failover for your .net clients which your approach wont
Cool, this is a good argument, that was the biggest fear of our implementation.
What is the performance concern and possible data loss with mirroring?
July 28, 2011 at 4:31 am
ajsnyman (7/28/2011)
steveb. (7/28/2011)
ajsnyman (7/28/2011)
Each server will have its own SQL instance, but only one SQL instance will have the DB attached.So when the primary server dies, so we can just attach the DB on the backup server's SQL instance.
One problem is, what happens if the primary server comes online and try connecting to the DB while it's attached on the backup SQL instance.
Hope this helps 🙂
I would not recomend this approach as you will have a lot of issues when the primary db crashes and there are open transactions and you will not be able to attach it to the secondary server.
seriously forget this approach and implement mirroring, its what you are trying to do anyway, but it has been fully implmented and tested also it will allow automatic failover for your .net clients which your approach wont
Cool, this is a good argument, that was the biggest fear of our implementation.
What is the performance concern and possible data loss with mirroring?
Perfromance concern is minimal in my experience, if it is a deciding factor then you should look at increasing the perfromance of your database using other methods of tuning. Also the reason there it takes some performance is because it is doing the neccesary work to keep the databases in sync which would be impossible otherwise... there is no such thing as a 'free lunch'..
data loss can be zero if setup correctly
July 28, 2011 at 4:42 am
steveb. (7/28/2011)
ajsnyman (7/28/2011)
steveb. (7/28/2011)
ajsnyman (7/28/2011)
Each server will have its own SQL instance, but only one SQL instance will have the DB attached.So when the primary server dies, so we can just attach the DB on the backup server's SQL instance.
One problem is, what happens if the primary server comes online and try connecting to the DB while it's attached on the backup SQL instance.
Hope this helps 🙂
I would not recomend this approach as you will have a lot of issues when the primary db crashes and there are open transactions and you will not be able to attach it to the secondary server.
seriously forget this approach and implement mirroring, its what you are trying to do anyway, but it has been fully implmented and tested also it will allow automatic failover for your .net clients which your approach wont
Cool, this is a good argument, that was the biggest fear of our implementation.
What is the performance concern and possible data loss with mirroring?
Perfromance concern is minimal in my experience, if it is a deciding factor then you should look at increasing the perfromance of your database using other methods of tuning. Also the reason there it takes some performance is because it is doing the neccesary work to keep the databases in sync which would be impossible otherwise... there is no such thing as a 'free lunch'..
data loss can be zero if setup correctly
I know MS specifies that you only need to license one SQL server and can maintain a backup SQL as long as it is not in use, will this be the same when implementing mirroring?
Thank you very much 🙂 This will help make our choices so much better.
July 28, 2011 at 4:44 am
ajsnyman (7/28/2011)
steveb. (7/28/2011)
ajsnyman (7/28/2011)
steveb. (7/28/2011)
ajsnyman (7/28/2011)
Each server will have its own SQL instance, but only one SQL instance will have the DB attached.So when the primary server dies, so we can just attach the DB on the backup server's SQL instance.
One problem is, what happens if the primary server comes online and try connecting to the DB while it's attached on the backup SQL instance.
Hope this helps 🙂
I would not recomend this approach as you will have a lot of issues when the primary db crashes and there are open transactions and you will not be able to attach it to the secondary server.
seriously forget this approach and implement mirroring, its what you are trying to do anyway, but it has been fully implmented and tested also it will allow automatic failover for your .net clients which your approach wont
Cool, this is a good argument, that was the biggest fear of our implementation.
What is the performance concern and possible data loss with mirroring?
Perfromance concern is minimal in my experience, if it is a deciding factor then you should look at increasing the perfromance of your database using other methods of tuning. Also the reason there it takes some performance is because it is doing the neccesary work to keep the databases in sync which would be impossible otherwise... there is no such thing as a 'free lunch'..
data loss can be zero if setup correctly
I know MS specifies that you only need to license one SQL server and can maintain a backup SQL as long as it is not in use, will this be the same when implementing mirroring?
Thank you very much 🙂 This will help make our choices so much better.
absolutely you do not need a second license , that is why it is so cost effective..
The backup server can be up for a certain number of days (about 60 I think but dont quote me) before you have to fail it back
July 28, 2011 at 4:49 am
steveb. (7/28/2011)
ajsnyman (7/28/2011)
steveb. (7/28/2011)
ajsnyman (7/28/2011)
steveb. (7/28/2011)
ajsnyman (7/28/2011)
Each server will have its own SQL instance, but only one SQL instance will have the DB attached.So when the primary server dies, so we can just attach the DB on the backup server's SQL instance.
One problem is, what happens if the primary server comes online and try connecting to the DB while it's attached on the backup SQL instance.
Hope this helps 🙂
I would not recomend this approach as you will have a lot of issues when the primary db crashes and there are open transactions and you will not be able to attach it to the secondary server.
seriously forget this approach and implement mirroring, its what you are trying to do anyway, but it has been fully implmented and tested also it will allow automatic failover for your .net clients which your approach wont
Cool, this is a good argument, that was the biggest fear of our implementation.
What is the performance concern and possible data loss with mirroring?
Perfromance concern is minimal in my experience, if it is a deciding factor then you should look at increasing the perfromance of your database using other methods of tuning. Also the reason there it takes some performance is because it is doing the neccesary work to keep the databases in sync which would be impossible otherwise... there is no such thing as a 'free lunch'..
data loss can be zero if setup correctly
I know MS specifies that you only need to license one SQL server and can maintain a backup SQL as long as it is not in use, will this be the same when implementing mirroring?
Thank you very much 🙂 This will help make our choices so much better.
absolutely you do not need a second license , that is why it is so cost effective..
The backup server can be up for a certain number of days (about 60 I think but dont quote me) before you have to fail it back
Thank you so very much for your help.
Just want to say, I'm a big fan 🙂 You guys are awesome!!
Thanks to Jayanth_Kurup as well.
July 28, 2011 at 6:27 am
Except in servers under extreme load the performance hit caused by mirroring is negligable. Data loss can be zero with syncronous mode.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply