I am trying to set up a SQL mirroring senario - I have some questions
Do I need to purchase an extra SQL Server license for the SQL server instance on the Mirror? what about the Witness does that also need a separate license/
Can the witness be a virtual server on the Mirror box itself? Do I still require a license if the witness is on a virtual server?
I am working on a proof of concept to take to our Technical Board.
This POC is concearned with SQL server 2005 Mirroring for High Availability and Failover in a Highly Critical business sector.
The problem is that we use custom web pages to give the Operations Center an overview of the status of the system and that there are so many metrics available (not may are well documented). I have looked at the following
sys.database_mirroring_witnesses
is_suspended to show the state of the Sync
database_name to return the name of the mirrored database
safety_level_desc to show that the Safety level is at Full
Principal_server_name to show the name of the Principal server
Mirror_server_name to Show the Name of the Mirror Server.
I am at a loss as to what is the best approach as I do not want to display too much Information and confuse.
The Servers are locked down to remove any permissions not required so I have to create a SQL account with only acces to the required sys views. one issue is that the following SQL only works on the Principal
Code SnippetSelect * from sys.database_mirroring where mirroring_guid is not null
Hi, im trying to mirror a database with SQL server 2005. I have completed all the setup for this, including attacthing a copy of the principal database and all the other settings. When I completed the Mirroring wizard, it prompted me to start mirroring and when I accepted, it gave to me the following error:
TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'CTM'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476 ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://DBNAME.domain1.local:5022" can not 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)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
I have sql server 2005 evaluation edition. And This version doesn't provides Mirroring option.Even after installation of service Pack1. Is there any other edition (Freeware or Evaluation) which provides the Database Mirroring.
Hello, My question is simply: according to BOL, a DB snapshot taken from a Mirror is read-only. Say I wanted a temporary (disposable) read-write DB created from the snapshot. Is there a way to generate a writable temp DB (that's not tempdb) from the snapshot? Thanks,AK
I am developing an enterprise class solution using SQL Server 2005 and MS .NET v2 and am tying determine if SQL Server 2005 (which edition and if so how) would be adequate for my proposed solution. Any feedback, tips, comments would be greatly appreciated.
As a background the solution I am developing will be web services based and used by multiple offices around the globe by over 500 users. I have already developed a prototype using a single SQL Server 2005 instance but as this solution is going to be used by offices around the world I want to have an IIS Server and SQL Server 2005 server instance in each office with "links" back to the primary SQL Server 2005 cluster in Australia.
One of my thoughts was to set up replication between the offices that would happen at midnight remote office local time and then set up triggers to update the primary cluster when assoociated data was changed on the remote sites or on the primary cluster. Does anyone know or can anyone suggest alternatives to this strategy?
I effectively need some sort of inter site caching functionality with store and foreward capabilities ...
I get a general question about the feacture "Database Mirroring". Is it support by SQL Server Standard. Edition without SP1?? Of i still need to upgrade the server from SQL Server 2005 to SQL Server 2005 with SP1?
Another question: are there any problem if the one machine(server) has installed SQL server 2005 Standard edition and the other with "Developer Editon".
I hope some one can answer my question. Thanks anyway!
Hi, we have setup synchronous mirroring with witness server.We ran into problem when we stoped the SQLService of Primary Server. Failover Occured and Witness Server made Mirror Server Primary.
The User assosciated with the Database become Orphaned and no one is able to connect to the Database . We recove login failed error for all login for mirrored database. We have to manually run the below comand and reset the password for each login.
1.To Detect Orphaned Users sp_change_users_login @Action='Report'; 2.To Resolve an Orphaned User sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>'; GO
Please let me know why sql server mirroring show such strange behavior. If we manually failove every thing work fine.
I am trying to enable database mirroring for 100 database. It goes error free till 59 databases (some times 60 databases) with the status (principal, synchronized) on principal. on the 60th or 61st database it gave the status (principal, disconnected). Also mirror starts acting abnormal. connection to mirror starts to give connection timeout and it is not enabling database mirroring on any more databases. I have SQL SERVER 2005 Enterprise with SP1 on the servers. witness is not included yet.
this are my test servers... i have more than 500 databases on my production servers.
principal and mirror both are using port 5022 for ENDPOINT communication.
I have a download "SQL Server 2005 Evalution Copy". I installed it to 3 machine, 1 - XP Professional (Witness) and 2 - Windows Server 2000 (Principle and Mirror) . And I want to try Database mirroring.
However, after restore the Log into mirror server I try to execute the following statement in Principle Server:
ALTER DATABASE TestDB SET PARTNER=N'TCP://[10.0.200.2]:5022' GO
The above 10.0.200.2 is the mirror server IP address.
it show error message as:
Msg 1416, Level 16, State 31, Line 1 Database "SCMPGroup" is not configured for database mirroring.
In Miorror Server
I execute the statement :
ALTER DATABASE TestDB SET PARTNER=N'TCP://[10.0.200.1]:5022' The above 10.0.200.1 is the principle server IP address.
It shows sucess
ALTER DATABASE TestDB SET WITNESS=N'TCP://[10.0.15.146]:5022'
it show error message as:
Msg 1416, Level 16, State 1, Line 1 Database "SCMPGroup" is not configured for database mirroring.
Please help me! I down't know if NT4 domain or evalution copy problem in there.
I am trying to enable database mirroring for 100 database. It goes error free till 59 databases (some times 60 databases) with the status (principal, synchronized) on principal. on the 60th or 61st database it gave the status (principal, disconnected). Also mirror starts acting abnormal. connection to mirror starts to give connection timeout and it is not enabling database mirroring on any more databases. I have SQL SERVER 2005 Enterprise with SP1 on the servers. witness is not included yet.
these are my test servers... i have more than 500 databases on my production servers.
principal and mirror both are using port 5022 for ENDPOINT communication.
All of the databases are critical and all must be included in the Database Mirroring. so, after that I tried to implement database mirroring again...... System has 3 GB of RAM, SQL SERVER (Mirror) using 85 MB of RAM but still giving this error while trying to enable database mirroring for 37th Database.....
"There is insufficient system Memory to run this query"
Alter failed for Database 'Northwind'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address "TCP://kookaburra.sydney.ssw.com.au:5022" can not 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)
I could connect to both instances:
C:Documents and SettingsSergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k ookaburra,5022 1> exit C:Documents and SettingsSergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k ookaburrasydney2005,5023 1> exit
I could ping my laptop
C:Documents and SettingsSergeiTchernykh.SSW2000>ping -a kookaburra
Pinging kookaburra.sydney.ssw.com.au [10.0.0.1] with 32 bytes of data:
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128 Reply from 10.0.0.1: bytes=32 time<1ms TTL=128 Reply from 10.0.0.1: bytes=32 time<1ms TTL=128 Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Ping statistics for 10.0.0.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms
Can someone at Microsoft comment on this article, specifically, how it relates to SQL Replication? Will SQL Replication fall into the category of what this article describes, or only the mirroring feature?
I am trying mirroring in my own laptop in 2 different instances of 2005 but when i configure it gives me an error "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"
does anyone know what is the problem???? i read one post but could not understand properly.
Hi everyone, I have a project on my hand in which i need to implement two SQL 2005 servers And configure it as DB mirroring. I know that for the sake of 'fail over' I need to configure a witness server as well.
My question is: For the purpose of the mirror server and the principle server - what are the software requirements (enterprise, standard?) For the purpose of the witness server - what are the software requirements (enterprise, standard, express?) Should the witness server be a 'server' in terms of hardware...or is it enough to establish a strong pc machine?
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?
I am using SQL 2005 (Enterprise Edition, SP2) Mirroring with a Principal, Mirror and Witness set to High Safety mode and am a little confused!
As far as I understand it, during a failover, although the database content will automatically be available on the Mirror any user accounts will need to be either manually created or manually mapped to the new principal. Is this the case? If so, I can't really see the purpose of using Mirroring (in my scenario anyway).
For example - We principally use the database servers for our web applications which we connect to using ADO.NET. Each database and Web app has a different SQL user account to allow access etc. If we experience a failover this means the users will not be able to use the web app until we have manually recreated/mapped the SQL accounts for each and every database. Surely this is a massive oversight?! Or am I simply getting the wrong end of the stick.
I'm devlving into 2005 and specifically mirroring.Having read the following article (http://www.microsoft.com/technet/pr...5/dbmirror.mspx ),is mirroring a sensible option as it indicates that it should not beused in a production environment, yet the SQL2005 seminars I've been toindicate that this is a worthy option.Do we also need a witness server, or can this be done (sensibly) withtwo SQL2005 servers ?Currently we have 2xSQL2000 (clustered) servers and a SAN for the data.If we go down the route of mirroring, it looks like we can get rid ofthe SAN and Cluster and use the mirroring instead.Any advice on the pro's / cons would be appreciated.ThanksRyan
Can I know is it possible using database mirroring for only certain tables from the database, not for the whole database in sql server 2005. Hopefully able to get any assistance here. Thx alot.
Hi, for SQL 2005 - to set up database mirroring, is it required to have Active Directory installed or to be part of a domain? I am looking to use a workgroup with the SQL machines only and to not reference any domain and cannot find this information - is this possible?
We have a .NET application with a backend SQL 2005. The issue started when we setup DB mirroring on this db. The issue is an System.InvalidOperationException that is thrown every once in a while when connecting to the databases. The message is 'Internal .Net Framework Data Provider error 6.'.
Both Operating System (Windows 2003) and databases (SQL server 2005) are up to date, including all Security Updates and Service Packs.
The issue disapeared when we stopped db mirroring.
I am new to 2005 mirroring but have read a lot of documentation on it. I want to use mirroring to centralize a bunch of DBs to one location and then back them up from that central point. However, from what I've read since the mirror is kept in a "recovering state" it cannot be accessed directly. Does that mean I wont be able to backup the mirrors? If so, are there any work arounds or different strategies for this?
I was hoping to learn from real world experience regarding the hardware requirements for SQL 2005. For both the 32 and 64 bit OS the minimumn recommendaiton is single processor, 1GB or more, 512 MB RAM.
This seems very thin and I was wondering if anyone had recommendations for hardware configs for a mirrored db cluster? I'm filling in as a DBA and apologize for the ignorance.
i have a small problem to connect SQL Native ODBC Client to a mirrored Database. The connection goes without any problem when I connect to a primary Database, but when i give this connect string to an ODBC Client ""Server=Partner_A; Failover Partner=Partner_B; Database=XXX; Network=dbmssocn"", then i get an connect fail. There are no firewalls and other things on the network. I am also able to connect to a mirror database during failover, but I'm not able to set both servers on the ODBC Connection.
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....
I 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.