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!!
We 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
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.
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
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 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.
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.
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.
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.
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.
I'm having a problem connecting with a Java application but I CAN connect using my .Net application - the user name and password are the same for both (using the same database on SQL Server Express 2005).
The error I get is: "com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "CORNERS" requested by the login. The login failed." An interesing note - I get the same message if the database is not running.
SQL Server Express 2005 is installed in mixed mode.
Here is my connection string in the .Net appplication: <add key="connectString" value="Server=(local);UID=sa;PWD=myPasswd;Database=CORNERS" />.
Hello:I have 2 MS SQL Servers 2000 Developer edition.Both have same settings and databases. I'd like to have them as mirroredservers - all transaction on one server must be performed on the otherautomatically. More than that I'd like to get possible if something happenedto one server, all transactions will be performed on another one.Please, give me a hint how to do that.Thanks,GB
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?
hi.I've seen ways to delete duplicate rows.Can someone give me some sql to do this?I have a table with varchar table_name_start, varchar column_name,varchar table_name_end;it has rows like this:table1 col1 table2table1 col2 table 3table2 col1 table1I'd lke to delete the rows if they exist with the names swappedaround, i.e. like above since the first and third share a column nameand the table_name_start/end matches the others table_name_end/start,I'd like to delete one and leave the other.I'm scratching my head trying to figure this out.thanks
I currently have 2 mirrored servers and would like to implement a backup solution using an existing TSM server. The first thing that comes to mind is using the TSM client or Litespeed by Quest, but I'd like to know the effects of performing backups on principal and mirrored servers first.
Will using one of these products cause errors or problems should the backup client try to backup a mirrored database? Can anyone make any recommendations on the effects of using TSM client or Litespeed for a mirrored environment?
The following problem occurs when trying to mirror multiple databases using SQL Server 2005:
After mirroring about 40 databases, an error occurred concerning a lack memory availability on the mirror-server. I added the parameter /3GB to the boot.ini file and enlarged virtual memory space.
Now I was able to mirror a total of 59 databases. When trying to add the 60th database on the principal server, it won't get through. I don't get any error message, neither the mirror starts. However the SQL Server Management Studio on the mirror-server doesn€™t seem to respond to any commands given until the mirror is again removed for one or more databases on the principal server. Adding the parameter /PAE to the boot.ini file, or enlarging the virtual memory again didn't bring the solution.
Both principal- and mirror-server are dual processor servers.
The principal is a Windows 2003 Server Standard x64 Edition service Pack 1 and runs:
SQL SERVER 2005: Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 5.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
It has 4GB of memory.
The mirror is a 32-bit Windows 2003 Server Enterprise service Pack 1 and runs:
SQL SERVER 2005: Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
I am in the process of architecting SQL 2005 HA options and of course mirroring is at the top of the list. Does anybody know what the requirements are regarding upgrading mirrored databases, for example, applying hotfixes and service packs? Is it a requirement for the principal and mirror (and witness if applicable) to be at the same build level? Is there any requirement to upgrade one first and then the other as there is with replication topologies?
I had a look in BOL but can't find anything covering this (perhaps I missed it).
I am trying to setup mirrored subcriber in Transactional Replication in SQL 2005. I am not able to do so as it don't work after failover.However I am able to setup mirrored publisher with distribution property "PublisherFailoverPartner" for snapshot and log reader agent profiles and it works even after failover.
However I need to know, do we have something called "Subscriberfailoverproperty" or any other method to set up mirrored subscriber in Transactional Replication in SQL 2005.
Today, I became an involuntary DBA until my company finds someone else. We have an SQL R2 DB that is mirrored that failed dur to the transaction log growing and filling up the disk. As a band aid remedy, I was able to add 20GB to the disk (it is a VM) and then I backed up the Transaction log and did select log_reuse_wait_desc from sys.databases where name = 'mydb' and the result showed that the backup was successful (NOTHING, I think was the result).
After this I was able to resume the mirror and everything has been running fine. What I need to do now is to shrink the Transaction log so that if it starts to grow again, I will get an alert and can avoid today's issue. From what I have read, I can just use DBCC SHRINKFILE [logname] after I do a backup and the changes will be reflected on the mirror as well. I have shrunk the T-log in non-mirrored servers before, just don't know if there are any key differences.
Executing the statement: select * from test2.database.dbo.table
gives the following error:
Server: Msg 7356, Level 16, State 1, Line 1 OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time. OLE DB error trace [Non-interface error: Column 'IsActive' (compile-time ordinal 2) of object '"database"."dbo"."table"' was reported to have changed. The exact nature of the change is unknown].
If i change the statement to
select * from openquery(test2,'select * from database.dbo.table')
It works. I don't really want to have to go and refactor all the SQL though!
I've seen similar posts related to Oracle and SQL2k5 64bit but they don't seem to be relevant to this situation.
Here's the version info:
2000 server (SP3):
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2005 mirrored servers (SP2 3054):
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I've also tried applying the following to the 2005 servers: sp2(3159) sp2(3161) but makes no difference.
I'm having a problem with SQL2005 Database mirroring.
I have an ASP application that loops for a certain amount of interations and in each iteration I create a SQL Connection object and use the failover partner in the connection string. The object then writes a simple record and then the connection is closed and the process starts again.
About half way through my loop I force a failover to the server mentioned as the failover partner in the connection string. At this point my application encounters a SQL Exception error and the application fails.
I read in this article, http://msdn2.microsoft.com/en-us/library/ms366199.aspx, that this is expected and that you shoud request a new connection using the same connection string but this isn't working for me and unfortunately no examples are given anywhere.
I would appreciate any help. Thanks
Luis Bonilla
Here is a sample of my code:
Dim k As Integer = 1 Dim constring As String = "Server=SVR01.XXX.XXX.comInstanceName;Failover Partner=SVR02.XXX.XXX.comInstanceName;" _ & "Database=test;" _ & "Integrated Security=SSPI;"
Do Until k = 60 Using con As New SqlConnection(constring)
Dim cmd As New SqlCommand()
Try con.Open() '<--------This is where the exception occurs Catch ex As SqlException Dim en As String = ex.ErrorCode Dim em As String = ex.Message Dim emm As String = ex.Number Dim enn As Integer = ex.State End Try
Simple question, I hope. I need to add a column to a table of a database that is mirrored. How do I have to do that? Do I need to stop mirroring? Is it sufficient to simply pause mirroring? If I make the change on the principal db, what do I need to do the make the same change on the mirror?
We are planning to change the location of the log files from my production server. We have setup the mirroring also for those databases. I know for the databases without mirroring job i could detach the database, copy the log file to the target location and attach the database with the new logfile location. But for the mirroring databases I don't have any idea how to do that. Could some body guide me thorugh the process on how to do this task? thank you very much for all your help!
I have a mirred database and have snapshot created from the mirroed database. I can do data selecting from the snapshot. But when the pricipal server is down , I can re-active the mirroed database.( I did succesfully upto this). Then I need to restore the database from snapshot and which failing with following error message.
Code Snippet Msg 5123, Level 16, State 1, Line 3 CREATE FILE encountered operating system error 3(error not found) while attempting to open or create the physical file 'E:sql_datadevitst_mirrorlog st_mirror_log.ldf'. Msg 5024, Level 16, State 2, Line 3 No entry found for the primary log file in sysfiles1. Could not rebuild the log. Msg 5028, Level 16, State 2, Line 3 The system could not activate enough of the database to rebuild the log. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.
I am unable to read the mirrored database as it is always in the restoring state. I have used only two servers (principal and mirror no witness server) I tried to create the snapshot of the mirrored database but i get the following errors:
Code Snippet
Msg 1823, Level 16, State 2, Line 1 A database snapshot cannot be created because it failed to start. Msg 5119, Level 16, State 1, Line 1 Cannot make the file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataCRO_MASTER_data_1800.ss" a sparse file. Make sure the file system supports sparse files. How can i get rid of this problem?
I have been struggling to make my classic ASP to call mirrored db successfully for the last few days. I am using a ODBC SQL Native Client. The front end application works perfectly untill the database is switched from principal to mirror server. I am getting the following error.
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][SQL Native Client][SQL Server]Cannot open database "testDb" requested by the login. The login failed.
I will briefly explain what I have done. I hae created a SQL Login testUser and this login has the dbowner rights to testDb. This is being done in Principal server. Till now the application works fine. Once I switch it to failover, the mirror server acts as a principal - this is what I was expecting. On database level it works. But my ASP application doesnot seem to like it. When I closely looked into the security, I found that testUser does exist in the testDb but the real problem is testUser is not associated with testUser Login. It's not possible to change as well.
