I would like synchronizing all the missing objects (logins, agent jobs, SSIS, and anything else that I've missed) across SQL 2012 Availability Groups.
I would like to be able to able to automate this process....
I been trying to learn availability groups since I have not implemented it.
From my understanding you can have more than one group.
Lets pretend we have two groups in one instance:
1. Accounting
2. Engineering
From my understanding you can't make a database in two AG because it wouldn't make sense.
But lets pretending there is one database that both are used by accounting and engineering.
Would you have to make a third AG for future fail overs so that other databases in the other two group don't failover when not needed because when you fail over an AG all the databases inside it fail over.
From what I understand, one SQL Server 2012 instance can host multiple availability groups. That should allow, for example, the following situation - one SQL Server 2012 instance containing three primary replica databases - each one part of a different availability group - and each availability group's secondary replica located on a separate SQL Server 2012 instance.
Can you have three SQL Server 2012 instances, each with one primary replica database and have all three secondary replica databases on one SQL Server 2012 instance? So instead of, as above, going from 1 primary server to 3 secondary servers, this time we're going from 3 primary servers to 1 secondary server? The one secondary server would then contain all three secondary replicas for each of the 3 separate primary replicas.
This would mean that the single server (where all three secondary replicas reside) was part of three separate Windows Server Failover Clustering clusters.
Is this scenario possible?
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 RelatedI have several 2012 availability groups running on a cluster. I have one database that is bulk loaded every 30 minutes. The DB is about 1 GB in size. To be on the availability group it has to be set to full recovery mode, but simple or even bulk would obviously be better. Is there a better way to handle the transaction log size other than to run a backup after each bulk load causing extra overhead? With mirrors you could use simple, but since those are going away . . .
View 2 Replies View RelatedWe have some tables that are bulk-loaded every day and they do not have RI to the other tables in the database.
To ease pressure on the logs, I had the idea of spinning them off to another database on the same AG in simple or bulk-load recovery model and using synonyms to point to them so the code base would not need changing.
I know an earlier bug in 2005 existed that basically made the query analyzer ignore indexes if a table was accessed via a synonym.
We 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'm reading up on Always on and I am confused what is the difference between AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances.
View 3 Replies View RelatedApproach 1:
Prod - shared storage between server 1 and 2
Server1: clustered SQL instance with availability group as primary
Server2: Passive server for clustered instance of PROD
DR - shared storage between server 1 and 2
Server1: Clustered SQL instance with availability group as replica
Server2: Passive server for clustered instance of DR
Approach 2: Using replicated SAN
Prod -
Server 1: Standalone instance with availability group as Primary
Server 2:Standalone instance with availability group as replica
DR -
Server 1: Offline until Disk group 1 (Prod server 1) has been broken and brought online at DR
Server 2: Offline until Disk group 2 (Prod server 2) has been broken and brought online at DR
Both these approaches will work wont they? I have only built and played with normal availability groups across servers, not mixing it with clustered instance replicated SAN
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.
Can we set up always on availability groups in server 2012 standard edition.
View 3 Replies View RelatedI'm looking at using Cluster Shared Volumes on a new Windows Server 2012/SQL Server 2014 cluster. Each instance is going to be configured to use cluster shared volumes. Is there any reason why Availability Groups couldn't be used in conjunction with Cluster Shared Volumes.
View 4 Replies View RelatedI am planning to have AlwaysON Availability Group 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 3 -->Subscriber-->2012 SQL Enterprise edition-->Windows Std 2012 --> NOT involved in Always ON
Server 4 -->Subscriber-->2012 SQL Standard edition-->Windows Std 2012 --> NOT involved in Always ON
Server 5(VM)-->Distributor-->2012 Standard edition-->Windows Std 2012-->not involved in Always ON
i know Publisher and subscribers can be mix match of 2014 and 2012 rom the above scenarios can i use (VM)SQL2012 std. as Distributor ? while publisher is being SQL 2014 ent.?
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?
We are trying to build HA and DR environment, combining AG and FCI. So we have 3 servers added to a cluster, 2 of them sharing the same storage and working as ACTIVE-PASSIVE cluster instance. The other server has a stand alone instance with its local storage, with the same drivers letters as the cluster instance.
Between the Active FCI and the Stand Alone, we set up an Availability Group. They are running fine, except by the listener. When the stand alone instance owns the Primary Replica, I am able to access the SQL Server instance by the listener, but when the Primary Replica is owned by the FCI, I can't reach the database by the listener (name or IP).
I've opened the same ports in both servers, it hasn't worked though.
I'm doing a certification process using AlwaysOn, and was using the link below, and on the lower 90 hotfix, and instead of downloading one by one, and then upgrade one by one updates, is there any way to make it more faster or practical, or unfortunately have to do this one by one, so the download as the update? This rollup contains the latest version of the Windows system files that are updated after the release of SP1. URL...
View 2 Replies View Relatedhow 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 .
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.
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 .
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?
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 RelatedI 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 RelatedServer:
Sql 2005 SP2 running on Win2003 Ent. SP1
I have a 3rd party app that needs me to add a user to the SQLServer2005SQLAgentUser group for the instance I placed the DB in. This group doesn't exist on my server for any of my instances. Is there a simple way to generate it or a document that can tell me how to manually create it?
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
I have a clustered server environment where three of the SQL Server local groups were not created during installation. One of these missing groups is SQLServer2005SQLAgentUser$ComputerName$InstanceName, and this is causing problems with trying to automate replication.
How do I create this group after I have installed SQL Server?
Thanks,
Neal
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?
I need help in anyone with this problem.
I started out with trying to get to know the existing SQL Servers (6.5 and 7.0). I am missing some Performance Monitor objects (ie SQL Server: Buffer Manager, SQL Server: General Statistics, etc). I am surprised that it is missing.
A typical SQL Server setup:
* NT 4 with SP6
* SQL Server 7 (standard) with SP2
* 2gig Mem
* Over 50gig HD
Does any of you ever have this problem? Any solutions? Any answer would be greatly appreciated.
Thanks.
mjt
Greetings:
I am trying to gather into a central location the missing index data from the sys DMV's for dynamic index creation in the next step. In trying to use a cursor, I get the following errors:
Msg 154, Level 15, State 3, Line 20
variable assignment is not allowed in a cursor declaration.
Msg 102, Level 15, State 1, Line 94
Incorrect syntax near 'Get_Data'.
Msg 16916, Level 16, State 1, Line 2
A cursor with the name 'Get_Server' does not exist.
Msg 16916, Level 16, State 1, Line 3
A cursor with the name 'Get_Server' does not exist.
Here is the SQL:
--CREATE PROCEDURE usp_Get_Missing_Index_Data
--AS
--Declare @Sql2 nvarchar(4000)
Declare @Sql nvarchar(4000)
DECLARE Get_Server Cursor -- gets a server name from a list of servers
for
Select MachineName from rsqlaudit1.DBStatistics.dbo.servers
Open Get_Server
Declare @Server nchar(20)
Fetch Next from Get_Server Into
@Server
While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)
BEGIN
DECLARE Get_Data Cursor
FOR
select @sql= 'select distinct id.*
, gs.avg_total_user_cost
, gs.avg_user_impact
, gs.last_user_seek
,gs.unique_compiles
from '+@Server+'.master.sys.dm_db_missing_index_group_stats gs
,'+@Server+'.master.sys.dm_db_missing_index_groups g
,'+@Server+'.master.sys.dm_db_missing_index_details id
where gs.group_handle = g.index_group_handle
and id.index_handle = g.index_handle
order by gs.avg_user_impact desc'
exec (@Sql)
Open Get_Data
DECLARE @Handle int,
@database smallint,
@object int,
@equality nvarchar(4000),
@inequality nvarchar(4000),
@Included nvarchar(4000),
@statement nvarchar(4000),
@avg_user_cost float,
@avg_user_impact float,
@last_seek datetime,
@compiles bigint
Fetch NEXT FROM Get_Data INTO
@Handle,
@database,
@object,
@equality,
@inequality,
@Included,
@statement,
@avg_user_cost,
@avg_user_impact,
@last_seek,
@compiles
While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)
BEGIN
insert into rsqlaudit1.DBStatistics.dbo.Missing_Index_data
values (@Server,
@Handle,
@database,
@object,
@equality,
@inequality,
@Included,
@statement,
@avg_user_cost,
@avg_user_impact,
@last_seek,
@compiles)
FETCH NEXT FROM Get_Data into
@Server,
@Handle,
@database,
@object,
@equality,
@inequality,
@Included,
@statement,
@avg_user_cost,
@avg_user_impact,
@last_seek,
@compiles
Fetch Next from Get_Server Into
@Server
END
CLOSE Get_Data
DEALLOCATE Get_Data
GO
CLOSE Get_Server
DEALLOCATE Get_Server
GO
Any suggestions are appreciated.
Thanks,
Derek
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