New answers tagged

5

Where do you see an explicit recommendation of 8 files? This is certainly not universal. The guidance is 1 file per core, up to 8 cores. If you have more than 8 cores, start with 8, and only add more (in groups of 4) if you actually observe the type of allocation contention that can be relieved by additional files (see this post for a way to determine this. ...


5

You pretty much always want more than a single TempDB file. I'd certainly not go above 8 files in most situations; and probably more like 4. Having said that, the only way to know the best configuration is to test it. This applies equally to SQL Server instances of all kinds, including high availability setups, not just AlwaysOn Availability Groups.


8

You should use smart index maintenance scripts as opposed to maintenance task. Should I run the job in primary or in replica? Yes, as they get replicated on secondary replicas. If I run it only on secondary then what benefits do I get? you cannot run on secondary replica. Index maintenance generate a lot of transaction log activity that affects ...


6

Putting in a simple language : A login gives you access to server. A user gives you access to database. A login must map a user so that you can access the database. For SQL Authentication, you should create the login with SAME SID and map it to your user database/s on all the nodes with proper permissions (follow the principle of least privileged). This ...


0

It appears that the dates you are clustered on are not being inserted sequentially. If your date field was recent purchases for example they would be inserted at the 'end of the table'. Because it is taking so long to do the insert it seems that SQL is having to reorder your data with each insert instead of tacking it on the end. Determine if your ...


0

Were you able to get this sorted out? If not , try the following: Go to the Failover Cluster Manager ( cluadmin.msc from Run). Expand the Cluster name ( Expand meaning click on the + sign ). Expand Services and Applications. You should now see your Availability Group listed there. R-click on that and go to Properties. In the General Tab you should see ...


1

This is login and permission related error. What kind of account are you using to connect when you build the AOA features? If you are using a domain account you can fix this error it in two ways. Use the same domain account on all of the participating replicas. If you can't (allowed) change the account you could use the second option Add the service ...


2

The more common and accepted way is that once a job lands on a server in an AG you start editing it: Add a first job step that checks whether this is the primary. Exit on success if it isn't. Otherwise continue onto the normal job step. This has been explored in a few other SE questions (they don't specifically relate to CDC which is why you didn't find ...


0

on a HA scenario, how does the primary server get updated once it is again available ? The primary server is always where the write workload goes. If you're asking how the replica gets updated once it transitions from Primary->Secondary the answer is it finds the primary and checks in with its' LSN. There is a negotiation that goes on and depending on ...


1

Yes, Windows 2016 supports Workgroup and Multidomain clusters. For Workgroup nodes and Multidomain cluster nodes should be able to ping and communicate. https://blogs.msdn.microsoft.com/clustering/2015/08/17/workgroup-and-multi-domain-clusters-in-windows-server-2016/ and SQL server is supported including hyper-v and file server.


2

This sounds like you might have a long running transaction on the primary replica. Run this query to find any transactions that have been running longer than 10 mins: Select B.session_id, A.transaction_id, C.transaction_begin_time, DATEDIFF(Second,C.transaction_begin_time,getdate()) TimeTaken_In_Seconds, B.HOST_NAME, B.program_name, ...


3

Can anyone shed some light on this behavior? The behavior you're describing is not working as described. The end result is the same but the inner workings are different than what you're thinking. The Availability Group for this specific DB is synchronized and healthy. I had to add a second transaction log file (logfile2) to the primary. The ...


1

The scenario is called out and supported on the link you've provided. Availability Group with One Remote Secondary Replica If you have deployed an availability group only for disaster recovery, you may need to fail over the availability group to an asynchronous-commit secondary replica. Such configuration is illustrated by the following figure: ...


0

I have 2 SQLServers at production site Windows Server Failover Cluster and 1 SqlServer at DR site. I will add the DR node to existing windows cluster and configure AOAG.So for this should I go for File share witness or Disk Witness for Quorum Configuration for this AOAG set up . Generally when there are geographically distributed clusters, having a disk ...


3

Technically, yes, you should still be able to read from the DR instance assuming it is configured as a Read Only replica and you are connecting to it directly (As read only routing through the primary would not be working). You should really be concerned in this case though about bringing the DR site online as the new primary replica. You would need to ...


3

But now it needs upgrade to 2012 I would suggest you to go with SQL Server 2014, unless you have a compelling reason to stick with SQL Server 2012. AlwaysON is getting a lot of fixes back ported from SQL Server 2016. we like to do AlwaysON but its mirroring behind AlwaysON so will it work or is there any other way ? This is not true. The ...


1

I see your point on not feeling the FCI usefull in this kind of setup. You could have 2 good servers on FCI with shared storage sharing the dbs and then automatic Failover and then configure the AGs for some of the databases to be replicated on a third sercer with not so good specs just to work as a read only replica and also allowing to balance some ...


2

You can have AGs within an FCI but I don't see the point (I mean when you do this you usually do away with the FCIs unless there was some specific requirement). In an AG the listener acts similar to a load balancer - kind of. If applications support using application intent read only in the connection string it can offload reads to the "passive" server. ...


1

If you use Availability Groups you must do log backups. If you use Hallengren's backup solution then you'll need to set it up on both the primary and secondary node(s). Then direct the jobs it creates to point to the same network share. What will happen next is the script will create one directory for each server to hold system databases and any databases ...


0

Aparently this is practically impossible to do in an "clean" way. I have a open support ticket with Microsoft Premier, and the answer was that the only way is to create a trigger in the logon procces to disconnect users that are attempting to connect to the primary node, but there is no way to redirect that connections to the secondary automatically. So, I ...


0

Rather than doing this on a per job basis (checking every job for the state of the server before deciding to continue), I've created a job running on both servers to check to see what state the server is in. If its primary, then enable any job that has a step targeting a database in the AG. If the server is secondary, disable any job targeting a ...


1

Ste, what you are wanting is a point and click solution or a setting somewhere. Unfortunately, none of these currently exist in "off the shelf" form. It would be realllly nice if Microsoft included this in the connection settings for a Registered Server so it could be saved, but alas they do not. This leaves you with one of two options: Write Something ...


0

I think you're asking if you need to reinstall SQL Server on the existing node you're adding to the cluster. If it's just availability groups, and you want to add an existing instance, this is easy. You just add the computer to the cluster and enable AG in SQL Configuration Manager. It's different to the old Failover Cluster Instances where you had to ...


3

Isn't "everything" that happens in Primary replica, replicated to the Secondaries? Yes, stats created on Primary replica will replicate to secondaries. The secondary is a strict replica (copy) of primary database. So, the stats are created in tempdb linked with the readonly database. SQL server will maintain statistics of read-only secondary databases ...


3

Isn't "everything" that happens in Primary replica, replicated to the Secondaries? If so, statistics changes that happen in Primary would be replicated into secondary isn't it? You are correct. Can you please help me understand why there is a need for SQL Server to maintain statistics for DBs that are part of Always On availability group in tempdb? ...


2

If I install SQL Server FCIs in Production environment(2 node) can i set one secondary to synchronous mode automatic failover and DR(Stand-Alone) server to Async mode? If you use a SQL Server FCI with Availability Groups, you cannot have automatic failover of the AG but you still get automatic failover of the FCI. The FCI will be one instance and ...


2

I don't have an answer to your entire question (though I gave an answer to a similar question today http://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=ReadOnly doesn't work properly. Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag ...


1

It's possible but the client won't like it. Disable automatic failover of the AG. Keep applications using the listener. Remove all user logins from the primary. Setup user logins only on the secondary. Provide the secondary node name directly to the users instead of the listener. Failover manually when required (if it stays that way then you'll then need ...


2

There is no way to complete this with just SQL Server and the interfaces it exposes. In order to do this you'll either need an appliance between the clients and SQL Server (the users connect to the appliance endpoint and the appliance does the redirection) or you can implement your own through a cname and some type of configurable load balancer like an F5. ...


0

Regarding Asynchronous and Synchronous connections, this is not about speed of the connection, but about the results of the synchronization. Syncronous - The purpose is to make sure that the Primary server and the synchronously updated Secondary server are kept in synch. The transaction is only complete once the Primary and Secondary server transactions ...


3

There are a number of different factors involved but it is entirely possible to have a replica that is synchronous be faster or slower than an asynchronous one. To list a few: Replica is under too much load Hardware specification differences (i.e. synch runs ssd and async runs rotational) or synch is using 8 cores @ 3.6 GHz and async is using 4 cores @ ...


1

There is no reason to move the cluster core resources. They are NOT part of the availability group and function entirely on their own. There is no need to even have these on the same server as any of your AG resources. The core cluster group provides the administrative point for the cluster which is completely independent from any other resources on the ...


0

Currently Invoke-Sqlcmd does not support the ability to connect to a read-only secondary replica. The SQL Server, and some of the PowerShell community, however started working with the Program Manager at Microsoft to get SQLPS and SSMS improvements organized. A Trello board was opened in the past month that we are showing by votes what priority we want ...


0

The solution is quite simple. The issue is that the secondary node is set to 'Readable'. This locks the log file and any operation to shrink it will always fail with error 'The log for database [databasename] cannot be shrunk until all secondaries have moved past the point where the log was added. Change the configs for the AG and make the secondary ...



Top 50 recent answers are included