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


ADVERTISEMENT

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 :: 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 :: High Availability - Only Run On Primary

Mar 9, 2015

We have H/A setup on SQL2012. Seems to work fine.I thought there was code I could add that would check to see if it was running on the primary node?

View 3 Replies View Related

Recovery :: Cannot Create Listener For High Availability Group Of AlwaysOn On Cluster Environment

May 27, 2015

I have getting issues when i am creating listener for always On . Error shown as below

Can not bring  the Windows server fail over cluster (WSFC) resources online. (Error Code 5942). The WSFC service may not be running or may not be accessible in its currents states, or the WSFC resources may not be in a state that could accept the request.

For information about this error code see "system error code" in windows development documentation 

The attempt to create network name and IP address for the listener is failed. The WSFC service may not be running or may not be accessible in its currents states or the value provide for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate network name and IP address with network administrator. (Microsoft SQL Server error 41066) ...

View 2 Replies View Related

SQL 2012 :: AlwaysOn High Availability Features Disabled?

Nov 13, 2014

I am using SQL Server 2012 and my AlwaysOn High Availability features is not enabled? What should I do. Is this requires any extra system requirements to be installed?

View 5 Replies View Related

SQL 2012 :: High Availability Groups Service Restart

Jun 9, 2015

I inherited a SQL 2012 Ent server sitting on a 2008R2 server using AlwaysOn High Availability, two nodes.

Available Mode: Synchronous commit
Failover Mode: Manual
Connection in Primary role: Allow all connections
Readable secondary: No
seesion timeout: 10

Somebody decided to give SQL server priority boost so I need to change this ASAP. So I plan on doing the following.

1. Manually fail over to the secondary, which does not have the priority boost set to true
2. change the setting
3. restart the service
4. Manually fail over

My question is with the service restart. How does SQL handle if the DB changes on the new primary while the secondary is having the service restarted. Where can I see if the DB are sync again or if not where are they in the sync process.

View 2 Replies View Related

Recovery :: 2012 R2 High Availability Database Not Replicating?

May 19, 2015

Discovered that a geo-spatial AlwaysOn HA database (1 of 4) was not synchronizing as at a point in time earlier in the day.  Suspend Data Movement appears to be working perpetually without finishing.  The SQL Server services is in a Pending Changes state after an attempt to restart it from SQL Configuration Manager.  The Cluster Dashboard says it is in a Not Synchronizing state, with only the one database in question having a yellow triangle, all 3 others show green.  

The warning for the cluster is:At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state.  There is no abnormal data movement from the primary to the seconday.The warnings for the unhealthy database are:

The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.Either a database administrator or the system has suspended data synchronization on this availability database.So how to get this database back to synchronizing state?

View 2 Replies View Related

SQL 2012 :: Setup Snapshot Replication On High Availability Replica?

Jul 9, 2014

We have a vendor that is exposing our database via a High Availability replica. They are geographically far away from us though so we would like to extract portions of the database over to our side for our reporting /warehousing purposes. I was curious if it is possible to setup snapshot replication on a high availability group?

View 0 Replies View Related

Recovery :: Availability Group Transaction Log

Sep 12, 2014

We're having an issue with an AG where the the log backup does not appear to truncate the log. symptoms..Run full backupRun transaction log backup DBCC Loginfo shows all VLFs with a status of 2sys.databases.log_reuse_wait_desc says LOG_BACKUPOPENTRANS indicates no open trans.All backups are being run on the primary.

View 10 Replies View Related

High Availability To High Protection Without Reconfiguring Mirroring

Apr 23, 2007

Hi,

Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.

If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?

Thanks,
J.

View 3 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 :: 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 :: 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 2012 :: Argument Daily Transaction Log Shrink?

Jul 7, 2015

For a few days now I have a discussion with a colleague about shrinking the transaction log as a daily maintenance job on an OLTP database. The problem is I cant figure out a way to convince her she is doing something really wrong. Its not the first discussion.. Maintenance Plans.

She implemented this "solution" with a lot of customers as a solution against VLFs fragmentation and huge transaction log sizes. My thoughts about doing this is very clear and I have used the following arguments without success to convince her:

- To solve too many VLFs you have to focus on the actual size of the transaction log and the autogrowth settings in combination with regularly transaction log backups. Check the biggest transaction and modify the transaction log size based on this. Not use shrinking as a solution for solving many VLFs.

- Shrinking the transaction log file on a daily basis that is disk I/O intensive. When the transaction log file is too small for new transactions, the transaction log needs to grow and this will cause disk I/O, this can cause performance problems.

- It looks unprofessional.

These steps are used every morning at 6:00 AM and a transaction log backup is made every 30 minutes.

Step 1
DBCC SHRINKFILE (N'' , 0, TRUNCATEONLY);
go

Step 2
ALTER DATABASE
MODIFY FILE (NAME = N'', SIZE = 4098MB);
GO

My main purpose is making sure the customers have the best possible configuration and I cant accept this is being implemented. Are there any more arguments available for this issue?

View 2 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 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 :: Monitoring Table In High Transaction Database

Nov 4, 2015

I am developing a process to monitors a table in a high transaction database. The process will count the number of lines in the table to verify if it has changed or it is stuck. Due to the fact that the database has a lot of transaction I don't want to execute a query on database too often.l Is there another suitable way to accomplish this goal ?

View 2 Replies View Related

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 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







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