SQL 2012 :: Compress Data Transfer During Synchronization Between Availability Group Nodes?

Apr 13, 2015

I got this situation where my network admin observerd that there is a high network utilization between 2 nodes in our AG (the primary node & the DR site, 2 separate locations of course); then he advised to compress the data transfer between those 2 nodes as the previous DBA already did that before!

Ok, I have no clue about this, so decided to google it, got nothing. My backup is already compressed through some third party app (just in case if that matters to the subject).

View 3 Replies


ADVERTISEMENT

Recovery :: Availability Group Quorum - Majority Of Nodes Down But Cluster Still Up?

Sep 9, 2015

I'm just starting to work with AlwaysOn Availability and WSFC.

I have in my environment (in Azure) a DC, WSFC and to SQL instances, so I have 3 nodes in my Failover Cluster:

WSFC 
SQL1
SQL2

If I simulate failure by shutting down one of the SQL boxes my Availability group seamlessly fails over to the other SQL instance - which is great.

However, I'm starting to look into the workings of the Quorum, my envt has the default settings and when I shutdown both of my SQL servers I expected the Cluster itself to go offline as 2 out of the 3 votes will be negative, but the Cluster is still up - Screenshot below when SQL1 and SQL2 are  shutdown:

Going through the Wizard (but not changing anything) it shows following config:

View 6 Replies View Related

Recovery :: Migrating Existing Availability Group To New Datacenter Availability Group

Nov 20, 2015

need to migrate a cluster with an AG dtabases to new data center cluster with AG.

I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.

View 17 Replies View Related

SQL 2012 :: Backup Of Availability Group?

Aug 6, 2015

Proposed configuration:

Using a centralized backup server from which to run SQL backups. (this server is not part of this AVG)

AVG with backup preference set to primary.

SQL backup (daily full and hourly logs run from centralized backup server) with target set to AG Listener name.

Any failover should still target Primary Replica.

View 2 Replies View Related

SQL 2012 :: Reestablish One Database In Availability Group

Jul 29, 2014

I've got an availability group with multiple databases, replicating to multiple secondary servers. On one of the secondary servers, some of the databases are not synchronising, and when we try re-establish the sync we get an LSN error. I can't see any obvious way to re-establish only one database on one secondary without affecting all databases on that secondary or affecting that database on all secondary nodes.

The options I seem to have are to either remove the database and then re-add it, in which case this affects all secondary replicas, or to remove the secondary replica and add it, in which case all the DBs are added.

View 3 Replies View Related

SQL 2012 :: Server Clustered Availability Group

Jul 30, 2014

I have 2, 2 node clusters

PROD1(cluster 1) Clustered SQL instance1
PROD2(cluster 1)
DR 1 (cluster 2) Clustered SQL insatace 2
DR 2 (cluster 2)

I have set an availability group up from the PROD instance to the DR instance.How does the AG behave if a SQL instance fails at PROD? Does it try to fail over to Node 2 on Prod before going over to DR? or bring the Replica at DR online straight away? Can we only use Manual Failover of the AG in this scenario to make use of the High Availability of the Windows cluster?

View 3 Replies View Related

SQL 2012 :: Using Availability Group Listener For UNC Connection?

Sep 10, 2014

I have set up a couple of servers in a SQL 2012 AlwaysOn Availability Group (non FCI). I have also configured a Listener which enables SQL clients to connect to the server currently servicing the database, as expected.

I would also like non SQL clients to be able to connect to the server currently hosting the database so that they can run scripts sitting in a share. I don't have a shared disk so just have a directory share on each server with the same scripts in each directory.

I am able to ping and RDP to the listener IP address/name and end up on the correct server but am unable to connect to the share ListenerNameShare. Is that actually supported? If it is, any thoughts on what I need to do to get it going. If it isn't what other options do I have?

View 0 Replies View Related

SQL 2012 :: Availability Group Listener Port

Oct 26, 2014

So I have Availability groups configured as well as the Availability Group Listener, what If I want to change the port that the listener is listening on, do I need to reboot the server or is it dynamic across the board ?

