SQL server Replication error : The process could not connect to Distributor 'server\instance'.

 Issue :

SQL Server replication distribution job is failing with error " The process could not connect to Distributor  'server\instance' "

Cause : There can be different reasons for the error. 

Possible causes :

-> SQL Distributor server is not running, Distributor DB is not online, 

-> Distribution agent service account does not have sufficient access to Distributor DB

-> SQL Distribution instance is not reachable

-> Distributor to publisher linked server not working

Solution :

In our case, distributor agent is running on subscriber end as its pull replication. We started verifying distribution DB SQL instance status and DB status, both verified to be up and running.

Verified distribution agent account is having sufficient access to authenticate distributor DB SQL instance.

Verified Distributor to publisher linked server connection is fine.

However when we tried to connect distributor instance from subscriber side, connection failed. We observed distributor instance is running on non default port (for example 6789).  But when we tried to connect with explicit port number the connection is successful. 

We made sure SQL browser running as SQL port is non default. So the next step we tried to check the SQL browser service port 1434 connectivity between distributor and subscriber server which was closed.

Hence we have got the port opened between the servers and distribution job started running fine.

Another workaround, if port 1434 is not open and server is running on non default port then you can edit your distributor job properties to explicitly port.

For example :

-Subscriber [SubscriberServerName\instance] -SubscriberDB [SubscriberDBName] -subscribersecuritymode 1 -Publisher [PublisherServerName\instance] -Publication [PublicationName] -PublisherDB [PublisherDBName] -SubscriptionType 1 -Distributor [DistributorServerName\instance,6789] -DistributorSecurityMode 1 optional_command_line

Failed to retrieve VerSpecificRootDir for syssubsystems population

Error Messages:

Failed to retrieve VerSpecificRootDir for syssubsystems population

SQLServerAgent could not be started (reason: Failed to load any subsystems. Check errorlog for details.)

Reason:

As a part of SQL Agent start up process, process executes the stored procedure msdb.dbo.sp_verify_subsystems to read the registry values. If the stored procedure throws the execution error, then agent will go to stop state. You can identify the failure step from Agent .out error log file.

Below is the part of stored procedure msdb.dbo.sp_verify_subsystems code, where it is trying to read the registry values

IF ( (@syssubsytems_refresh_needed=1) OR (NOT EXISTS(select * from syssubsystems)) )

  BEGIN

     EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

                  Sep 24 2019 13:48:23

                  Copyright (C) 2019 Microsoft Corporation

                  Enterprise Edition: Core-based Licensin

g (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)', N'VerSpecificRootDir', @VersionRootPath OUTPUT

     IF @VersionRootPath IS NULL

     BEGIN

       RAISERROR(14659, -1, -1) WITH LOG

       RETURN(1)

     END

     EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

                  Sep 24 2019 13:48:23

                  Copyright (C) 2019 Microsoft Corporation

                  Enterprise Edition: Core-based Licensin

g (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)\SSIS\Setup\DTSPath', N'', @DtsRootPath OUTPUT, N'no_output'

     IF (@DtsRootPath IS NOT NULL)

     BEGIN

       SELECT @DtsRootPath  = @DtsRootPath  + N'Binn\'

       SELECT @DTExec = @DtsRootPath + N'DTExec.exe'

       CREATE TABLE #t (file_exists int, is_directory int, parent_directory_exists int)

       INSERT #t EXEC xp_fileexist @DTExec

       SELECT TOP 1 @DTExecExists=file_exists from #t

       DROP TABLE #t

       IF ((@DTExecExists IS NULL) OR (@DTExecExists = 0))

         SET @DtsRootPath = NULL

     END

     SELECT @ComRootPath  = @VersionRootPath  + N'COM\'

     DECLARE @edition nvarchar(256)

     DECLARE @bitness int

     SELECT @edition = @@version

     SET @bitness = CASE WHEN @edition like '%(X64)%' THEN 64 ELSE 32 END

     IF @bitness = 64

         EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Wow6432Node\Microsoft\Microsoft Sql Server\Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

                  Sep 24 2019 13:48:23

                  Copyright (C) 2019 Microsoft Corporation

                  Enterprise Edition: Cor

