Connection String Stability

Previous SQL Server Version and Edition	Large Scale Deployment Overview Next

The term Connection String Stability relates to avoiding changes to the connection strings used to connect to SQL Server regardless of changes in the underlying SQL Server topography.

The problems associated with changing connection string syntax often restrict the scope of topography changes that can be made, which often leads to slow adoption of new versions of SQL Server with a corresponding loss of opportunity to the business when new features cannot be used.

The key to having connection string stability is the use of DNS Aliases. These form an abstraction layer between the application or user and SQL Server, and therefore allow almost any change to SQL Server topology to be made without having any impact on whatever is using SQL Server. Keeping connection string stability allows all of the following to happen without any impact on how applications or users connect to SQL Server:

• Server names and SQL instance names can change
• Servers can become clustered or unclustered
• Availability Groups can be added or removed
• SQL instances can move between hosted on-premesis or in the cloud

The following sections look at how to plan and implement connection string stability:

Item	Description
SQL Server Connection Strings	Connection strings for SQL Server databasee engine
Analysis Services Connection Strings	Connection strings for Analysis Services
Reporting Connection Strings	Connection strings for Reporting

Top

---

SQL Server Connection Strings

Separate DNS Aliases should be set up for SQL Server database engine to manage Business Contingency (Disaster Recovery) and to manage application connectivity.

This combination of BCO and application aliases will facilitate SQL Server consolidation and dispersal, both in and out of cloud infrastructure. All of this can be achieved without any connection string changes being needed at the application level.

The examples shown below contain a very small number of servers and applications. In practice most organisations would have significantly longer lists of these items.

BCO Aliases

The BCO aliases are used to simplify the BCO Failover process. It is likely that a large number of applications will be hosted on a single SQL Server instance, and having a only single alias that needs to be changed at failover time will both speed up the failover process and reduce risk that an application may not be connected to its BCO location.

When an organisation has performed SQL Server Topology analysis, it will result in an inventory of SQL Server instances grouped by physical location and how they are related to each other for BCO purposes.

Each of the groups identified above will be assigned a DNS Alias. The target for each BCO alias should be set to the active server in a BCO pairing. This could result in a table similar to the one shown below:

Where Distributed Availability Groups (dAG) are used to synchronise data between the primary and the BCO location, it can aid simplicity if the dAG name, the BCO group name and the BCO alias name are the same.

Server	Location	BCO Group	Environment	DNS Alias
PDGB01SQLC01AOA	GB01	ProdBCO-SQL1	Prod	ProdBCO-SQL1
PDGB02SQLC02AOA	GB02	ProdBCO-SQL1	Prod
PDGB01SQLC03AOA	GB01	ProdBCO-SQL2	Prod	ProdBCO-SQL2
PDGB02SQLC04AOA	GB02	ProdBCO-SQL2	Prod
DVGB01SQLC05AOA	GB01	DevBCO-SQL3	Dev	DevBCO-SQL3
DVGB02SQLC06AOA	GB02	DevBCO-SQL3	Dev

Application Aliases

The application aliases are used to link a given application to a specific BCO group. It is likely that a large number of applications will be hosted on a single BCO group.

When an organisation has performed Service Level Tiers analysis, it will result in an inventory of applications grouped by how they are related to each other and by Service Level.

Each of the groups identified above will be assigned a DNS Alias. The target for each application alias should be set to the appropriate BCO alias. This could result in a table similar to the one shown below:

Application	Group	Service Level	Environment	DNS Alias	Target
Dynamics	CRM	Gold	Production	ProdSQL-CRM	ProdBCO-SQL1
SelectHR	HR	Gold	Production	ProdSQL-HR	ProdBCO-SQL1
SelectPayroll	HR	Gold	Production	ProdSQL-HR	ProdBCO-SQL1
ERPSystem	ERP	Silver	Production	ProdSQL-ERP	ProdBCO-SQL2
Dynamics	CRM	Gold	Development	DevSQL-CRM	DevBCO-SQL3
SelectHR	HR	Gold	Development	DevSQL-HR	DevBCO-SQL3
SelectPayroll	HR	Gold	Development	DevSQL-HR	DevBCO-SQL3
ERPSystem	ERP	Silver	Development	DevSQL-ERP	DevBCO-SQL3
AppDev	DEV	Silver	Development	DevSQL-Dev	DevBCO-SQL3

Connection String Configuration

The connection strings used by each component in an application will need to be updated to use the application alias. This is a one-off change, and afte rthis is made then the underlying SQL Server topology can be significantly changed without requiring any further changes to the connection strings used by an application.

1. Example: Moving the databases for a given application to a new host:

   • Make the databases required by the new application available on the new host
   • Change the target of the relevant application alias to point to the new BCO alias
   • Note that no change has been needed to the connection strings used by the affected application

2. Example: Performing a BCO failover:

   • Perform any required steps to make the BCO servers the active server in a BCO set
   • Change the target of the relevant BCO alias to point to the new active server
   • Note that no change has been needed to the connection strings used by affected applications

Top

---

Analysis Services Connection Strings

The same concept of BCO aliases and application aliases that was used for SQL Server database engine should be used for Analysis Services. The alias names used for Analysis Services must all be different to those used for SQL Serve rdatabase engine.

Connecting to Analysis Services from Reporting

Where Reporting (SSRS or PowerBI Server) are connecting to Analysis Services, it is important to use the SSAS BCO alias name for this connection.

When Reporting (SSRS or PowerBI Server) connects to Analysis Services, it generates a SPN based on the name given in the connection string. This behaviour is different to all other uses of aliases within SQL Server, where the alias gets resolved to the ultimate target name before generating a Service Principal Name (SPN) request.

The BCO alias for the SSAS instance should be provided in a /ASAlias: parameter, which will allow SQL FineBuild to set up the required SPN and delegation for the SSAS BCO alias.

Top

---

Reporting Connection Strings

It is possible to provide a stable connection string to end users for the organisation's reporting environments. As with the other types of connection string, this will allow the physical topology of the reporting environment to change without any impact on how end-users navigate to that environment.

The infrastructure needed to make this work is slightly complex, but SQL FineBuild can configure everything that is needed. The configuration that is needed is described below:

1. Create Reporting DNS Alias

   A separate DNS alias is required for each reporting environment. The target for the alias should be the BCO alias for the SQL Server instance hosting the ReportServer database

   The alias name used should be suplied to SQL FineBuild using the /RSAlias: parameter, for example /RSAlias:ACMEReports

   The end result is that when a user enters //ACMEReports in their browser, a http request will end up on the active server of the BCO Group refreenced by the target of the ACMEReports alias

2. Install IIS on all servers in the BCO group

   SQL FineBuild will do this automatically if it detects a value for the /RSAlias: parameter

3. Edit the DEFAULT.HTML file (the Default Document) for the default website

   This will be set contain the full URL to get to the report server. The processing performed by SQL FineBuild for this is described in Setup Report Service IIS Alias

4. Configure the Report Server to respond to the Reporting DNS Alias

   This applies to both SSRS and PowerBI Server. Use Report Services Configuration Manager to respond to the URL specified in step 3 above

   SQL FineBuild will do this for you automatically

5. Configure the response URLs in the rsreportserver.config file

   The UrlRoot and the ReportServerUrl nodes should be set to the URL configured in step 3 above

   SQL FineBuild will do this for you automatically

It should now be possible for a user to enter //ACMEReports in their browser and be presented with the reporting web portal.

Copyright FineBuild Team © 2021. License and Acknowledgements

Previous SQL Server Version and Edition	Top	Large Scale Deployment Overview Next