View 5 Replies View Related

SQL 2012 :: Migrating VLDB To Availability Group

Feb 4, 2015

I have been using AlwaysON AG for a long time now and currently have about 10TB of data across 120 databases and 3 AG groups for any application that is on SQL 2012 with great success. Each AG group is running on patch level 11.0.5058.0 with 2 synchronous replica(on different SANS) in Primary Data center and 1 ASYNC replica in DR. Migration has been a non-issue because none of the databases weren't substantial enough that I could not fit into my maintenance window which is 12-4AM on SAT morning.

My issue is that my last application to migrate to 2012 includes a 4TB TDE encrypted databases database which is about 10x larger than any of the previous ones I have migrated. The database takes 4 hours to backup after tuning extensively(I hate TDE!!)

The restore to the primary replica is instant because of seeding incremental but the issue comes from having to backup the database before adding to the availability group. 4 hours is my exact outage window and I can't get any more. My plan to migrate application is to -

First Outage Window

1) Restore Database from 2008 to 2012 Primary Replica
2) Change application ARECORD(or cname not sure which) to Primary replica
3) Run database on single node until next outage window

Week Later
1) Add database to availability group
2) Change ARECORD/CNAME to listener

What I don't like about this is I am going an entire week with 1 node instead of 3 which is worrisome. How to accomplish this I would love to hear from you or any type of comment from people who have worked with VLDB in availability groups and what you like/hate/loved about doing it. I am trying to go all in on this software and have loved it so far but getting worried when it comes to the VLDB migration.

View 4 Replies View Related

SQL 2012 :: Backup Status In Availability Group

Mar 16, 2015

some of the databases are not backed up in availability groups , they are showing the status as synchronized , how can we check the reason of this ?

View 2 Replies View Related

SQL 2012 :: AlwaysOn Availability Group Per Database

Jun 26, 2015

We are planning to upgrade our production servers from mirroring to alwayson. Our current mirror setup gives the advantage that it can failover a single database.To have a similar setup in alwayson we are probably going to create an availability group per database. Any other disadvantage in this except for the extra initial configuration work?

View 9 Replies View Related

SQL 2012 :: Backup / Restore Availability Group Following Failover

Jul 21, 2014

I have 2 servers in a SQL Server Fail-Over Cluster. IOW I use always-on availability groups. I run backups - full, diff and log - regularly via SQL Agent on one server only depending on which is primary. If there is a fail-over, then backups will continue on the other server. If I have to restore a database in an availability group I probably would need some combination of full, diff, and log backups from each server. Would that actually work? I test the backups weekly however I just realized that I never tested that scenario.

View 1 Replies View Related

SQL 2012 :: Schema Changes On High Availability Group Databases

Jul 23, 2014

So we have our HA group servers and databases, now we want to deploy schema changes to the HA group databases.

1.) Can we deploy the changes to Server2.mydatabase whilst still having Server1.mydatabase available to users?

2.) If yes, what is involved in doing so

3.) If no, What is the best suggestions to apply schema changes to HA databases.

View 1 Replies View Related

SQL 2012 :: Availability Group Is Not Ready For Automatic Failover

Dec 19, 2014

how to resolve and trouble shoot the availability group is not ready for automatic failover in sql2012

View 4 Replies View Related

SQL 2012 :: High Availability Group Transaction Log Shrink

Feb 3, 2015

I have a Customer running a database in a High Availability Group and I am not familiar with the set up... They have a transaction log that quadrupled in size during a data import and update which was generated by an external application. They have limited server space and would like to shrink the log again now as this import / update only happens once a year. The way this has always been dealt with in the past was by shrinking the DB and logs after the update.

Now however, when attempting to do a log or db shrink, an error message is generated which says that the log cannot be shrunk as the DB is in use as part of an Availability Group....

The more I search and try to read up on this subject, it looks like the DB has to be removed from the Availability Group before the log can be shrunk and then the Availability Group has to be re-created or restored in some way. Is there a simple solution to this conundrum?