e-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)\Tools\ClientSetup', N'SQLPath', @ToolsPath OUTPUT

     ELSE

         EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

                  Sep 24 2019 13:48:23

                  Copyright (C) 2019 Microsoft Corporation

                  Enterprise Edition: Core-based Lice

nsing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)\Tools\ClientSetup', N'SQLPath', @ToolsPath OUTPUT

We can see from registry editor that above highlighted path does not exist

Solution:

Alter the stored procedure to update the correct registry path:

In the code  N'VerSpecificRootDir’ exists under the registry path \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130

Hence update the code as

     EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\130', N'VerSpecificRootDir', @VersionRootPath OUTPUT

Similarly update all the paths to the correct locations. 

How to Create and Drop Database in SQL server

Create Database:

There are several ways to create and drop a database in SQL servers using GUI or TSQL. Here I will show in both ways how to do it.

1)GUI - SSMS

Connect to SQL server using SSMS

Right Click on Database and click on "New database"

Provide the DatabaseName

also, you have the options to configure the database
->Restrict the DB size(Datafile and Logfile size)

->Set the DB owner

->Set the Recovery model
->Set the compatibility mode
->Set the collation

Click OK

Refresh the Database and you can see TESTDB created

2)T-SQL

Connect to SQL server using SSMS
Click on New Query and query to create new DB is

use [master]
go
Create Database [DatabaseName] -- Replace the [DatabaseName] with TESTDB
go

and click Execute

Drop Database:

1)GUI- SSMS

Connect to SQL server using SSMS

Expand Database section

Select the Database and right click
click on Delete

Click OK

2)T-SQL

Connect to SQL server using SSMS
Click on New Query and query to drop DB is

use [master]
go
Drop Database [DatabaseName] -- Replace the [DatabaseName] with TESTDB
go

and click Execute

SQL 2016 Developer Edition Installation Step by Step

SQL 2016 Developer Edition Installation Guide Step by Step

Download SQL 2016 developer edition setup file from the Microsoft Site.

Extract the downloaded setup file to a local folder

Run the setup.exe as administrator

Once the SQL server installation center opens, then click on the installation and choose New SQL Server stand-alone installation or add features to an existing installation

Follow the below steps for installation

• Microsoft Update: check the box of 'Use Microsoft Update to check for updates' and click next

• Install Rules: Check for any errors in the window and if you find any errors then need to take rectify it and click next once everything looks fine

• Installation Type: Choose the "Perform the New SQL Installation" and click next

• Product Key: Choose the Developer Edition or you need to enter the product key if its Enterprise edition. Click next

• License Terms: Accept the License terms and click next
• Feature Selection: Here need to select the appropriate features required, and for basic SQL setup needs to select the below features.

• Instance Configuration: Select "Default Instance" to have the default instance name as  "MSSQLSERVER" or else select the "Named Instance" and provide the appropriate name

• Server Configuration:  

• 1)Service Account: Provide the domain level service account and password if you have any service accounts reserved for SQL services or else keep the default NTService accounts as below

• 2)Collation: If the requirement is for different collation than MS default collation, then it can be changed  to the respective collation in this window as below

• Database Engine Configuration:

1) Server Configuration: Choose the "Mixed Mode" and enter the password. This password is used for the SQL SA account. Add current user and optional group, users or service accounts can be added to SQL SysAdmin group by click on Add button.

2)Data Directories: Appropriate SQL Data Root, User DB Data, Log, Backup files path can be selected in below window

3)TempDB: SQL 2016 lets you configure TempDB as part of the installation itself

• Ready To Install: Review the summary and click the install to proceed for installation

Once after successful installation, need to install SSMS separately and which can be downloaded from MSSite.