Secondary server is offline due to a hardware issue and the log files are growing on Primary sever for availability databases. The log drive is running out of space. How can I stop the log growth for the primary databases?
View 4 RepliesI 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?
I have four instances and each instance have its own Availability Group with its own listener.
Would like to know if you can have one listener for multiple Availability groups?
We had 3 Availability Groups set up in SQL 2012 last year but they were poorly named so I am just looking to rename them but there doesn't seem to be any command for it that I can find.Can they not be renamed once created? I guess I could just create new ones and move the DB's into them but just thought I would check!
View 1 Replies View RelatedWe 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 RelatedWe have multiple SQL 2012 SQL servers setup in an alwaysOn availability groups. Where should we schedule the re-index? We have Server1 as the primary and 2 secondaries Server2 and Server3. Are their any tricks to have it run on which ever one is the primary?
View 1 Replies View RelatedI 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 RelatedWe 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.
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]....
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.
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 RelatedIn always on docs in msdn they mention only about backup of secondary.. explain the backup of primary or how logs are managed in primary database. My doubt is a normal database in full recovery mode the log file will grow if we didnt take proper log backup,how the same is managed in primary in Always On.
View 2 Replies View RelatedLooking for info on ole/requirement of primary and secondary replica under SQL Server 2012 AlwaysOn.
View 4 Replies View RelatedI have a SP that runs on the primary in 18 min and 45 min on the secondary( poorly written cursor,trying to fix it).Both machines are Exactly the same.I ran them in the middle of the night when no one was on the Sec. Node as we use it for reporting.
PLE: 7,000+
AVG Disk sec/write below .01
AVG Disk sec/read below .01
CPU below 5%
both machines set a max dop 4
I was working on a job to send me info each morning about database file free space and was noticing some odd things when looking at the log file VLFs for one of my databases in an AlwaysOn availability group.When I run DBCC LOGINFO on the secondary replica for this database, I get what I expect and most VLFs have a status of 0 (indicating the VLFs are reusable or unused). When I run DBCC LOGINFO on the primary replica, all of the VLFs have a status of 2 (active or recoverable).
Since log backups on the secondary replica in AlwaysOn still truncate the log in the primary replica, I would expect that the VLFs in the primary replica would also be mostly in a reusable or unused state. My log file sizes are the same size on each server and my backups are completing successfully. what might be causing the VLFs on the primary replica to have a status of 2 in DBCC LOGINFO when taking log backups from the secondary replica?
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.
The MSDN doc makes it sound like after a failover of the primary, the CDC data won't "keep working" on the secondary unless you "To allow the logreader to proceed further and still have disaster recovery capacity, remove the original primary replica from the availability group using ALTER AVAILABITY GROUP <group_name> REMOVE REPLICA. Then add a new secondary replica to the availability group."
We have a few CDC tracked tables that we use and the general idea of AlwaysOn I thought was to minimize all the overhead and let things "just work" so your apps just connect and the listener re-routes everything where it needs to go.
It looks like to get this working properly an automated job /trigger would have to wait for a failover event and then kick off tasks to remove and re-add the replica and perhaps start up the CDC job on the secondary?
I have Configured always on Availability groups between Server 1 Primary Replica(Active), server 2 Secondary Replica(Passive) on top of WCFS...
Listener Name: AGListner( CLIENTS/APPLICATIONS connect using this Name)
Testing Scenario 1(on Virtual Servers):
I have turned network down on Server 1(primary) , then secondary server (Passive ) one came ONLINE and this is now the primary and i was able to connect to AGListner.now When i try to SHUTDOWN/POWER OFF PRIMARY(current Active server),failover happened to Secondary successfully but lost cluster and lost AGListner and was not able to connect ....now applications which are trying to connect using AGListenr name will loose all connections..does AlwaysOn Availability does not support Server SHUTDOWN/POWER OFF?or is there a way to resolve this? or am i doing wrong somewhere?
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?
What I asked for: Three Windows Server 2012 R2 machines with independent storage running a SQL Server 2014 AlwaysOn Availability Group. DB1 would be the primary, DB2 would be a synchronous replica, and DB3 would be a remote asynchronous replica.
What I was given: a two-node Windows Server 2012 R2 WSFC to run SQL Server 2014 Enterprise with shared storage and a third (remote) Windows Server 2012 R2 machine with independent storage, also with SQL Server 2014 Enterprise, to host an AlwaysOn Availability Groups asynchronous replica.
DB1 and DB2 (as Cluster1) share an E: drive. The remote DB3 has its own E: drive. Initially, DB3’s E: drive was claimed as a cluster resource and I couldn’t even see it. I’ve had several ugly days trying to make this work and have temporarily given up, installing DB3 as a standalone SQL Server that is no longer part of the WSFC and pointing everything towards that (it was originally a third node in the WSFC).
Is it possible to create an AlwaysOn Availability Group with nested clusters (i.e. create the AOAG with Cluster1 and DB3 and somehow ignore the individual nodes that comprise Cluster1)?
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 ..
If i create Publications on Server1 (primary replica) to subscriber 4 servcer, will the publication be created automatically in Secondary Replica Server2 ? or do i have to create manullay using GUI/T Sql on Both Servers?
I have configured replication between Always ON Availability Groups (Listener) (PUBLISHER), remote distributor to XYZ SUBSCRIBER...with above link ...
Now, I want to know how to replicate Data from XYZ SERVER a PUBLISHER to Always ON Availability Groups (Listener) (SUBSCRIBER)? Distributor Database being on XYZEX:
XYZ SQL SERVER as PUBLISHER, and DISTRIBUTOR
to
Always ON Availability Groups (Listener) SUBSCRIBER...
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?
This is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.
View 1 Replies View RelatedWe are currently using 2008 environment. We do have an SSIS Package running. The package used to run everyday and take the production server full backups and restore into the another server. Then do some delete commands and do some updates in that database on that server (We have some sensitive data other than Production we have to run that scripts in any environment). After run all those delete statements another team will read the data from that database.
We are planning to migrate to 2014 and set up always on and use the replica as the source. In this case how the package will work?
How to change that SSIS package. With the 2014 always on we are directly reading the data there is no backups to restore then how to run the delete statements?
We are looking at going down the High Availability Always On route. However we have some concerns around the lack of support for MSDTC. In short we are concerned that developers may introduce functionality either on purpose or by mistake that uses the or escalates the Query’s to the MSTDTC. As this could result in database splitting.
Understand that this will be a moot point in SQL 2016 but for 20122014 is it possible to disable the MSDTC to protect against this and run High Availability Always On. ? Does it just need to be disabled on the SQL Server or does it need to be done on the application server ?
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 RelatedI was looking to change the file growth setting in our AlwaysOn environment databases.We have a single availability group, one primary and one secondary replica. I learned that when changing the file growth setting on the primary databases (data file), the change flows though to the database on the secondary replica.However after doing the same with the log files, the file growth setting changed on the primary but the change did NOT propagate to the secondary.
Is the solution to apply the change directly to the secondary?here's the T-SQL code I used:
ALTER DATABASE myDB
MODIFY FILE ( NAME = N'myDB_log', FILEGROWTH = 512MB );
GO
SQL Server 2012 (11.0.5532)
just i see a database and a table 'tbl_OutBox_MT' where there is now primary key and have index (non unique, non cluster). and it store almost 3000000 data per everyday. and wipe out data from their and archive all data to other location and broadcast this table 'tbl_OutBox_MT' by mobile operator everyday from morning to evening. but when it perform broadcast it to mobile operator it takes huge time. because this table gather data from different sources (tables) by using complex query and INSER INTO statement and insert into this table.
I need to perform first, my observation is there is no primary key. when i run any complex query into this table it takes huge time and sometimes shows transaction deadlock error.
CREATE TABLE [dbo].[tbl_OutBox_MT](
[TRAN_ID] [varchar](36) NOT NULL,
[OUT_MSG_ID] [int] IDENTITY(1,1) NOT NULL,
[OUT_MSG_ID_TELCO] AS (CONVERT([bigint],((((CONVERT([varchar](4),datepart(year,[PROCESS_TIME]),(0))+case len(CONVERT([varchar](2),datepart(month,[PROCESS_TIME]),(0))) when (1) then '0' else '' end)
[code]....
Can we set up always on availability groups in server 2012 standard edition.
View 3 Replies View RelatedWe have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies View RelatedHow do I add my second (secondary) node in my AlwaysOn Availability Group, after adding my head node, and the secondary node is a virtual machine. See based on the attached file if it is the correct way?
View 2 Replies View RelatedHow to test always on availablity after configuring them.I have configured always on group with 1 active and 1 passive with readonly.I want to test from application.what are cases which we can have for testing.
View 5 Replies View Related