View 9 Replies View Related

SQL 2012 :: Adding Datafile To Database In Availability Group

May 15, 2015

one of my database is configured in availability group , I need to add another datafile to that database , how can I do this?

View 6 Replies View Related

SQL 2012 :: Automated Script Run On Availability Group Failover?

May 29, 2015

Let's say I have a two node AG, Server A and Server B. Server A is normally the primary and Server B is the replica. Whenever the primary fails over from A to B or from B to A, I'd like to automatically run a script that will restart the SQL Agent service on the new replica.

View 3 Replies View Related

SQL 2012 :: Availability Group Not Synchronizing After Network Outage

Aug 18, 2015

I've been working with sql server for some time, but couldn't find any way to fix this problem without rebooting the production and secondary sql servers, which is not very good for uptime .

I'm running a primary and secondary availability group on sql 2012 enterprise edition on windows server 2012, and it runs fine except when a network outage occurs.

Then the handshaking keeps failing, and the secondary databases all show not synchronizing.

The primary DBs all show synchronized.

I tried following several different troubleshooting guides for always on, but nothing I tried fixed the issue.

The only way to get them in sync is to reboot both primary and secondary. Just rebooting the secondary didn't fix it.

The secondary server error log showed AlwaysOn Availability Groups connection with primary DB established, then DB mirroring login succeeded, then error 35250, the connection to the primary replica is not active, then connection timeout.

I increased query connection timeouts to 60, but that didn't work.

View 3 Replies View Related

SQL Server 2012 :: Automatically Adding New Databases To Availability Group

Oct 28, 2013

automatically replicates new databases to Availability Group partners - if you do a little prep work on your environment first.To make it work:

1) Create linked servers on all group members pointing to all other servers in the group, with names matching the hostnames they represent.
2) Ensure suitable credentials (or 'current context' impersonation) for linked servers. Also: Enable RPC and RPC OUT
3) Run the DDL code below.
4) Schedule hadr_replicate_queue on [master] to run as often as you want initial syncs to occur. Every 5-10 minutes is plenty for most purposes.
5) Connect to an availability group listener and call CREATE DATABASE :)

I use a slightly more extended version of this code at home to do things like permissions synchronization across replicas - I essentially allow applications to install direct to an availability group replica and then have all the relevant objects replicate to other nodes. I don't really like going through manually and doing things, even though there's an AddIn from SQLSkills for management studio - it still requires manual intervention.

The main use I have for this at home is that I'm using the Azure pack, and want to automatically ensure that my newly created 'SQL Server Cloud' databases are highly available, plus it means when I install a non-alwayson aware product it doesn't require any extra work afterwards to allow failover to another machine.

* AlwaysOn Self-Population Script
* By: Steve Gray / steve@mostlyharmful.net
* Usage: Free, but buy me a beer if you're ever in Brisbane.
**/
USE [master]
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='hadr_pending_replicate')

[code]....

View 4 Replies View Related

SQL 2012 :: Availability Group Failover Stops Working After First Failure

Apr 21, 2014

I've setup a two node Cluster Server (non-shared storage) with a file sharing witness. I'm testing some of the different failover scenarios to see that everything is working properly. Everything works fine until I try testing the failure of the SQL Server service. When I stop the SQL Service on the primary server, it fails over to the secondary server as expected. I then start the service on the (now) secondary server and it comes back online as the secondary server. I then try to test that the service will fail back over when I stop the service on the new primary server.

However, when I stop the service, the secondary server now shows "resolving" and never comes back online. When I bring the service back up on the primary server, the secondary now shows as secondary instead of resolving. So to see if it's something about failing over from one server to another, I do a manual failover making the original primary server the primary again and everything is as it was originally.

I then stop the service on the primary server, but the secondary server now says resolving and the AG will not become available again until I start the service on the primary server.

It seems that when I first configured the quorum it worked fine the first failover scenario, then stopped working. I then added the file sharing witness, and failover worked the first time again, but not after that. For some reason after the initial failover it won't automatically failover again after that.

Config:

