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..
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.
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.
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!
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.
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
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?
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 RelatedI need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
Operating system is Windows 2012.
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
Sorry I am a newbie at this SQL thing.
Hi All
By mirroring I mean device mirror, since I do not think that there is a database mirror
We only have disk mirror. I also know that SQLServer 7.0 does not provide Mirror, unmirror or remirror. and for this we have to refer to Windows NT disk mirroring.
I need mirroring in both versions SQL Server 6.5 and 7.0
I want to have mirror on another server. I need this for tonight.
Sincerely.
I hereby let u know the steps that i undertook for performing mirroring
1. Created a database in Principal server named Mirror1 and one table named tblMirror1.( no values inside the table)
2. Repeated the same in Mirror in Witness
3. Selected the Principal server's database -->right click-->tasks-->mirror
4. from the left hand panel selected Options -->recovery model-->full
5. selected Mirroring from left hand panel-->configure secuirty-->entered mirror and witness name--Success
6. When I click on start mirroring I have an error stating
" Alter failed for database mirror1"
"Database mirroring cannot be enabled because Mirror1 database may have bulk logged changes that have not been backed up etc...."
7. followed this one
BACKUP database mirror1 TO DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'
got the answer as
Processed 160 pages for database 'mirror1', file 'Mirror1' on file 2.
Processed 1 pages for database 'mirror1', file 'Mirror1_log' on file 2.
BACKUP DATABASE successfully processed 161 pages in 0.318 seconds (4.147 MB/sec).
According to sources
Run a restore of this backup on your mirror.
This can be done through the GUI or using a T-SQL command.
The database restore must use the NO RECOVERY option, so the database stays in a loading state.
Also the database name on the mirror must be the exact same name as the principal
RESTORE TestMirror FROM DISK='C:Backup TestMirror_FULL.BAK' WITH NORECOVERY
Hence did the below
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
ERROR
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
HENCE MODIFIED AS
use master
go
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
========
Kindly let me know please where am i going wrong
Regards
cmrhema
hello
if i run dbcc dbreindex on a database thats the principal in a mirrored pair, will the mirrored database indexes also be rebuilt?
i am for the first time creating the database mirroring.I am moving the all the usr databases and everything from test server to a new server called test1.And from that new server i am restoring on the another new server called test2 for database mirroring.But in the test server , the transaction logs are backup for every 15 minutes.So in this case is there a need to move all the logs to test1 and then to test2 for mirroring?? or there is no need to move transaction logs to the new servers at all??
my secnario
------------
server1 server2 server3
-------- ------- --------
test test1 (moving everything from test) test2(mirror for test1 )
all the transaction logs are backup in test for every 15 minutes. so in this case , do i need to move the transcation logs from test to test1 and test2
or there is not need to move at all??
thanks,
Can anyone please tell me what mirroring is??
Thanks in ADvance
RKNAIR
I have sql server 2005 on my vista laptop.
When i set up database mirroring, do i have to set up Principle,
mirror and witness on seperate servers as well as seperate instances.
Or can i install 3 seperate instances on the same laptop im using and still use mirroring? I have tried to start mirroring with all three instances on my laptop but i receive an error.
Regards
Rob
Hi All,
I am trying to set up database mirroring and in the initial phase of database restoring im getting some problem.
the scenario is .
1- I create a TestMirror on server1(which will be principal server) and backup full and transaction logs.
2- create TestMirror in server 2( Which is going to be used as mirror) .
3- restore the full backup of "TestMirror" from Principle Sever with NORECOVERY option.
this step results in a message box showing that the restore was successfull...
but the database explorer( management studio) shows the TestMirror in (restoring ...) state. and no more operations can be performed with this Database. ( because the database is in restoring mode).. please help what this issue is?
Thanks In Advance for any suggestions
-- Krishna
hello
i used following query to create endpoint
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
i got the following error msg:
Msg 1498, Level 16, State 2, Line 11
Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online.
any help plzz
thanks in advance
Hello,
I have Microsoft SQL SERVER 2005 installed in my system.
To try out mirroring, I rightclicked on the database went to the tasks, but i found that in Status its written
"This database has not been configured for mirroring"
What shall I do now?
Hi,
I am new to DB mirroring and witness. For the purpose of learning, any one can suggest me like is there any posibility to do the DB mirroring and witness on 2 or 3 VHD Files. Basically, these VHD's are downloaded from microsoft website.
VHD's have W2K3 and SQL2K5 EE .
Please suggest me in this.
Regards,
Mohanraj Jayaraman
Hello Guys,
Can any one help I want SQL to email me when a database fails over I can find alerts for loads of thing to do with Mirroring but not to notify me when a database failover.
Thanks matt
How sql server will mirror big transaction ?
Example
inserting 10000000 rows in 1 transaction (columns big ,text data ...)
will it transfer data in multiple packets (size, location of temp file) to mirror ?
will it transfer data in 1 packet (size , location of temp file ) to mirror ?
Can you control transfer (size , location of temp file) ?
Thanks
Alex
Do you know if the following excerpt from technet is still true? I want to mirror a device over the net to another server. Has anyone tried this with sql server 6.5 or is it still not a feature?
----------------------
PSS ID Number: Q115043
Article last modified on 04-29-1997
4.2 | 4.2
OS/2 WINDOWS
================================================== ====================
----------------------------------------------------------------------
The information in this article applies to:
- Microsoft SQL Server version 4.2 for OS/2
- Microsoft SQL Server version 4.2
----------------------------------------------------------------------
The use of database devices accessed over the network is not a supported
configuration for either the OS/2 or Windows NT versions of Microsoft SQL
Server. All database devices, including mirrors, should be configured to
use local drives. Database dump devices are an exception to this
restriction.
----------------
Thanks.
Gail Wade
Raymond James and Associates
Clearwater, FL
gwade@is.rjf.com
I am testing out Synchronous Mirroring and have a question about failover. It seems the only way I can failover to the Mirror box is to log onto the Principal box and hit the Failover button? Is the whole point of mirroring to be able to handle the Principal not being available? Is there another way?
TIA, ChrisR
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.
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.
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.
Hi,
I have place an question, but in some other place in the same forum.
My apologies for that
I wanted to implement mirroring
I hereby let u know the steps
1. Created a database in Principal server named Mirror1 and one table named tblMirror1.( no values inside the table)
2. Repeated the same in Mirror in Witness
3. Selected the Principal server's database -->right click-->tasks-->mirror
4. from the left hand panel selected Options -->recovery model-->full
5. selected Mirroring from left hand panel-->configure secuirty-->entered mirror and witness name--Success
6. When I click on start mirroring I have an error stating
" Alter failed for database mirror1"
"Database mirroring cannot be enabled because Mirror1 database may have bulk logged changes that have not been backed up etc...."
7. followed this one
BACKUP database mirror1 TO DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'
got the answer as
Processed 160 pages for database 'mirror1', file 'Mirror1' on file 2.
Processed 1 pages for database 'mirror1', file 'Mirror1_log' on file 2.
BACKUP DATABASE successfully processed 161 pages in 0.318 seconds (4.147 MB/sec).
According to sources
Run a restore of this backup on your mirror.
This can be done through the GUI or using a T-SQL command.
The database restore must use the NO RECOVERY option, so the database stays in a loading state.
Also the database name on the mirror must be the exact same name as the principal
RESTORE TestMirror FROM DISK='C:Backup TestMirror_FULL.BAK' WITH NORECOVERY
Hence did the below
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
ERROR
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
HENCE MODIFIED AS
use master
go
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Please let me know what shuld i modify
regards
cmrhema
I think I've read some conflicting advice in BOL. Maybe someone can clarify it for me.
Under "Database Mirroring and Database Snapshots" it says:
"You can take advantage of a mirror database that you are maintaining for availability purposes to offload reporting. To use a mirror database for reporting, you can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot"
To my mind, to enjoy a noteworthy performance gain, that mirror database would need to be on another server.
But then you read under "Database Snapshots":
"Multiple snapshots can exist on a source database and always reside on the same server instance as the database."
So how do you get the snapshot to the mirror?
I have a quick question regarding mirroring failover. If you have mirroring setup with a witness and the principal server goes down, once the principal is back up as the mirror will it automatically synchronize with the new Principal?
The main thing that I wasn't sure of is that if it took a fair amount of time to bring the original principal back up, the new principal will be in production so will all the new transactions be sent to the mirror once it is available?
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
Hi,
We have 2 servers each having a domain (i.e. 2 domains) running
Windows Server 2003
Standard Edition
Service Pack 1
We would like to have one of the servers replicate/mirror the data on to the other but we are getting this error
The error produced from the mirroring part is:
TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'CTSM'. (Microsoft.SqlServer.Smo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://UM_db_live.UMlive.local:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
Thank you.
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....