Replication Guide 1 - Windows 10 and SQL Server 2016 CTP 2.2 issue

Tested against SQL Server 2016 CTP2.2 (re-released version)

A fresh install of SQL Server 2016 CTP 2.2 (re-released version) does not have SQL Server Agent Running which is needed for a default Replication Setup

select @@VERSION gives the following

Microsoft SQL Server 2016 (CTP2.2) – 13.0.407.1 (X64)   Jul 22 2015 21:19:11   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows 10 Pro Insider Preview 6.3  (Build 10162: ) (Hypervisor)

We manually started the SQL Server Agent via services.msc

As replication setup via SSMS creates SQL Server Agent Jobs we first note the default SQL Server Agent Jobs created during a fresh standalone installation

We first create the Distributor:

• In SSMS under Replication right click and choose Configure Distribution
• Configure Distribution Wizard appears
• Have the server act as it's own Distributor
• Pick "No, I will start the SQL Server Agent service manually (Known issue otherwise)
• Use default snapshot folder
• Use default location for distribution database
• Use default publishers
• Leave wizard actions as default
• Click Finish to complete the Wizard

Note the new SQL Agent Jobs created

We next create the database and table to be replicated

Then we poplulate the table to replicated

Then check the contents of the table

We then create the database and table to be the target of replication

We create a new publication under Local Publications

• Replication -> New Publication
• Choose Database SnapPub
• Choose Snapshot Replication
• Pick table SnapCust1
• Do not filter rows
• Choose defaults on SnapShot Agent Screen
• Under Agent Security Choose "Security Settings" "Run under SQL Server Agent Account"
• Connect to Publisher "By impersonating the process account"
• Under Wizard Actions choose Next
• Under Complete the wizard choose Publication Name Pub1 and Finish
• We now have a new SQL Server Agent job '..-SnapPub-Pub1-1'
• Under the Publication add a new Subscription
• Choose the Publication Pub1
• Choose default - run all Agents at Distributior
• Pick Subscriber and Database SnapSub
• Under Distribution Agent Security choose '...' and Run under the SQL Server Agent service account
• Under Synchronization Schedule choose Agent Schedule 'Run on Demand only'
• Under 'Initialize Subscriptions' leave Initialize when at 'At first syncronization'
• Click Next then Finish

We get a new SQL Agent Job for the publication

Under the publication we then create a subscription

• Choose the Publication SnapPub1
• On the "Distribution Agent Location" keep the default - Run all agents at the Distributor
• Choose the subscriber and Choose SnapSub as the subscription database
• On the Ditribution Agent Security choose "Run under the SQL Server Agent" account
• On The Syncronzization Schedule Change to "Run on demand only"
• On the Initialize Subscriptions Screen keep the default of "At first syncronization"

We get a new SQL Agent Job for the subscription

Try an inital syncronization for the snapshot subscription we setup

We view syncronization status

We then start the agent

This then still says the Agent has never been run

When then choose Monitor to start the Replication Monitor and then status is still "Never Started!"

How do we see what happened?

As replication is handled by SQL Agent Jobs we check SQL Agent Job history

We see one of the new Replication related SQL Agent Jobs failed

We highlight the job and scrolling down in the window below we can see the error

The error is

The job failed.  Unable to determine if the owner (MicrosoftAccount\david@smooth1.co.uk) of 
job DESKTOP-CC0V7Q0\MSSQL-SnapPub-Snap1-DESKTOP-CC0V7Q0\MSSQL-1 has server access (reason: Could not obtain information about Windows NT group/user 
'MicrosoftAccount\david@smooth1.co.uk', error code 0x54b. [SQLSTATE 42000] (Error 15404)).

This is trying to determine permissions for my Microsoft Account as if it is a Domain Account under domain 'MicrosoftAccount'!!

In order to be eligible to continue being part of the Windows Insider program I switched my Windows install to my Microsoft Account

We can see what SQL Server is doing if we call xp_logininfo ourselves, we get the same error

Installing SQL Server as my default account after installing windows is no longer an option!

The correct answer (for me with no clustering/AGs) is to create a local account and use that to manager SQL Server!