Servers: Windows Server 2012 Standard
SQL : SQL Server 2012 Enterprise SP1

View 5 Replies View Related

SQL 2012 :: AlwaysOn High Availability Group Showing All Databases

Apr 28, 2014

I came across an issue while migrating from SQL 2005 to SQL 2012 and using AlwaysOn Group. For some strange reason, when ever i connect to the Listener name for each AlwaysOn group, it list all the databases which is on the SQL instance, so i would be able to see databases that is not part of that Availability Group. I am not using default port, so have to put the port after the Name to connect and both Instance and Listener are using different port.
Testing the fail over works fine too, when i perform a manual failover, i can connect to any of the databases in the group from my application with no problem.

Considering that the Listener Port is different to the port which the instance is using?

View 8 Replies View Related

SQL 2012 :: Linked Servers Connecting To High Availability Group

Dec 16, 2014

I have a HA Listener which is visible and can be connected to, it has a read only secondary on a different subnet so when connecting to it we use the applicationintent = readonly and multisubnetfailover = true.

Trying to connect it as a linked server is giving me problems. I tried putting the extra info into the provider string but keep getting the failure to initialise error. I am trying to link SQL2012 to a 2012 HA group but will also need to connect from a sql2008 server as well

View 1 Replies View Related

SQL 2012 :: AlwaysOn Availability Group Listener DNS Resolving To DR Node

Jan 19, 2015

We are rolling out the use of Availability Group listeners to our SQL Server 2012 Environment which has a 2 node multi-subnet cluster. The Primary is R/W and the Secondary is a non-readable node that would be manually failed over to in a DR scenario

I have set up the AGL and asked the sysadmins to create a DNS record in both subnets with fixed IP's.

The issue I have having is that when I ask the app developers to connect to the databases using the AGL it is totally random whether the AGL resolves to the Primary or DR node - as a result that are having problems getting their apps to connect.

I was thinking of asking the sys admins to remove the DNS record in the DR subnet and then add it back in should we need to fail over - but I was thinking there must be a better way.

View 3 Replies View Related

SQL 2012 :: How To Check For Successful Full Backups On Availability Group Databases

Apr 20, 2015

I am setting up Availability Groups and I want to use the secondary replica to perform the full copy_only backups to reduce the load on the primary replica.But what is the best way to check for successful full backups on Availability Group databases?

Previously I could check the system table msdb.dbo.backupset but this is not available for copy_only backups.So I wonder how people are monitoring that their full backups have been successful?

Do you just check that the SQL Agent job that runs the backup was successful?

Or do you search the SQL Server Error Log for entries like "Database backed up. Database: xxx" where database xxx is in an Availability Group?

Or is there a better method?

View 1 Replies View Related

SQL 2012 :: AlwaysOn Availability Group Replica Ends Up With User Account As Owner

Sep 5, 2014

Setting up a test AlwaysOn Availability Group for one database.

However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.

Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.

I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.

View 2 Replies View Related

Recovery :: AlwaysOn Availability Groups Server Logins Fails When Switching Between Nodes

Jul 9, 2015

I am setting up a 3 node cluster as part of an availability group.Initially I tested failover between nodes using SQL Management Studio and everything failed over successfully when I stopped a node, and I was still able to write queries. I started to test with an application which connects using an SQL user and whenever I would switch nodes, I would get login failed. I believe the cause of this issue is because the server logins SID's which are tied to the database are different than the server logins on the other nodes which resulted in login failed.how can I ensure that the server logins SID's are the same between nodes? Is there a way of copying this over or how is this supposed to be done? I read a little about contained databases where I could just set the login on the database itself vs. creating a server login but I would rather not go down that route. 

View 4 Replies View Related

SQL Server 2012 :: Compress Table Timelines

Jul 8, 2014

Here is data I am working with:

CREATE TABLE HISTORY(CUSTOMER VARCHAR(10), PLANNBR VARCHAR(10), SUBPLAN VARCHAR(3),
STARTDATE DATETIME, ENDDATE DATETIME, HISTORYMONTH VARCHAR(6))
INSERT INTO HISTORY(CUSTOMER, PLANNBR, SUBPLAN, STARTDATE, ENDDATE) VALUES('9111111', 'H1111LAC', '006', '2014-01-01', '2014-05-31', '201401')
INSERT INTO HISTORY(CUSTOMER, PLANNBR, SUBPLAN, STARTDATE, ENDDATE) VALUES('9111111','H1111LAC', '006', '2014-01-01', '2014-05-31', '201402')

[code]...

I need to compress these segments into records which will look like this. The HistoryMonth column is not used to make the final determination of records to keep.

CUSTOMERPLANNBRSUBPLANSTARTDATEENDDATE

9111111H1111LAC0062014-01-012014-05-31
9111111H1111OC0102014-06-012999-12-31
9111112H1111LAC0062014-01-012014-05-31
9111112H1111LAC0182014-06-012999-12-31
9111113H1111LAC0062014-01-012999-12-31
9111114H1111LAC0062014-01-012014-02-28
9111114H1111LAC0062014-04-012999-12-31

Customer 9111111 shows a change in both PlanNbr and SubPlan 2014-06-01 Need to show both records
Customer 9111112 shows a change in SubPlan 2014-06-01 - Need to show both records
Customer 9111113 shows no change in PlanNbr or SubPlan - Need to show 1 record
Customer 9111114 show a break between and enddate and next start > 1 day - need to show both records

View 5 Replies View Related

SQL 2012 :: Compress Existing Backup File?

Jan 22, 2015

I have a SQL 2012 enterprise server, and I'm using Commvault as my backups. So commvault can restore a .bak file to my server, but it cannot use sql compression on the file apparently. So what would be a 150GB .bak backup file is now 600GB. I have to manually upload these files to an auditing firm on an sftp server and the transfer times are now huge.

Is there a way to use something in sql to compress this already existing .bak file down?

View 5 Replies View Related

SQL 2012 :: Log File Data Transfer Amount (MB) Versus Data File Transfer Amount (MB)

Mar 19, 2014

In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?

I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?

I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?

View 6 Replies View Related

SQL 2012 :: SSIS - Transfer All Data From Source To Destination

Sep 1, 2014

I am a complete newbie to SSIS. I can create a simple package to transfer data between SQL instances and thats about it.

I have tableA (source data) and tableB (Destination data). TableA has 4 column and tableB has 5. I want to transfer all of the columns from tableA into TableB, but the 5th column in tableB needs to be populated with the ServerInstance name of the server TableA sits on. Do I need to have multiple data sources to achieve this? I have tried but no matter how I set it up, the Column in the destination is set to ignore.

View 2 Replies View Related

SQL 2012 :: Transfer All Jobs From One Instance To Another By Using Data Tools - SMO Connection?

Feb 12, 2014

I am trying to transfer all jobs from one instance to another by using data tools. However, once i tried to make smo connection i am getting this error;

A network-related or instance-specific error occurred while establishing a connection to SQL Server.

In order to solve this issue i have tried these solutions;

1. SQL Server should be up and running. (OK)
2. Enable TCP/IP in SQL Server Configuration (OK)
3. Open Port in Windows Firewall (FW ACCEPTS ALL LOCAL PORTS)
4. Enable Remote Connection (CHECK OUT THE sp_configure SETTINGS, i even right-click instance then see from properties )
5. Enable SQL Server Browser Service (sql server browser has been restarted)
6. Create exception of sqlbrowser.exe in Firewall (FW ACCEPTS ALL PROGRAMS)
7. I tried windows and sql authentication which has sysadmin role
8. INSTANCE name is also chekced millions of times

View 0 Replies View Related

How To Compress Data At Restore Time

Aug 17, 2015

Is it possible to compress the data at restore time?.

Backup is coming from a not compressed database.

The backup file is compressed.

View 4 Replies View Related

Recovery :: Create New Availability Group

Nov 2, 2015

i am preparing for always on with multiple instant.is there any consideration with multiple instant ? with each instant shall i create new availability group?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved