SQL Server Admin 2014 :: Separate One Availability Group To Several AVs?

Jul 3, 2015

We have 4 servers : Server 1 , Server 2 and HA , DR servers.

I designed 2 Plans to get HA support for my databases .

Which of them are better , And is there any problem in my design ?

View 4 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Notification On Availability Group Failover

Nov 10, 2014

Is there any way to trigger an event (ie. call a procedure/job/etc.) when/if an AG fails over?

Not looking to use agent alerts.

View 0 Replies View Related

SQL Server Admin 2014 :: Manual Failover Availability Group

Dec 24, 2014

Recently after turning on trace I restarted the sql services on a box which is configured for automatic failover availability groups. The ag has not failed over to other node. The other node was in resolving state. When the restarted server is back, the AG went back to that server. I checked the sys.availability groups field for failover property failure condition level, it's set to 1 which means service restarts should initiate the failover.

View 3 Replies View Related

SQL Server Admin 2014 :: AlwaysOn Availability Group Configuration

Jun 17, 2015

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

View 6 Replies View Related

SQL Server Admin 2014 :: Where To Find Availability Group Fail Over Occurrences

Nov 14, 2014

Where can I find dates and times to when an availability group was moved outside of the SQL error log?

View 1 Replies View Related

SQL Server Admin 2014 :: Exit From Not Synchronizing Primary Database In Availability Group

Jan 27, 2015

I Create an availibility Group with a primary and a secondary. (For test)

Then run Planed Failover.

Switched to secondary. That's ok.

After that I update some tables on secondary (That now is primary)

I Run Again Planed Failover on server 2.

Switched. OK

But primary database Get (Not synchronizing) Status.

And in primary I don't have that updates.

How to sync these databases and exit from Not synchronizing.

View 5 Replies View Related

SQL Server Admin 2014 :: Disconnected From Listener In Availability Group (When Primary Failed)

Jun 27, 2015

I setup an availability Group. (Only 2 servers - Primary And secondary) -- 21 , 22

I also define an listener . IP .. 23

1- In First step I connected To Listener (23) And in a while I inserted A record to a table .

While 1=1
insert into Tbl_T1(f1,f2) Values (1,2)

2- in second, I Stop the primary .

- I expected this while whitout disconnect, continue.

3- The while code stopped whit this message :

Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

4- I execute again the script, And it worked in new primary.

My questions :

1- is the listener disconnected between switched primary and secondary ? OR have we data loss between switching?

2- I did some huge update on Primary that fill the Log fiel space. And in last Update I got this error :

Msg 9002, Level 17, State 2, Line 27

The transaction log for database 'Your_DB' is full due to 'LOG_BACKUP'.

Is this (Fill All space) a reason to switch primary? Or not ?

View 2 Replies View Related

SQL Server Admin 2014 :: Availability Group Handshaking Fails After Network Outage

Aug 14, 2015

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

Then the handshaking keeps failing, the databases on the replica show as not synchronizing and the only way to fix this is to reboot both primary and secondary.

We keep getting 3520's, etc. on the DR error log

How to eliminate all these prod reboots?

I increased query connection timeouts to 60, but saw no change.

View 0 Replies View Related

SQL Server Admin 2014 :: CDC And AlwaysOn Availability Groups

Apr 2, 2015

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?

View 1 Replies View Related

SQL Server Admin 2014 :: High Availability - Job Error

Aug 18, 2015

I am trying to create a job that runs against my High Availability listener server.

It is a fairly simple SQL statement in the job - execute tsql.

When I try and run the job I get the error:

Executed as user: NT SERVICESQLAgent$SQL2014A. The target database ('BB_Prod') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online. [SQLSTATE 42000] (Error 978). The step failed.

I thought there was a way to run a select statement as a job against the listener? The tsql step is only a select.

Is there a way to pass in the application intent = readonly as part of my SQL statement?

View 2 Replies View Related

SQL Server Admin 2014 :: Separate Max Fields From The Others To Another Filegroup?

Jan 18, 2015

we have many max fields in our database .

A role in performance says : It's better to separate this fields to another FileGroup with separate file.

Is the result good for performance? and what are the risks?

View 9 Replies View Related

SQL Server Admin 2014 :: How Does Security Works Between Availability Groups

May 21, 2014

how does security works between availability groups.

ex if i create an object and grant permissions to a user will that be replicated to secondary replica .

View 4 Replies View Related

SQL Server Admin 2014 :: Load Balancing With Availability Groups

Aug 28, 2014

We are having a conversation at work and the subject of load balancing with SQL came up. Right now we are running SQL Server 2014 on four (4) machines. I am using a AlwaysOn with Availability Groups (AG). Now I know that we can scale out the reading in AG by allowing the secondary serves to receive reads.

Is there a way to be able to do this with writes? Can I have in essences 2 masters that some how reconcile with each other? We are expecting a huge amount of writes in the near future and we need a way for SQL to handle the amount of traffic we are expecting with out any issues.

I explored the possibility of Peer - to - Peer replication; however, it seems that it would be more work if we are constantly making updates to the database scheme.

View 2 Replies View Related

SQL Server Admin 2014 :: Backup Jobs On Availability Groups?

Feb 11, 2015

we currently use Backup Scripts from Ola Hallengren, It Says Full (non copy-only) and differential backups are performed on the primary replica. Full(Copy-only) backups and transaction log backups are performed on the preferred replica.

we currently do FULL(COPY_ONLY) Backup everyday and LOG Backups for every 15 min, is there any performance benefit on running the FULL (non copy-only) on the preferred replica .

View 3 Replies View Related

SQL Server Admin 2014 :: High Availability Index Rebuilds

Jul 9, 2015

I have a situation where I need to rebuild indexes on a large DB (500G).

When I do a test run of the rebuilds in my test environment it uses 100G of space - which is fine with me.

When I do a rebuild in my High Availability environment - same DB, same script - it eats up over 600G of space and fills the volume.

What can I do without removing my DB from H/A to rebuild the indexes?

View 8 Replies View Related

SQL Server Admin 2014 :: Availability Groups - Adding One More Listener

Sep 1, 2015

We have a server A and a server B setup with SQL2014 High Availability - working fine.

I need to add an additional listener to our new DR site.

What is the best way to do this without impacting production - can I just add the additional replica from nodea as a secondary replica?

View 1 Replies View Related

SQL Server Admin 2014 :: Replication On AlwaysOn Availability Groups

Sep 15, 2015

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?

View 1 Replies View Related

SQL Server Admin 2014 :: Reporting Services On Separate Box From Database

Sep 8, 2014

I've got reporting services on a different box from the database and I can see all the reports, but when I try to setup a subscription, I get this weird error:

The SQL Agent service is not running. This operation requires the SQL Agent service. (rsSchedulerNotResponding)

The same error happens when I connect to the database server via management studio and try to run a job.

I can confirm that SQL Agent service is running.

View 1 Replies View Related

SQL Server Admin 2014 :: Database Backups And AlwaysOn Availability Groups

Oct 16, 2014

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 Related

SQL Server Admin 2014 :: Disabling MSDTC For High Availability AlwaysOn

Nov 3, 2015

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 ?

View 0 Replies View Related

SQL Server Admin 2014 :: Separate Transaction Log Files For Multiple Databases?

May 15, 2015

We have multiple databases on a single instance in an OLTP environment. I have my data files on a separate SAN LUN from my transaction log files (and a few NDFs split out onto additional LUNs). I was wondering if there is a performance benefit to putting each LDF file on its own LUN? Or at least my few busiest LDFs?

We are currently on 2012, but I'm having to put together specs for a 2014 installation and need to answer this question without having an environment in which I can benchmark different setups. I just want to hear whether or not others have done this (why or why not?).

View 3 Replies View Related

SQL Server Admin 2014 :: Determine Which Server Is Currently Active For Availability Groups

Nov 25, 2014

I want two write a small script to determine which is the currently active (primary) server in the AG.

Right now, I see that using SELECT * FROM SYS.dm_hadr_availability_replica_states I can determine the role. However, when the server goes down and switches to the secondary node, I don't believe that the role changes (or does it?). How do I determine which is the active node?

View 9 Replies View Related

Recovery :: Setup AlwaysOn Availability Group Between 2 Separate Clusters

Sep 22, 2015

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

SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups

Jan 9, 2015

I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.

View 2 Replies View Related

SQL Server Admin 2014 :: Group Policy User Denied Access

Sep 15, 2014

I have a user, who is trying log into the server, but everytime he gets this error saying something about the Group policy denies him access.

This user needs access and i'm trying to understand how to grant it to him.

I have been looking into how i can access the group policy editor, but the farthest i can get is the Local group policy editor. How do i make sure this specific user has access?

View 1 Replies View Related

SQL Server Admin 2014 :: Re-balancing Blob And Lob Data In Multiple Files On Primary File Group

May 7, 2014

A little background on what I am trying to achieve first. We are moving to Azure virtual machines and we will have 8 disks on the SQL Server box. I am adding more files to the primary file group and each file will go on its own drive. I am then rebalancing data across these files by rebuilding all of the indexes on the tables which is working fine. No problems so far all is good.

I now have an additional problem. If there is a lob or blob column on the table, rebuilding the clustered index and all the non clustered indexes doesn't rebalance the blob or lob data across the disks such as it does with in row data.

I cannot find any articles on rebalancing lob or blob data because all the articles say to move to a new file group. I do not want a new file group, I just want to use the primary file group where the data already resides, and just redistribute it evenly in the same way that I can in row data which is working fine.

One solution I thought about was to BCP data out of the table, truncate the table and then BCP back into the table which I imagine would have the desired effect of distributing the data evenly over the files.

View 2 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 Server Admin 2014 :: Creating Additional Data File For A Particular File Group?

Jul 6, 2015

For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.

Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?

View 5 Replies View Related

SQL Server Admin 2014 :: Can Delete A Data-file Or File-group

Apr 27, 2015

In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?

View 3 Replies View Related

SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out

Aug 27, 2015

I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.

I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.

I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).

So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.

View 0 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 Server 2014 :: Splitting Similar Data Into Separate Columns?

Aug 18, 2015

If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip

Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018

The query I am using

select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip

from Loan AS L

LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'

View 3 Replies View Related

SQL Server 2014 :: Modification Of SSIS Package With AlwaysOn Availability Replica?

Dec 1, 2014

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

View 0 Replies View Related







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