We have a database with AlwaysOn enabled, where the database is getting mirrored using AlwaysOn to a secondary node in a two node cluster. Now we want to set up a DR site for that database in a different data center.
So trying to analyse what technologies. We can use for setting up the DR for that database. Can we use mirroring / replication or what's the best way of setting up DR on that database.
We have an AG scenario where we are using WFC on a 2 node cluster. We are then using AG for mirroring the databases to both nodes and have a listener.
What I want to do next is to establish another copy of the database at a remote location. But I don't want to add the 3rd system to the WFC. I am not a big fan of WFC and I have seen it causes many more problems. The 3rd system will be in a remote location and the network not 100% reliable. I have seen in the past that it causes the entire cluster to hang and causing my production to crash which I don't want.
I there a way to add a 3rd node to the mirror configuration. I don't know if I can add a 3rd node to the AG unless it is part of the same cluster.
I know I can configure log shipping, I am fine with it but in the source, I have no control of which node the DB will be. I am not sure if a log shipping scenario can be configured using the listener instead of the physical host.
I have checked my Production databases, both are went to Disconnected state, both are unable to connect to the application, i checked all the services and its working fine. and restarted the end points on Principal and Mirror, still issue not solved, after restart the witness server End point both the databases are working fine.
View 3 Replies View RelatedI can't install the SQL Server 2012 Express version with tools downloaded from [URL]
I'm getting the following error:Â
Error installing SQL Server Database Engine Services Instance Features
Could not find the Database Engine startup handle.
Error code: 0x851A0019
I'm trying to install a new stand-alone version with a default instance but always getting this error. From the features I'm installing the database engine services (not the replication), all the tools, only the basic management toolds and the SQL Client connectivity SDK.
Further in the installation I'm choosing the Windows authentication and in the list of SQL administrators I've already tried to install as current user but also as System. Both with the same error result.
[URL]
Note: Visual studio is not installed.
SP2 refuses to install for the Database Engine because €œthe previous upgrade did not complete€?, i.e. it detects a suspended installation. The same thing happened to me when I installed SP1. I then remember opening the maintenance dialog and completing the setup, after which SP1 installed just fine.
When I try to do the same thing this time, I€™m presented with the €?complete the suspended installation€? option, which when I run it reports the installation completed successfully. However, in reality the installation still remains in suspended mode for whatever reason. If I reboot and open the maintenance dialog, I€™m still presented with the €?complete the suspended installation€? option. I€™ve even tried to run it more than once, but it doesn€™t help. Needless to say, SP2 still refuses to install.
I don't know why the installation is in a suspended state. I know every part of SP1 installed successfully in the end, and the computer never crashed during the installation. I've been using it every day since then, without any problems. All other parts of SP2 did install successfully.
I created a feedback entry on Microsoft Connect, where you can find the log files created by Windows Installer. The URL is:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259606
Any ideas what I can do to resolve this problem?
Hello,
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
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'.
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.
I'm setting up my first pair of Sql 2012 servers using AlwaysOn. I set up backups to run on the primary and I understand that you can set up backups to run on both the primary and secondary servers but the secondary will fail. Is there a way I can stop the secondary server from sending out error messages about failed backups? Is it possible to script it so that the server looks at whether it's primary or secondary and turns on or off alerts based on that?
View 8 Replies View RelatedWe have a client which they have production 2 node cluster environment. On it around 200 databases with single SQL instance.
Now client wants disatster plan for these 200 database. In these 200 database 3 db's are around 80 GB each databases remaing are less than 5 DB.Note: All these 200 db's are having produciton sites (i mean to say each db is having single site)
For this DR paln clinet is going to provide other DR server,they wants to setup DR between exsting produciton cluster instance to this DR server.
So in this case we have suggest SQL server AlwaysOn availability group.
Here my main question is can we keep all these databases in single AG? .If yes, guidlines to move up. if not, do we have any limitations.Also, best method to setup for this DR plan.
I am using alwayson on my SQL 2012 databases. I am using ola hallengren scripts for backing up databases. Full & diff db backups work fine, but the log is not getting backed up. The tran log backup job doesn't error out too. Trying to figure out what I may be missing?
View 10 Replies View RelatedTo set up AlwaysOn Feature on cluster environment how many VM's we would require.
We have already have 2 VM's for cluster but am not able to configure AlwaysOn.Â
We have a reporting job that will query the mainDB and do so come calculation and insert into reportingDB, there are a lot of blocking and performance issue when it query the mainDB so we have plan to read the secondary replica and do the processing there later to insert back into primaryDB (reportingDB).. for this we have implemented linked server but it gets timeout, I do not want to change the remote query timeout on server level so is there anyway to do this, Any one making use of secondary replica to do get the data by job?
View 4 Replies View RelatedWe have an existing SQL Server 2012 Enterprise cluster with 2 nodes (active-active) and uses Windows 2008 R2 OS. We are looking for a way to increase HA as well as offload backups to secondary server and it was suggested that AlwayOn could be an option.
The questions I have are:
1) Is it possible to turn on AalwaysOn feature on an existing cluster?
2) If yes to above, does the secondary replica need to exist as a node on the same cluster or can it be on a completely different cluster?
3) If the secondary replica is on the same cluster (i.e. we add a 3rd node to existing 2 node cluster), can that node be provisioned with storage from a completely different SAN? (i.e. Node 1 and Node 2 accesses LUNs on SAN1 and Node 3 accesses LUNs on SAN2).
Though to setup Alwayson, it is necessary to have both SQl Instances to be in same version, same database file path , etc.But, is this possible to setup the AlwaysOn between a default and a named instance?
View 1 Replies View RelatedI have configured AlwaysOn HA setup with HyperV environments without shared disk and using quorum voting in file share witness.
1. I want to monitor AlwaysOn HA setup and AO Group database on daily basis.
2. To configure email alerts for proactive monitoring if unusual events occur.
Scripts for monitoring in that AO setup as well as AO group database ...
I have a 2 node cluster having 4 cores each wherein having 3 instances of SQL 2008 R2 enterprise comprising of 60 databases, 20 on each instance. I need to setup mirroring for each of the databases to a secondary server having 4 cores and 3 instances.
What i understand is that in this case the mirror server will be providing max of 512 worker threads and the 60 mirror databases would consume 240 threads.
What all needs to be checked for looking into the feasibility of going ahead with a async mirror setup as mentioned above.
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 RelatedI am trying to setup alwayson on 2008r2 server on 2 instances in the same machine fort testing purpose. I installed 'Failover Cluster Manager' now I don't see 'Nodes' to add up.Should I run 'Validate a configuration Wizard'? or something else.. Can't find online to setup 'Alwayson' from the scratch...Will I able to setup 'Always on' on same server but different instances like mirroring? or has to be different servers?
View 5 Replies View RelatedOS - Windows 2012 R2 Standard Edition.
DB - SQL 2012 Enterprise edition
Total 3 nodes participates for AO setup, 2 Nodes for Local HA and 1 Node for another datacenter for DR. All the 3 Nodes are same domain name and member.
1. Local First 2 Nodes are same subnet XXX.XX.44.XX
2. DR Node another subnet XXX.XX.128.XX
Does it require to add two different IP address while creating cluster name? Not using shared disk SAN storage etc.. I am using Node majority quorum witness setting for failover.Â
I am planning to have AlwaysON Availability Groups setup between Server 1 and Server 2
Server 1 -->Publisher-->2014 SQL Enterprise edition-->Windows Std 2012 --> Always on Primary Replica
Server 2 -->Publisher(when DR happens)-->2014 SQL Enterprise edition-->Windows Std 2012 --> Secondary  Primary
Server 4 as Subscriber
Server X Â as Remote Distributor ..
as of now , In the event of Disaster happens on Node 1  i will have to manage JOBS on Node 2  (backups, Maintenance, replication, day-day other Jobs)..Is there a way i can call all jobs to run from a remote server which executes on Active NODE(which ever is active)? so that in the event of disaster happens on node1, when  node 2 becomes online and  i don't have to do anything with SQL Agent or jobs on Node 2?
Two servers are configured with Windows 2008 / SQL server 2012 utililizing Always-On for HA. We need to upgrade both servers to Windows 2012 / SQL Server 2014 with minimum downtime(Time for Always-On failover). The upgrade to SQL 2014 is straight forward with for minimum downtime.The Windows upgrade(2008 -> 2012) is the problem. From what I have observed and read in blogs.The Windows node to be upgraded must be removed from the Windows cluster before the node can be upgraded to Win 2012.A Win 2008 and Win 2012 node can not reside in the same cluster. If this is true then the only option I can think of is to dump the DB on WIN 2008 server and restore on Win 2012. This is an outage(time it takes to dump and restore).Is there any other method to upgrade these two nodes utilizing Always-On of some other method without downtime?
View 2 Replies View RelatedI have an active passive cluster on my primary Data center in NY and have a DR Active / passive SQL Cluster in TX. These are two separate clusters in the same domain using the same SQL server credentials.Both clusters host an active / passive SQL instance. Lets call it SQLNY(Primary) and SQLTX (DR). I want to enable always On Availability group within the two SQL Instances SQLNY and SQLTX. The listener will be SQLAG which will be used by the Application to connect to the SQL instance. Is there a practical way to implement this? This will not only give me instant fail over within the NY (Primary) but also give me the flexibility to fail over to TX. I am using SQL 2014 Enterprise Edition on both clusters.
View 6 Replies View RelatedI 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 RelatedI am trying to set up log shipping in a clustered server environment. I am pretty confused about the location of the shared folder to be created to put backup created by  log shipping job. Which drive should I use either local or clustered shared drive to store the backups in primary server and  to copy the same in secondary Server?
View 2 Replies View RelatedHi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)
Best Regards,
Hans
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.
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
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
I am using MS SQL 2000 Standard, is it possible to create a mirror database in SQL 2000? If so, would you suggest how to do this. Thanks! I am a newbie about this.
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,
anyone has the experience to solve this?
View 8 Replies View RelatedHas anyone tried mirroring any of the Dynamics GP databases yet?
I am looking at using this and what to see if anyone has had any problems or even know if it can be done.
Thanks