March 24, 2009 at 1:43 pm
I am looking into DM to increase the high availability of our back end SQL server. We have a 150 GB database and a 50 GB database. More than half of the space on each database is to store images. The size of t-log each day is 3 GB for the large db and 1.5 GB for the small db.
The front end apps is not database mirror aware so I think a manual intervention is required to failover. This eliminates the use of a witness server so only principal server and mirror server are in the picture now with the data flow from principal to mirror. With these large numbers of t-log, is a 1 GB network card fast enough when I set Transaction Safety to full? What other options do I have to increase the flow from principal to mirror?
Regards,
March 24, 2009 at 2:44 pm
Hi,
Your transaction log is 1GB doesn't mean that you are throughput is 1GB on transactions. So I don't think you have to worry about it that much; but look at the performance monitor to see what the network usage is right now. With mirroring it is per transaction ...
So you need to ask number of transactions and current network load before you make your change to DM.
Also what is your front end application developed in? Even with manual failover your front end has to re-direct connections based on the connection string/cache value setting.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 24, 2009 at 3:35 pm
Mohit (3/24/2009)
Also what is your front end application developed in? Even with manual failover your front end has to re-direct connections based on the connection string/cache value setting.
That is something I need to research more. I am trying to setup a lab but there is limited hw for me to build a lab. I can't even get a PC with more than 2 GB of RAM to setup virtual servers!
Question: in case of a failover occurs, can I force the failover on mirror server, break the mirror, make sure principal is offline then rename and change IP of mirror server to match the name and IP of the pricipal server?
thx.
March 24, 2009 at 4:05 pm
htt (3/24/2009)
Question: in case of a failover occurs, can I force the failover on mirror server, break the mirror, make sure principal is offline then rename and change IP of mirror server to match the name and IP of the pricipal server?
I am not understanding 100%; you want to force the failover on mirror server? That is you want the database on Mirror to become principal without having to fail it over from Principal?
Answer is yes; at a small price (or big); but if your principal is not avalaible that is the only option. You have to execute following statement:
ALTER DATABASE DBName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
As for other option instead of going through the trouble of re-naming server and chaning the IP address do the following:
1) Enable SQL Browser service on your mirror server.
2) Setup an TCP/IP aliase on your mirror server so when people try to connect to the principal the mirror responds because of aliasing.
or use DNS Aliasing to repoint to your Mirror server; I wouldn't rename server/ip if you can avoid it. As it will be less headach I think...
Thanks.
Mohit.
Ref: http://sqllearnings.blogspot.com/2009/03/mirroring-faq.html
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 24, 2009 at 4:15 pm
Sorry if it confused you. I said force a failover when there is unexpected problem with the principal server that the mirror server can't see the principal any more. I have seen a server with built in RAID card failed causing the server unbootable after a reboot. In such case I need to force failover on the mirror server, am I correct?
March 24, 2009 at 4:34 pm
Correct and you'll need to use it if you don't have automatic failover :).
However you should not need to break the mirror when you do that if you can recover the partner in short amount of time. If you can't then it will be better to break mirror and re-establish because they will be too far out of sync.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 24, 2009 at 4:37 pm
Thanks a lot!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply