Several Questions Regarding Database Mirroring.
Jan 31, 2006
Our company is looking to move to a more robust database platform/setup, and it looks like sql 2005 + database mirroring is what we are looking for. First I have some questions about it.
Can a server that is functioning as a mirror for one database be the primary server for a seperate database? Basically think of a triangle of three sql servers, each serving as a primary server for a specific database, and using the next server in the triangle as a mirror for that specific database. To be more specific:
Server 1, Primary Database A, Mirroring Database C
Server 2, Primary Database B, Mirroring Database A
Server 3, Primary Database C, Mirroring Database B
If that is possible, how many witness servers would be needed to accomplish the above topology? On that note, how robust hardware wise should the witness server be?
On a completely seperate note, what method is recommended for interconnecting these servers? Is gigabit ethernet fast enough, or does it require something like fibre channel or infiniband?
Thank You!
View 1 Replies
ADVERTISEMENT
Dec 13, 2006
Scenario: The principal is in LA and the Member is in NY.
1. Where should the witness typically reside LA or NY?
2. Can the witness handle several quoroms or it has to stay within the quorom it is assigned to?
3. LA to NY - what is the recommended bandwidth for this? right now we have a 1MB pipe? We average about 9 Gig of transactions/day
Thank you..
View 1 Replies
View Related
Nov 3, 2006
Following a bunch of tests I've concluded that mirroring works very well, even in sync mode the perf impact is low. Log shipping integration is clean following failover. Excellent stuff, thanks. However there are a few things that would make it a much better sell against competetive solutions:
1. It's my experience that Database Mirroring in HA mode effectively only supports one failure, so if your Witness goes bang you lose auto failover capability. If the mirror then fails you lose database service. This makes robustness requirements for the Witness very important. Are there any plans to allows multiple witnesses with some form of active/passive failover between them? I seems a little over the top to have to consider clustering a witness.
2. It's extremely irritating that we can't create logins on the mirror if the login has the mirror database as their default database, just because the database is in recovery; I mean it not as if the db doesn't exist. Can this be addressed with the security team, perhaps masking the error somehow if the database can't be opened ? Bear in mind the database principal will already exist in the mirror database as it will have been created on the principal server, all we need is the output from sp_help_revlogin from the principal server to match up the sid and password.
3. If number 2 is addressed, it would be possible to build a more elegant and robust sync mechanism using service broker, without having to rely on alerts to then create any missing logins on failover. Are MS looking at building something to address login synchronisation of the failover servers, perhaps using service broker? Seems for a supposed HA solution this is a very untidy missing piece and with some environments which might not be able to rely on using Windows groups (sql logins), it makes rapid failover difficult.
4. It would also be useful, while on the subject of number 3, to provide some mechanism for, at least, auto-checking configuration/msdb differences between servers and reporting these, if not provision for keeping them in sync. I appreciate that not all systems will want exact matches between the partner servers, but at least providing mechanisms to sync them up would help.
5. Any plans for supporting multiple mirrors? Even async? Would make for reporting provision using snapshots much cleaner as the main mirror could be left alone for HA.
thx,
Simon.
View 4 Replies
View Related
Apr 25, 2007
Hi guys,
I have read many articles on the matter and I have probably used up all my printer's ink in doing so, however, some questions still remain.
1) What happens if I have to reboot the mirror.. security update, etc.? Obviously the session is broken during reboot, but would I have to do another backup of the principal and resync everything?
2) I know it is not best practice but at this point I have no choice, however, I wanted to get your guys feedback on having two instances of SQL2005 on my development box. One for the mirroring of the production and the second for development. The two instances would live on their own drive... not partitioned and have adequate memory and space.
What would I have to look forward with this?
3) Lastly, I am still uncertain if mirroring is approved for production. Is it?
Thanks for your help.
View 4 Replies
View Related
Feb 16, 2006
We are planning to update our two node, active/passive clustered system to SQL 2005 from SQL2K. Would we be better off to use database mirroring rather than clustering with failover? I believe mirroring takes 3 systems rather than two (one for each mirror and one for monitoring), but can the monitor be a rather low powered PC?
Our current cluster uses two 4-processor Dells and an external PowerVault RAID array with fiber channel connections. Each server has two NICs, one for general network and one for heartbeat and a fiber channel card to connect to the external PowerVault.
Here are my questions:
1 - If I understand it correctly, mirroring uses just separate servers without the need for the rather expensive and complicated shared fiber channel array that must go between them. Do I still need a shared array or can I just use internal RAID arrays in each box?
2 - Are there any advantages to keeping our current system? Our current system has worked well, but the failover takes a couple of minutes. Is mirroring faster?
3 - Is there still a virtual IP address and network name that everyone connects to?
4 - What are the drawbacks?
5 - Can I run non-mirrored DBs on the same servers or are they really locked together tightly?
6 - How much different in configuration can the two boxes be? With the cluster, I really need duplicates in every way.
7 - Do I need a separate license for the SQL Server on each box in the mirror or is one enough, since they work in lock step?
Thanks for any of your experiences on this.
View 7 Replies
View Related
Mar 22, 2006
You will all have to excuse my ignorance. I'm a developer who also doubles up as a development DBA. I am however not particularly knowedgeable about all the really important DBA stuff.
We've built a small BI solution using SQL Server 2000. Our problem is that our server is getting on in years (5) and doesn't really have enough disk space or grunt. We havce a number of summary cubes that we've optimised quite successfully but our billing line level cubes run to 60 million rows and, well, they're about as quick as a dead ferret. Especially given the stupid queries our data analysts keep running.
We have however proved our point. That this can be done and indeed SQL Server can do it. So we're now looking at some infrastructure spend and some new copies of SQL2005.
But i need some advice. Our user base is climbing through the roof, we originally had 10, now we have closer to 50 and at this rate it'll be a couple of hundred by the end of the year. We're using a plugin called XLCubed to deliver that data into Excel from the Analysis Server.
The OLTP database that sits behind it is fairly robust but we have a number of web based apps (mostly lookup systems) that want to use the nice shiny new accurate tables of data we have created.
So I'm looking at a fairly big server to hold the OLTP DB, this will also serve up live data to our web apps. Its worth pointing out that the source data system is a batch system that processes overnight so we load data from yesterday at 6pm each evening and process our cubes and stuff overnight. Thus the data is a couple of days out of date. Don't laugh they used to use MS Access and got one mangy data set a month so this is a massive leap forward.
I wanted to mirror the DB to another machine but I also want to have a separate Cube Server. I wondered if the cube server could use the mirror to read its data from as opposed to loading the Main Server (the mirror would be an identical box) we would also have a separate box running some of our other systems acting as the witness.
I also wonderd about exporting the Cubes onto file shares for use locally as opposed to via the server which is how they connect now.
We have been using Reporting Services and some of the queries the devs write are not exactly efficient. So I was also planning on clustering a pair of smaller servers into a reporting farm. Could I use another SQL Server to serve data up to them? Could I use a DB snapshot to copy the data required to this server? What are the time / size implications of using a snapshot and replicating it over each night?
Any suggestions for places to read up on this? I've looked at the MS marketing blurb and while its big on buzzwords its light on specifics. Like how it actually works and how you would actually configure it to do some of this and what the implications would be.
Any advice?
many thanks
Steve
View 4 Replies
View Related
May 3, 2008
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
View 7 Replies
View Related
Oct 12, 2015
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
View 5 Replies
View Related
Dec 2, 2014
I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
View 2 Replies
View Related
Jun 12, 2007
When I issue this command:
ALTER DATABASE foo set PARTNER = 'TCP://10.3.3.1:1234'
I get this error message:
The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed.
What does that mean, and how do I fix it?
View 8 Replies
View Related
Apr 2, 2008
I set up a new mirror server. Everything is good except that the Database Mirroring Monitor is not working for one of the databases. In the monitor the principal data is showing up as blank
If I look at dbm_monitor_data on the principal most of the data columns (e.g. Send_queue_size) are null where as they have data for the other databases.
Both servers are SQL Server 9.0.2047 Enterprise Edition.
Any idea what might be going on here?
Thanks in advance.
View 3 Replies
View Related
Oct 12, 2007
Hi All,
Does anybody know the OS requirements for Database Mirroring? I checked BOL and Google but couldn't find any info on that.
Thanks.
View 5 Replies
View Related
Feb 27, 2008
Hi All,
I have read on the web that high protection mode not recommended, except in the event of replacing the existing witness server. But I can't find the reason why anywhere. Can anybody explain? Thanks.
View 2 Replies
View Related
Mar 12, 2008
Hi All,
Couple of questions about database mirroing.
1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?
2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?
Thanks.
View 1 Replies
View Related
Mar 15, 2007
for a database mirroring , which stretergy is good.
Creating with witness server or without it.?
I am firsttime doing this , so i am posting it to the forum..
and also if we are creating the database mirroring with witness server for automatic failover , will the witness server need the same amount of harddisk space like the pricipal or mirror server.??
thanks
View 2 Replies
View Related
Jul 30, 2007
we want to migrate our production server , without taking the database down.so what we did was , we mirrored the databases from production to one of our development servers and we took the prodcution server for uprgrade.
so today we are moving back (mirroring) all the databases from that development server to our new production server.
my question is , when i move
productionserver_old(principal server) - having the script for principal server
development server(mirror server---->principalserver)) - having the script for mirror server , which is now acting as principal server
new production server(mirror server) - having the script for mirror server. will become principal server , once i moved all the databases
now while mirroring between the developement server and the new production server, i have to run the scripts for the mirror server only.
so both the servers are havign the mirror script now...
is that a problem, or any other way i can handle this....
View 12 Replies
View Related
Mar 21, 2008
Hi All,
Does anybody know any good Tutorials for Database Mirroring (Automatic Failover) or Manual Sync with Mirror Server. I tried some sites online, but doesn't have detailed steps..
pls.help as i need to sync data with One of my Mirror using Sql Server 2005.
thanks
View 2 Replies
View Related
Mar 14, 2008
Hi,
I have some questions about database mirroring. we have almost 40 databases in one server. Can I set up the database mirroring for all the databases. Is it going to any affect on performance. we have already setup the mirroring on almost 30 databases, and I need to set up for the rest of 10. please some body could help on this. Thank you.
View 3 Replies
View Related
Jul 7, 2006
Hi,
i'm a novice of sql server and I have a problem.
I have to reply a server in which there are database that are managed with sql server 2000 sp3; what I must make in order to set up the mirroring between the database of the two server so that the data are always aligned ?
thanks for the attention
best regards
alessandro
View 3 Replies
View Related
Apr 18, 2007
I am new user of SQl and preparing the exam database mirroring....can you tell me how i start the mirroirng ..what is my first tep..and is there only one instance
View 3 Replies
View Related
Mar 12, 2008
Hi All,
Couple of questions about database mirroing.
1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?
2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?
Thank you.
View 6 Replies
View Related
Jan 4, 2008
Can we have database mirroring for two databases. Like i have an application where in the db used are two, namely DB1 and DB2, DB1 is used in the connection string and with the help of DB1 the tables and sps of DB2 are handled.
in this case can i go ahead with database mirroring. Please comment and give the idea or link on the same.
View 1 Replies
View Related
Oct 25, 2006
Hi,
Is there a way to automatically mirror/backup a SQL Express database with a SQL Server 2005 licenced system?
They will be on seperate machines on the internet.
I have done manual backup and restores but would like to allow constant updates to allow both databases to be synchronized.
I read that the SQL Server 2005 version has a mirroring facility but I also read that this is not possible in the Express version.
What other (automated) options do I have?
Thanks Andrew
View 1 Replies
View Related
Jan 30, 2002
Hello Everybody ..
Can anyone help me with some information or procedure as to how to mirror database devices in SQL Server 7.0 & SQL 2000 servers ?
SQL Server 6.5 has mirror utility built in the Enterprise manager .
Is there any way to do it on SQL 7.0 and SQL 2000 Servers ?
Any help will be appreciated .
Many thanks
Yatin.
View 1 Replies
View Related
Mar 23, 2007
what are the different testing we can do for the database mirroring with witness and without witness ??
thanks
View 1 Replies
View Related
Jan 24, 2008
Can 32-bit witness Server support 64 -bit Principle and Mirror Server? Please experts in database, i need advise.
View 1 Replies
View Related
Nov 22, 2006
Can anyone offer some advice on how to reduce the size of a databaselog. The database is mirrored on another server using the logs. Thanks
View 5 Replies
View Related
Jan 28, 2008
Hi Can Anybody help me what exectly is database mirroring? I am working in a small scale company. we have SQL server 2005 installed for a software used by our staff. Can any body expalin me what is mirrroring? how can i implements to my Database? What are the Basic Requirements? What are the Steps? Plz.. Thanks in advace who ever help me out for this......
View 1 Replies
View Related
Dec 28, 2006
After reading about it, i'm still confused. Is replication and database mirroring the same? they seem to accomplish the same thing.
View 3 Replies
View Related
Mar 16, 2008
I have setup database mirroring for one big database.But now Transaction log is full and server is down. What should i do? We do bulk-insert in that database.
View 3 Replies
View Related
Feb 7, 2007
Hi
I have 3 server configured with mirroring. When the automatic failover occur, I use alter event generate by Sql Agent to rebuild user account (problem witch
orphan user). My application use multiple database and I set up mirroring to multiple database. A question is the order which database get up after automatic failover. When event occur all database are ready to use, or maybe one database get up and sql agent send me event , secound database get up and sql agent send me event. What is the order?
View 3 Replies
View Related
Feb 26, 2008
I'm not personally a DBA or claim to be a database expert, instead I am a software architect who works closely with database architects and DBA's. I'm presently architecting a system where we will have a smart client used in both Europe and the United States from the same database instance.
The system is to be used for performance testing of devices and then the results will be available to management through the same smart client used by test engineers etc. The first release is operational in the United States today with Poland coming online shortly.
Today we are weighing our options between having cross-geo database mirroring or merely having the database server in the United States with application server level caching implemented in Poland and the United States.
The .net framework's Windows Communication Foundation is used with a netTcpBinding and the service layer is asynchronous with the reliableSession option enabled.
Does anyone have any feedback on whether you would go with cross-geo database mirroring or would you use local application servers with possibly some caching capabilities at that layer? Please also describe the benefits and any risks associated with each also...
Thanks in advance,
Doug Holland
View 8 Replies
View Related
