My client wants to provide a redundant server at a remote location. They want to have db access in a worst case "building burns down" scenario. Can I use mirroring to a remote location? Do I want to use mirroring to a remote location? What happens to my mirror set when some knucklehead with a backhoe digs up my high speed cable?
Am I better off, in this case, sticking with log shipping and manually bringing up the stand by server?
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
View 4 Replies View Related
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
Thanks,
I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.
View 1 Replies View RelatedWe have a new server that is 64-bit and I have been approached about using the old 32-bit SQL Server (after a complete format and reinstall) for a mirrored server.
Either that, or buy a new Server with less power that would be used in times of need.
I have two sql 2005 servers. I want to do mirroring. My question is does mirroring supports store procedures, ssis packages, user log in ids also? I know mirroring supports database level. In this case, mirroring should supports sp and indexes also. Am I right?
View 3 Replies View RelatedHi all,
I feel happy to post my first thread at dbforums.com, and I think it's the community of DB professionals.
my question is:
I have two servers in my organization, one of them is running an SQL server database. Is there any way to make the second server a mirror of the first one and to make it runs automatically as the main server when the first server is down?
Thanks for replying soon.
Haytham
Hi folks,
I am creating an external database for a legacy application and one of the requirements is that every table has to be declared twice in the database:
TABLE_READ and TABLE_WRITE.
Now the idea is that as soon as a change is made to TABLE_WRITE, these
changes should be reflected in TABLE_READ.
Now there are dozens of these tables, and I dont want to have to go
through and write AFTER UPDATE/INSTERT/DELETE triggers for all of
them. Is there an easier way?
- can you use replication within the same database instance (ie, replicating tables instead of instances)
- some kind of table mirroring within a single database?
- is there a good solution using tsql/stored procedures?
Thanks in advance,
Alex
Hello All!
I have a very simple question.
Im studying for 40-431 and just finished reading about Log Shipping.
It says in the book that when using Log Shipping you cannot run any other log backups because that would affect the LSNs. Can one do log backups with mirroring - principle and mirror?
Because mirroring uses log to keep the mirror up-to-date and synchs on LSN.
Thank you
Hi,
I've got a problem with the DB mirroring in SQL 2005.
Configuration:
Server 1 Name: ABC001 NIC#1: IP 10.1.1.1 no DNS Server, no Gateway NIC#2: IP 192.1.1.11
Server 2 Name: ABC002 NIC#1: IP 10.1.1.2 no DNS Server, no Gateway NIC#2: IP 192.1.1.12
NIC#1 is an cross-over between the 2 server, NIC#2 is related to the local network.
I cannot setup the mirror with the built-in tool nor by hand.
With the tool I receive the message:
"
One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroring again.
The syntax for a fully-qualified TCP address is:
TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>
"
By hand with (Endpoints are ready, added by the tool):
-- Specify the partner from the principal server
ALTER DATABASE [MyDB] SET PARTNER =
N'TCP://ABC002:5022';
also
ALTER DATABASE [MyDB] SET PARTNER =
N'TCP://10.1.1.1:5022';
makes a problem like "other server cannot be reached"
I can ping the other server and use windows sharing. I have create an host entry for ABC002 to use the NIC#1 alltime.
Has anyone an idea how to resolve this?
Thanks!
Hi,
Trying to build an replication and subscription beteween db in SQL 2000. The db holds our business system (Dynamics NAV, earlier Navision) and several of the tables include $, spaces and even :
We can take some of this away when creating a new db or make scripts for some actions but I wonder if the mirroring in 2005 could handle this?
Thanks,
Mikael
Im pretty new to DBA world
We have a SQL2005 Standard setup with mirror and witness
I create a Database in the Principle, create a SQLLogon account and give it permission to the database. All works.
I then fail the databse over to SQL2 and the database is there, it has the SQLAccount I create at the database level, but a logon does not work. I notice there is not login account at the database level and If I attempt to create one, I am told there is one already. I try to assign permission to that account for the database and it again replys that there is already on.
Is this refered to as an orphaned logon?
I was a post on Moving logins from on server to another, is that what I must do?
THank you
I have a question and excuse me if I look dumb ... but I can't MIRROR MASTER or other dBs. If I lose the server then (primary) ... will the db still be open? do I not require some of these non-mirrorable database to run?
View 3 Replies View RelatedHello,
I'm trying to setup database mirroring between 3 servers and the details are as follows:
1. All 3 machines reside in the same domain.
2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.
3. All the 3 machines are having their individual service domain accounts.
4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.
5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.
6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.
7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.
8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.
9. On Server 2 I'm executing this statement
ALTER DATABASE <dbname>
SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'
10. On Server 1 I'm executing this statement
ALTER DATABASE <dbname>
SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'
ALTER DATABASE <dbname>
SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'
On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."
I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.
Thanks
Hope this is the right thread,
When you backup with Mirror?
Is that concurrent to those devices and if so, what happens if one fails does the entire backup fail?
Cheers,
Casey
When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
On the witness server the following sql
select * from sys.database_mirroring_witnesses
returns 1 row with the previous mirroring information.
How do I remove old information from witness server? Thanks.
I've setup sql mirroring which works fine. my problem is that when the mirror takes over from the partner that means the IP address of the SQL server has changed which is coded into the web.config file.
How can I get my web.conig file to recognise 2 sql servers that are being mirrored so it can see whichever one is the principal.
Currently, when the mirror takes over my web application does not work as the IP address of the sql server has changed.
Does anyone know the fix for this?
thnaks
I have a user which has the currect rights to do what he needs but others with the same permissions (from what I can tell) can not do somethings. It looks like I have 2 options
a.) find a way to mirror the working one to 30 others ids
or
b.) find some tool that will do a detailed comparision of the two ids
Any suggestions/tools(free)/scripts that would work?
Thanks
Hi i have tried a search on this topic but didn't find much.
I have my fiest sql db live on my we server and all works fine, more luck than skill i think.
But now i wory about backing up my data i have tried various tools backing up the db every 5 minutes and then sending it too another server via ftp this tool works fine but when we have a few people on and the db is accessed frequently it can't back it up i assume this is to do with the backup tool needing an exclusive lock on it.
What is the difference between Replication and data mirror both of these seem to be talked about alot and is there a simple guide somewhere on how to set these up.
Or if i am coming at this from totally the wrong angle then please let me know
Thanxs
All,
I have created an asynchronous database mirror and a snapshot of the mirrored database similar to what is described in the following article:
http://msdn2.microsoft.com/en-US/library/ms175511.aspx
The article says that once you create the mirror that "At this point, new client connections can be programmatically directed to the latest snapshot."
My question is, how can you programmatically direct connections to the latest snapshot?
Does anyone have a suggestion, or know what the article is getting at? We will use SQL Server Reporting services to query off of the database snapshot.
Thank you,
Racer-D
Does any one have any recommendations concerning replication when it comes to hardware failure.
I have many sites with one SQL Server in each - i'm contemplating the installation of hot spare servers in some locations - should a server die - I can put the hot spare into production fairly quickly, mainly because the DATA would Already be on the server - in other words a restore would not be needed thus lessening the time to get the spare server running etc etc.
I'm exploring a third party tool called "Double-take" - byte level replication
But then i read about SQL2005 (which we have not yet implemeted) that may have a built in SQL mirror possibility - (i might need primary server - a Secondary - and a "witness" server which allows automatic failover)
Any one's advice would be graetly appreciated.
Gibson767
Hi ALL,
I have a problem with merge replication and mirror. I have
Principal server: B is subscriber of A.
Mirror server: D is subscriber of C.
F is distributor and also witness.
D is mirror of B.
C is mirror of A.
I also set parameter PublisherFailoverPartner for Merge agent and log reader agent.
1. Merge replication between A and B is ok
2. Mirror between D and B is ok
Mirror between C and A is ok
3. When i failove, merge replication between D and C does not work. The message is shown
The merge process could not connect to the Publisher 'SGIMIS-2:Mis'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368)
Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)
Get help: http://help/21036
Thanks for your supports. You can email me via buihuynhmai@yahoo.com directly.
Hello,
We currently have one instance of SQL2k5 SP1. We have a couple of publications, and 30 subscribers, on the instance and are considering going to either a cluster environment or db mirroring. Currently our instance seems to be busy and I am wondering if clustering really gives it a performance boost. What are your thoughts/suggestions on going to a cluster environment versus just db mirroring? Can mirroring be used for real-time failover as we need to add that as well? Thanks in advance.
John
say i perform a mirrored backup:
backup database dbname to disk='c:a_1.bak', disk='c:a_2.bak'
mirror to disk='c:_1.bak',disk='c:_2.bak'
with format
anyone can tell me what's the different between a_1.bak and a_2.bak?
thanks
I have an access database, I am new to the SQL SERVER scene. How do I mirror the database I have in Access with out it being interrupted from its functions?
--David
We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:
- Doing Principal and Mirror (no witness)
- Using Management Studio
- Both servers are SQL Server 2005 SP1 Standard Edition
- Same domain account, same user id
- We can telnet to both and connect to both from Stuio.
We were originally getting error 1418, but now we're getting:
"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"
This is what the log file looks like:
------------------------------
2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.
2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.
2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.
2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.
2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.
2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].
2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.
2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.
2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].
2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.
2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.
2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.
2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.
2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.
We have an application running with a principal, mirror, and witness. If we bring the principal server down, it looks like failover occurs successfully, but the application is not able to access the database. If we bring the server back (so it is now functioning as the mirror), the application is now able to connect to the database. Why isn€™t the application able to connect without the mirror up? Any suggestions?
All,
I've got a medium sized database in a mirror configuration with witness. The database size is about 300gb and I would to reindex all of the tables in the database. My process would go something like this:
1) Backup principal
2) Break the mirror
3) Set the principal database to simple recovery mode
4) Perform the reindexing
5) Backup the principal and transfer that backup to the mirror
6) Restore the backup
7) Re-establish the mirror
Does anyone see any issues with the process itself?
Regards,
Ian
I'm trying to setup a SQL 2005 SP2 Mirror in between two mashines with no witness server.
I have successfully established the following:
1. Backup The Database on Principal Server: OK!
2. Copy the backup over to the mirror server: OK!
3. Restore the Database on Mirror Server: OK!
4. Establish Endpoint on Principal Server: Encyrption Disabled OK! TCP - PORT 5022
4.1 Test Script: Checks fine (values match)
SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id
5. Establish Endpoint on Mirror Server: Encyrption Disabled OK! TCP - PORT 5022
5.1 Test Script: Checks fine (values match) (same script as above)
6. Specify Principal server from Mirror server: OK! Connecting with IP
7. Specify Mirror server from Principal server: OK! Connecting with IP
8. View Mirror Information:
8.1 Test Script: Shows everything in NULL
SELECT mirroring_state_desc, mirroring_partner_name, mirroring_witness_name, mirroring_witness_state_desc, mirroring_role_desc, mirroring_safety_level_desc
FROM sys.database_mirroring
Mirror endpoints are still in red even though they initialized correctly.
Mirror monitor shows no mirrors are up.
SQL Logs shows: The Database Mirroring protocal transport is now listening for connections.
Any help would be appreciated.
Are there any good resources for data mirror in SQL 2005 SP1?
I tried to search on microsoft.com, there is an article, but it is outdated, it only described data mirror in SQL 2005 RTM.
appreciate any links or input.
Owen
I would like to replicate a single view to a table that is stored on another db server (connected as linked server object).
Is there a way to imitate the behavior of a trigger (insert, delete, update) for a view?
I could assign the triggers to the table that provides the primary key.
So I could handle insert, delete events.
But what about updates that affect row in other tables that are used in this view?
Code Snippet
CREATE TRIGGER mirror_tableA_insert
ON [TESTDB].[dbo].[tableA]
FOR INSERT
AS
BEGIN
set nocount on
SET XACT_ABORT ON
set REMOTE_PROC_TRANSACTIONS off
INSERT INTO OPENQUERY(TESTLINKED, 'SELECT * FROM tableA')
SELECT *
FROM [TESTDB].[dbo].[myView] orig
INNER JOIN inserted i
ON i.prim = orig.prim
END
Thanks in advance for any hints!
Marcus
Hi folks. I'm trying to clear up some licensing confusion I"ve come across. First, if I use MIRROR on SS SE I am limited to a single REDO thread and Sycn mode. I see that on the Web. Will the singel REDO thread hurt the ability for the Secondary to keep up? Any one done any tests?
My confusion comes from a document that came across my desk a long time ago that showed that if you use MIRROR on SQL Server SE you are limited to 4 processors for the entire cluster. So if you wanted a balanced failover you would have to have two 2-ways in essence. Is this true? Is it not longer true? I can't seem to find any mention of this....did someone give me bad advice? Help! Thanks.
I have two questions here.
Question 1:
If I am using Database Mirroring for my production database in SQL Serever 2005, can I do mirroring on the mirror database?
Principal Database -- Mirror -- Mirror A -- Mirror -- Mirror B
Can this be done?
Question 2:
Can I do 2 mirroring on a single principal database? If can, which mirror database it will goes when the failed over happen? can the other mirror switch to mirror the failed over database?
Principal Database -- Mirror -- Mirror A
-- Mirror -- Mirror B
Please advise. Thanks