We have a mirrored db setup, with a third server that needs to be linked to this mirrored setup as a linked server. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks
View 1 RepliesWe have a mirror setup, with a third server that needs to be linked to this mirrored setup. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks
View 1 Replies View Related
Hi there!
I use different udls for a website which is partly asp pages and partly aspx pages.
Every database is mirrored, so each udl indicates a "failover partner". However we decided recently that one of the database would not be mirrored anymore. For this database we still use in the udl a "failover partner" to redirect the connection to the second database in case of a failover. For the asp pages the failover works fine even if the database is not mirrored, but it doesn't work for the aspx pages.
It would be great if anyone had an idea why the behaviour is not the same for the aspx pages, and if we can still use a "failover partner" in udls even if the database is not mirrored.. if not, what should we use to connect to another database?
Any help is welcome!
idel
We have a Prinicipal, a Mirror and a Witness server. We have automatic failover configured between the Prinicipal and Mirrored server. When we stop MMSQL service on Prinicipal, not all the databases failover to the Mirrored instance.
Any suggestions would be welcomed as we have a tight deadline to get this in Production.
Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance?
Say I have a mirrored database on two servers: PRIMARY and SECONDARY.
I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.
This is what I'm using:
EXEC master.dbo.sp_addlinkedserver
@server = N'MIRROR',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
select count (*) from mirror.pubs.dbo.authors
and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
As far as I can tell, it doesn't try to contact SECONDARY at all. It seems like SQL Server is ignoring the FailoverPartner attribute.
If I switch PRIMARY and SECONDARY in the connection string (ie @provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.
Any ideas?
Piers.
Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance?
Say I have a mirrored database on two servers: PRIMARY and SECONDARY.
I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.
This is what I'm using:
EXEC master.dbo.sp_addlinkedserver
@server = N'MIRROR',
@srvproduct=N'',
@provider=N'SQLNCLI',
@provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'
select count (*) from mirror.pubs.dbo.authors
and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
As far as I can tell, it doesn't try to contact SECONDARY at all. If I switch PRIMARY and SECONDARY in the connection string (ie @provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.
Any ideas?
Piers.
Hello,
I am a C++/C# developer my SQL skills are very limited.
I have a database set up for mirroring and I would like to get an e-mail notification whenever an Automatic Failover occurs. Can anyone show me how to do this using SQLServer 2005? Please provide T-SQL script sample if possible!
Thanks,
-Agustin
Hi all,
The following query that uses a linked server is giving me the error message below after I initiate a failover (ALTER DATABASE Northwind SET PARTNER FAILOVER). I have SQL Server 2005 SP2. I think that without the service pack there is another error too.
The query is run from a database other than northwind of course.
select * from DualLink.northwind.dbo.Test1
Please note that:
without a failover it works perfectly
it always work if I try to run it a second time - only the first time it fails.
it fails the first time for each of the open connections. A new connection that was open after the failover will work fine.
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
The linked server is defined as
EXEC master.
dbo.sp_addlinkedserver
@server = N'DualLink',
@srvproduct=N'',
@provider=N'SQLOLEDB',
@catalog=N'northwind',
@provstr=N'Server=(local);FailoverPartner=MyIPAddr;'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DualLink',
@useself = false,
@locallogin = 'sa',
@rmtuser = 'sa',
@rmtpassword = 'MyPwd'
Thanks,
Avi
it is possible to create Linked server with Failover partner option. I can query when primary server and getting the error when I set the DB Fail over. I have tried with following script and also gone through different sources, but failed. Please see the script and error below.
EXEC master.dbo.sp_addlinkedserver
@server = N'MIRRORLink',
@srvproduct=N'',
@provider=N'SQLOLEDB',
@provstr=N'Server=primary;FailoverPartner=mirror;network=dbmssocn;',
[code].....
We are using SQL Server 2005 (9.0.3054 on Windows 2003 RC2 SP2 with clustering two instances. We are running an instance on both boxes.
I have 2 linked servers to the same server on the first box (SQL1C) that use different SQL Logins to connect to the same box but to different databases. One uses the name of the server and one uses the name of the server with a 2 at the end of it. (servername and servername2). The second linked server was added recently and is used very frequently by the new application. The other thing is there is a server with the name servername2. They are SQL boxes but I am not sure what version or what they are running on. If it turns out to be important, I will ask.
Here's what is happening:
When we fail over to the second box, up until recently, we had no problems. All linked servers worked well. But since we have added this new linked server, when we fail over, the linked server no longer works and we get an error like from the Cold Fusion application:
[Macromedia][SQLServer JDBC Driver][SQLServer]TCP Provider: An existing connection was forcibly closed by the remote host.
I got a similar error in EMS, but did not copy it to save it. Since this is a production server, I can't really just try things.
We checked to make sure all IPs from box1 and box2 are allowed access to the server and they are not using IP blocking for inside the university domain.
There are no errors in the SQL Logs. I checked the application logs and found no errors for the linked server. The only error I could find and I didn't think it was related but I will include it was this:
Closed event notification conversation endpoint with handle '{4F54167A-F3D0-DC11-97FE-000E0CB2D7CA}', due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
Any ideas? One of our pplications goes down when the cluster fails over now. Help PLEASE!
Thanks,
Linda
1. Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. after fail over done to 2nd secondary node what mode in production(readonly or read write).
4. how to rollback to production primary ,will change data in secondary will get updated in primary.
So I have 2 servers S1 & S2.
Database Group 1 = L1 with Primary S1 and Secondary S2
Database Group 2 = L2 with Primary S2 and Secondary S1
For 99% of the time the 2 groups of databases are not related. For the 1 procedure that does move data from L1 to L2 something like
Update L2.DB.Owner.Table
set flag = 1
Whare a = 0
On S1 I have a linkedServer with connection to L2.
If I have a failover I cannot have L2 on S2 as they are essentially the same server.
How to I use the 2 groups hand in hand.
SQL Configuration: Two 2003 Servers (OS Std ver) with SQL 2005 (Std ver) configured for db mirroring. (The servers aren't clustered.)
Web.config configuration string is using "ServerA".
If we fail Server A, then Server B will change roles to "primary" in about 20 seconds (we have confirmed this via SQL Mgt Console). BUT...our web app is still pointing to Server A and doesn't seem to know there is a fail over. SO - how can I make the web app aware of the failure?
Server A and Server B aren't in a cluster - and I understand a cluster's virtual server would be referenced in the connection string. Is there a way to make the web app automatically switch to Server B (without a cluster configuration)?
From what I understand, the cluster configuration would require Ent Ed. of OS and SQL and a big fat check!!
Thanks.
-Kevin
I did setup a Mirrored Database. Connecting from it using ADO.NET works well. It goes to the Mirror if the Principal fails.
But ADODB does not work. I get the error following error:
80004005 Invalid connection string attribute
When trying to connect to the DB in case the principal failed and the mirror is active. (MyProductiveDB is in failover state)
What do I do wrong?
Here is the code:
ADOConn = New ADODB.Connection
ADOConn.Open(CS)
CS is my Connections-String:
"Provider=SQLNCLI.1;Data Source=MyProductiveDB;Failover Partner=MyMirror;Initial Catalog=MyCat;Persist Security Info=True;User ID=MyUser;Password=xxxxxx;Pooling=True;Connect Timeout=5;Application Name=MyApplic"
Remark: When I try to add "Network Library=dbmssocn" to the connection String, I get the same error, even if the Principal is active.
Your help is very much appreciated.
Beat
The database in Mirrored server is always in the state of restoring only.
the database in mirrored server is restored with norecovery
then we started mirroring.
the status of mirroring is successfull and is synchronized.
but the Mirrored server is always in the state of restoting
not allowing use to open the database.
could any one know why this problem is comming and give some solution for this problem so that the mirrored database can be accessible opened and queryed to really verfiy that the changes made in principal database is mirrored to Mirrored database.
I need some clarification about adding file in to mirrored dataabse in primary server without downtime and breaking the mirror server.
In our environment we are using monutdisks in both the servers. in primary for ex we have F drive for data files under mount disk 3 in mirror server also we have same drive but in mount drive2.
As per my knowledge if it is same drives we can add the ndf files in the primary that will reflect on mirror. but in current situation i am confusing about mount points with different names.
I have two 64bit SQL Server Std servers that will be principal and mirror. I also need to stand up a witness. I already understand that the witness can be any version of SQL Server, but can the witness server be 32bit given the principal and mirror are 64bit?
We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.
However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.
The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.
Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.
The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.
Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.
Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.
We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?
Are we missing a service pack on the client?
Thanks,
Kimball
1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. After fail over done to 2nd secondary node what mode in production(readonly or read write).
4. How to rollback to production primary ,will change data in secondary will get updated in primary.
Hi,
We are migrating all dbs to SQL2005 one by one and now planning for a database mirroring. I would like to know the following:
1. My database(one main production) size on the primary is around 1TB. So having said that we are goin to have a db mirroring, do i need to have the same hardware requirements on the mirror also?? do i need 1TB space on the mirror server also? is there anyway it can use the same hardware that primary is using?
2. For the high availability configuration in mirroring, we are prefering to have a witness server too. Does the mirror server need to have same h/w configuration as the primary(64 bit, AWE, etc). Can the witness be a 32bit, 4 Gigs RAM?
Could anyone please answer the above and that will be very helpful. This is a bit urgent.
thks,
Mani
Every once in a while a scheduled restore of a production database backup to a development server will fail with the following error.
RESTORE cannot operate on database 'XXX' because it is configured for database mirroring or has joined an availability group
While it is true the production database is involved in database mirroring, the development server does not have database mirroring enabled. This error tells me something within the backup is telling the development server the database is configured for database mirroring.
However the perplexing part for me is that we only receive this error maybe 5% of the time, if that, and only on a couple of our databases. We have numerous other restores of mirrored production databases to development servers that have never produced this error. So my question is what is causing this error to occur, and why is it not happening all of the time? We get around this error by deleting the DEV database and re-running the restore job.
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
   @server = N'Machine123Instance456',
   @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'Â Â
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
I have to figure out a way to handle a failover in SQL Server 2005. I was reading a few articles about it.
1. Do I need to do anything in application layer to let a failover occur in SQL Server 2005
2. I have a web site that connects to a production server, the connection string is defined in web.config. If I implement Mirroring in SQL Server 2005, and when failover occurs, shouldn’t I change the connection string to point the application to the new server?
I have to figure out a way to handle a failover in SQL Server 2005. I was reading a few articles about it.
1. Do I need to do anything in application layer to let a failover occur in SQL Server 2005
2. I have a web site that connects to a production server, the connection string is defined in web.config. If I implement Mirroring in SQL Server 2005, and when failover occurs, shouldn€™t I change the connection string to point the application to the new server?
Hi all,
We are the studying the possibilty of implementing SQL - Clustering for
Failover server.
Are there any third party tools readily available.
Any suggestion or checklist ??
Any suggestions or comments will be appreciated.
Thanks and have a great day.
I'm looking at a setup where they have server1 and server2 in a mirroring relationship with automatic failover.Â
Server1 is the principalThey are using transactional replication to replicate asingle databse to server3 is AWS.Distribution database is on Server1All Agents (log reader, snapshot, distributor) run on Server1Server2 has not been set up for replication...My understanding is that in this set up you would normallly place the distribution database on a separate server and enable publication on the mirror, Server2.
What happens if they failover? Replication would stop, and presumably records added while the mirror is the active database would not be marked for replication?How would they recover? Failback and reinintialize
We have 2 nodes window Server 2012 R2 and SQL Server 2012 Enterprise Version cluster setup. We can switch roles and Node to one node to another and revert back to previous node with out any issues. But we are facing when one Node is restarted. We could not restart that Node in cluster Service start in Failover cluster Manager. Error Details is displayed as below inside double code."Cluster node NODE1 could not to join the cluster because it failed to communicate over the network with any other node in the cluster. Verify the network connectivity and configuration of any network firewalls."
I checked windows firewall. windows firewall is all of in Node1, Node2, SAN and DC.I have disabled and enabled the Internal and private network of Node 1. I have validated the cluster. it is showing no error though.
Node1:
Public IP: 10.10.0.11
SubNet Mask:255.255.255.0
Default Getway: 10.10.0.1
Prefered DNS: 10.10.0.10 (Ip of DNS)
[code]....
Private Network: Not configured.pinging to each other ip is successful from one node to another.
Greetings,
Ever since our sql2005 A/P failover cluster failed over to the 'B-passive node', I have a failing SSIS scheduled job with a 'missing component' error. While verifying services in computer management and sql surface area config, It appears that the SSIS service is not available because its not listed therefore cant be started. I have examined both nodes and the primary "A-active node' shows the SSIS service but the passive node unlisted. The server has failed over before during normal scheduled updates and sevice pack installs however this is the first occation we have not failed back to the primary node for normal operation. Any ideas or clues would be appreciated.
Hi,
Here is my testing environment:
Database server: SQL Server 2005 Developer Edition with SP1
Application: An application developed by Visual Studio 2005 using C# (.Net framework 2.0) and ADO .Net 2.0.
Principal server: computerA
Mirror server: computerB
Witness server: computerC
Mirroring mode: High availability with auto failover
Connection String:
Data Source=computerA;Failover Partner=computerB;Initial Catalog=test_mirroring;Persist Security Info=True;User ID=sa;
Code used for failover:
while (true)
{
thisDataSet = new DataSet();
try
{
thisConnection = new SqlConnection(connectionString);
thisAdapter = new SqlDataAdapter(SQL, thisConnection);
thisAdapter.Fill(thisDataSet, name);
break;
}
catch (SqlException e)
{
thisConnection = null;
thisAdapter = null;
thisDataSet = null;
}
}
Test Case:
(Part A) At the beginning of the test, computerA was in principal role. I started my testing application and connected to computerA without any problem. Then I disconnected the connection of computerA to the network by unplugging the network cable of computerA. The failover of database from computerA to computerB was carried out without problem. computerB was in principal role at that time. The application was pending for about 45 seconds and running again without problem. Then I re-connected computerA to network and it became the mirror server. computerB was still in principal role. Up to this point, all works fine, but the problem was coming next. (Part B) I disconnected computerB from network, database failover occurred, computerA became principal again. But my application cannot switch the database connection to computerA and then kept pending. Then, I re-connected computerB to network. It was surprised that the application switched the database connection to computerA successfully at that point.
My questions:
1. I think Part B is abnormal, isn€™t it? The application should be able to failover from computerB to computerA, because it works fine when failover from computerA to computerB.
2. Is there anything wrong in my code leads to the abnormal behavior of the application in Part B.
3. How can I achieve Part B? Any suggestion or idea?
Thanks for your attention.
I have 2 Windows 2003 r2 servers with a SQL failover cluster installed across the 2 node. Generally I have been running off node1 with node2 as the failover partner. I have purchased the correct licensing for noe1 and from the following I do not need to purchase a license for node2:
Failover clustering is a process where the operating system and SQL Server 2005 work together to provide availability in the event of an application failure, hardware failure, or operating-system error. Failover clustering provides hardware redundancy through a configuration in which critical resources are transferred automatically from a failing machine to an equally configured server to help ensure continuity of service.
When doing failover support, a server is designated as the passive server. The purpose of the passive server is to absorb the data and information held in another server that fails. A passive server does not need a license if the number of processors in the passive server is equal to or less than the number of processors in the active server. The passive server can take the duties of the active server for 30 days. Afterwards, it must be licensed accordingly
http://www.microsoft.com/sql/howtobuy/activepassive.mspx
My questions are:
1) If I switch my cluster instace to point to node2 for testing node2 in case of a failover, would I need to purchase another licence ? This means I am not doing the repointing because of an 'event of an application failure, hardware failure, or operating-system error' ? Can you please provide information from MS ?
2) How are SQL Licences bound to the windows install in a SQL Server failover cluster configuration ? For instance if node1 legitimately fails due to an 'event of an application failure, hardware failure, or operating-system error' and you failover to node2. Then you think, stuff (forget about) node1 as I have 1 set of licences for SQL Enterprise already and therefore can run with a legitimate licence on node2. Can you please clarify ?
3) I have heard that only one server needs a license in a failover cluster. Then how does this fit in with any of the questions from above:
'The passive server can take the duties of the active server for 30 days. Afterwards, it must be licensed accordingly' ?
In the event of a legitimate failure, node2 would be running off the license purchased. So why are MS restricting the use of it for 30 days ?
We have sql server 2005 installed locally on 2 servers and want to set up failover clustering. Do we need to uninstall what we have then install as failover cluster? Thanks.