SSMS connecting to Availability Groups

Connecting to Always On High Availability Group using SQL Server Management Studio (SSMS)

1. In SQL Server Management Studio, enter the High Availability Group Listener name.
Then click on the [Options >>] button.

2. On the “Connection Properties” tab, increase the "Connection time-out:"
Default setting is 15 seconds.
Microsoft recommends setting it to 21 seconds per subnet for availability groups.
This setting is available SSMS versions 2005 and up.

3. Click on the “Additional Connection Parameters” tab.
This tab is available on SSMS versions 2012 and up and this is parameter available in SQL Native Clients based on .NET framework 4 and up.
Add an entry for:

MultiSubnetFailover=True

References:
https://msdn.microsoft.com/en-us/library/gg471494(v=sql.110).aspx https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.100).aspx

Client Recovery Latency During Failover

A multi-subnet FCI by default enables the RegisterAllProvidersIP cluster resource for its network name. In a multi-subnet configuration, both the online and offline IP addresses of the network name will be registered at the DNS server. The client application then retrieves all registered IP addresses from the DNS server and attempts to connect to the addresses either in order or in parallel. This means that client recovery time in multi-subnet failovers no longer depend on DNS update latencies. By default, the client tries the IP addresses in order. When the client uses the new optional MultiSubnetFailover=True parameter in its connection string, it will instead try the IP addresses simultaneously and connects to the first server that responds. This can help minimize the client recovery latency when failovers occur. For more information, see AlwaysOn Client Connectivity (SQL Server) and Create or Configure an Availability Group Listener (SQL Server).

With legacy client libraries or third party data providers, you cannot use the MultiSubnetFailover parameter in your connection string. To help ensure that your client application works optimally with multi-subnet FCI in SQL Server 2012, try to adjust the connection timeout in the client connection string by 21 seconds for each additional IP address. This ensures that the client’s reconnection attempt does not timeout before it is able to cycle through all IP addresses in your multi-subnet FCI.
The default client connection time-out period for SQL Server Management Studio and sqlcmd is 15 seconds.

https://msdn.microsoft.com/en-us/library/ff878716(v=sql.110).aspx

Linked Servers/OPENROWSET use OLE DB and OLE DB in the SQL Server Native Client does not support the MultiSubnetFailover keyword.

https://msdn.microsoft.com/en-us/library/gg471494(v=sql.110).aspx

Try a System DSN using ODBC with MultiSubnetFailover specified and then creating the linked server to use this .

Other option is to increase the timeout of the linked server to give it time to work around all the registered IP addresses.

Use the following guidelines to connect to a server in an availability group or SQL Server 2012 Failover Cluster Instance:

- Use the MultiSubnetFailover=True connection property when connecting to a single subnet or multi-subnet; it will improve performance for both.

- To connect to an availability group, specify the availability group listener of the availability group as the server in your connection string.

- Connecting to a SQL Server instance configured with more than 64 IP addresses will cause a connection failure.

- Behavior of an application that uses the MultiSubnetFailover connection property is not affected based on the type of authentication: SQL Server Authentication, Kerberos Authentication, or Windows Authentication.

- Increase the value of Connect Timeout to accommodate for failover time and reduce application connection retry attempts.

- Distributed transactions are not supported.


If read-only routing is not in effect, connecting to a secondary replica location will fail in the following situations:

- If the secondary replica location is not configured to accept connections.

- If an application uses ApplicationIntent=ReadWrite (discussed below) and the secondary replica location is configured for read-only access.

https://msdn.microsoft.com/en-us/library/hh205662(v=vs.110).aspx


Bottom Line:

1. Upgrade clients where possible
2. Set the multisubnetfailover option if using SQLClient in .NET 4.0 and above client libraries and applications
3. Extend connection timeouts to 21 seconds per subnet for legacy connections that cannot be upgraded
4. If the application uses a library that cannot be upgraded, but supports ODBC, then upgrade ODBC and create an ODBC connection that uses the MultiSubmetFailover=True and have application connect using the ODBC connection.
5. If the application uses SQL Client, then can create a SQL Native Client alias that points to primary node (can use a powershell script to change)
6. If all else fails, connect the application to the primary node (will have to be manually changed on failover)
7. Not recommended, but can set RegisterAllProvidersIP to off for the cluster (this could cause some clients to not be able to connect for 20 minutes depending on HostRecordTTL setting)


Additional References:

http://www.madeiradata.com/make-sure-clients-can-connect-multi-subnet-cluster/
http://johnlouros.com/blog/leveraging-multi-subnet-failover
https://blogs.technet.microsoft.com/sqlpfeil/2014/03/31/sql-alias-powershell-script/
http://sqlperformance.com/2013/11/system-configuration/ag-connectivity

@@SERVERNAME returns name of old server

-- in the case where a server has been cloned or renamed 
-- and @@SERVERNAME still returns name of old server

use master
go

SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
GO


SELECT [server_id]
      ,[name]
      ,[product]
      ,[provider]
      ,[data_source]
  FROM [master].[sys].[servers]
GO

sp_dropserver 'OLDSERVER\OLDINSTANCE';
GO
sp_addserver 'NEWSERVER\NEWINSTANCE', local;
GO

-- has been updated in servers table
SELECT [server_id]
      ,[name]
      ,[product]
      ,[provider]
      ,[data_source]
  FROM [master].[sys].[servers]
GO

-- may need to restart instance 
-- for it to take effect for @@SERVERNAME

SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
GO

Installing SQL Server 2000 Desktop Edition

Download MSDE 2000 Release A

This will download a compress executable file MSDE2000A which is SQL Server 2000 Desktop Engine with SP3a (8.00.760)

Executing this will expand to a folder C:\MSDERalA\

From a command line navigate to this folder and execute on of the following:

To install a default instance configured to use the default Windows Authentication Mode, just pass in the SAPWD="AStrongSAPwd" switch where AStrongSAPwd is a strong password for the "sa" account.

C:\MSDERalA\>setup SAPWD="AStrongSAPwd"

To install a named instance include the INSTANCENAME="InstanceName" switch.  To use mixed mode Windows Authentication and SQL authentication use the SECURITYMODE=SQL switch.

C:\MSDERalA\>setup INSTANCENAME="MyInstance" SECURITYMODE=SQL SAPWD="AStrongSAPwd"


To start SQL Server, go to Start->Run and type sqlmangr.exe to bring up the sql server service manager.  You can start the service using it and set it to start when OS starts.

Now open a command prompt and attempt connecting using osql

C:\>osql -E


Resources:
How to obtain and install SQL Server 2000 Desktop Engine (MSDE 2000)
https://support.microsoft.com/en-us/kb/324998
http://support.microsoft.com/default.aspx?scid=kb;en-us;810826
http://support.microsoft.com/kb/322336/EN-US/

what version of .NET?

To find .NET Framework versions by viewing the registry (.NET Framework 1-4)

1. On the Start menu, choose Run.
2. In the Open box, enter regedit.exe.
3. In the Registry Editor, open the following subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP


To find .NET Framework versions by viewing the registry (.NET Framework 4.5 and later)

1. On the Start menu, choose Run.
2. In the Open box, enter regedit.exe.
3. In the Registry Editor, open the following subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full


https://msdn.microsoft.com/en-us/library/hh925568(v=vs.110).aspx#net_a