A Fine Slice Of SQL Server
Sreekanth Bandarla is ready to make a change:
In this blog post, let’s see how to change all the IP addresses involved in a typical Always on Availability group configuration. In my setup, I have an AG with two replicas and a listener. See below to get an idea of my current environment on which I am going to change all the underlying IP addresses.
Click through for a step-by-step process, as well as a few things to remember.
Comments closedChad Callihan ran into an error creating a new database:
A tool that restores a model type database and does a bit of configuration work was failing. I took a look at the stores procedures and started to go step by step. It didn’t take long before getting this error message when attempting to restore/create a database:
Msg 5184, Level 16, State 2, Line 3
Cannot use file ‘D:\sql_log\CC_Test_name_4.ldf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Click through for the solution.
Comments closedAlways On Availability Groups can support up to nine availability replicas, and while we don’t use anywhere near that many replicas in each of our clusters, we do have 2 replicas per cluster (3 servers total), with the replicas being used as a readable secondary.
Since we use readable secondaries in our environments, the application needs to connect to both the primary and the secondary servers with the same login. The catch is, logins don’t automatically sync across replicas. If the logins don’t sync, the application won’t connect to a secondary, which results in login failures.
Read on for one way to solve the problem.
Comments closedRajendra Gupta continues a series on Availability Groups:
In the previous article, Explore dynamic management views for monitoring SQL Server Always On Availability Groups, we explored the DMV’s for the Availability group for Windows Failover Clusters.
This article takes a further step and covers useful DMV related to availability replica and databases. Let’s start our journey with this article.
Click through for the article.
Comments closedKendra Little has some considerations for us:
One question which comes up periodically from our Microsoft Data Platform customers is whether there is anything special that they need to do when implementing version control, continuous integration, and automated deployments for a production database which is a member of a SQL Server Availability Group.
The good news is that deployments are quite straightforward. You’ve most likely already configured a listener for your applications to connect to the Availability Group and automatically find the writeable database. To do a deployment, you simply need to connect to the listener as well.
There are a few other considerations which are helpful to think about when building your approach to database DevOps, however.
Check out the article for the details.
Comments closedSQL Server Always On uses the Policy-Based Management(PBM) for determining its health. In the earlier articles, we discovered AG dashboard features to monitor synchronization status, data loss, replica states. It executes the PBM policies on availability replicas (primary and secondary), availability group database and organizes the results in a dashboard.
The primary replica contains information for all replicas, their synchronization states. It has sufficient information to compute the health for all availability groups. If we launch the AG dashboard from the primary and secondary replica, we can note the difference in monitoring.
This is perhaps the most under-developed and under-utilized feature in SQL Server. It is conceptually so powerful and what’s in there shows some of that power, but to this day, so much is still lacking in PBM.
Comments closedWith SQL Server 2012 Microsoft introduced the AlwaysOn Availability Group feature, and since then many changes and improvements have been made. This article is an update to our previous article https://www.sqlrx.com/steps-for-installing-sql-server-alwayson-availability-groups/ , and will cover the prerequisites and steps for installing AlwaysOn in your SQL Server 2019 environment.
Click through for a checklist of pre-requisites and installation + configuration steps.
Comments closedIn SQL Server 2016 Microsoft introduced Automatic Seeding for Availability Groups (AG). The Automatic Seeding process streams the database files directly to the secondary server(s) using the database mirroring endpoints, removing the need to restore the databases, before joining them to an AG.
Read on for Jamie’s thoughts and notes.
Comments closedAndy Mallon continues a line of thought:
In yesterday’s post, I showed how to configure an availability group (AG) to use the
RegisterAllProvidersIP=0when you can’t get clients to connect using theMultiSubnetFailover=trueconnection string attribute.I mentioned that you have to make some trade-offs when you set
RegisterAllProvidersIP=0, and included this comparison:But….when if you can eat your cake and have it, too?
In some cases, you’ll have some applications & clients that are not able to use
MultiSubnetFailover=true, and other clients that can. Perhaps you’re working on updating a bunch of legacy Java apps to move from old jTDS drivers to the current Microsoft JDBC drivers that properly supportMultiSubnetFailover=true. Parts of your codebase have been updated, and you want them to make use of the connection string attribute for fast cross-subnet failover. But other parts of your codebase are still being updated and rely on theRegisterAllProvidersIPcluster parameter to be false. Wouldn’t it be nice to have both?
Read on to learn how.
Comments closedAndy Mallon takes us through an all-too-common scenario:
With a default configuration, multi-subnet AGs require that the clients connecting to them include
MultiSubnetFailover=trueas a connection string attribute. This attribute tells the driver to expect DNS to provide multiple IP addresses for the Listener name, and to try all of them to find the correct IP to connect to for that network name. Clients that do not specify this attribute will get multiple IPs and not know how to handle them properly–most drivers will pick up one of the returned IPs at random (or maybe just seemingly random), and try to connect to that. This can result in random (or seemingly random) connection failures when it picks the wrong IP.However, not every client or application will support this connection string attributes. In my experience there are two extremely common reasons that you can’t use
MultiSubnetFailover=true:
Read on to see what you can do in that case.
Comments closed