Microsoft SQL Server - MSSQL / SQLSERVER

Publisher Setup

  • Log into Studio Using the server name - and not localhost or ip address
  • Run Through Publisher Wizard
  1. Use “Transaction Publication” Publication Type
  2. Select Database & Table(s) you want to replicate
  3. Create Snapshot
  4. Use SQL Server Agent Service Account for Snapshot agent. SQLSERVERAGENT neeeds file access to the MSSQL\repldata folder.
  5. Enter Publisher Name - “gor_test”

This creates “distribution” mdf and ldf files in the MSSQL\DATA folder.

Subscriber

  1. Create database - but NOT any tables.
  2. I using the Push approch (pushed from Pubisher to Subscriber) - all config done on Publisher.
  3. Needed to add SQLSERVERAGENT with read / write access to the repldata folder
  4. Create a SQL user on the Subscriber with owners rights to the specific database.
  5. On the PUBLISHER in Subscriber Properties set this as the Subscriber Connection
  • You can add a new column at the end of your existing table
  • You cannot change the type of an existing Column - “unable to modify table - cannot drop the table 'dbo.myTable' because it is been used for replication
  • you cannot rename a column - because “it is published for replication”
  • You cannot add a new column and move it between two existing columns - as this needs to drop the table.
  • Stop Replication (Right Click - View Sync Status)
  • Remove Table from Article
  • Make the changes on Publisher which require the table to be doped and re-inserted.
  • Add table back into Article
  • Right Click Publisher and Reinitialize All Subscribers - select “Use a New snapshot / Generate the new snapshot now” - Mark for Re-initialization
References

This allows you to restore the master database, and also add the windows local administrator to add user with whatever rights necessary. e.g. sysadmin for local admin

  • Stop SQL Service
  • Start command window 'run as an administrator'.
  • Change to the the required Folder for sqlservr.exe , e.g. C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
  • Run below command
"sqlservr.exe -c -m"
select @@servername
go
select @@version
go
select serverproperty('edition')
go

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-configure-transact-sql?view=sql-server-ver15

sp_configure;

When executed with no parameters, sp_configure returns a result set with five columns and orders the options alphabetically in ascending order, as shown example out[ut.

Note, when you run the sp_configure command by default you only get to see the standard configurations options

name minimum maximum config_value run_value
allow polybase export 0 1 0 0
allow updates 0 1 0 0
backup checksum default 0 1 0 0
backup compression default 0 1 0 0
clr enabled 0 1 0 0
contained database authentication 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
external scripts enabled 0 1 0 0
filestream access level 0 2 0 0
hadoop connectivity 0 7 0 0
max text repl size (B) -1 2147483647 65536 65536
nested triggers 0 1 1 1
polybase network encryption 0 1 1 1
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote data archive 0 1 0 0
remote login timeout (s) 0 2147483647 10 10
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user options 0 32767 0 0

If you want to see the Advanced options as well you need to run the following command:

sp_configure 'show advanced options', '1';
RECONFIGURE

And THEN run the command (advanced example output below):

sp_configure

Caution!
When the option Show Advanced Options is set to 1, this setting applies to all users. It is recommended to only use this state temporarily and switch back to 0 when done with the task that required viewing the advanced options.

name minimum maximum config_value run_value
access check cache bucket count 0 65536 0 0
access check cache quota 0 2147483647 0 0
Ad Hoc Distributed Queries 0 1 0 0
affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
Agent XPs 0 1 1 1
allow polybase export 0 1 0 0
allow updates 0 1 0 0
automatic soft-NUMA disabled 0 1 0 0
backup checksum default 0 1 0 0
backup compression default 0 1 0 0
blocked process threshold (s) 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 0 0
clr strict security 0 1 1 1
contained database authentication 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 0 0
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
external scripts enabled 0 1 0 0
filestream access level 0 2 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
hadoop connectivity 0 7 0 0
in-doubt xact resolution 0 2 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32767 0 0
max full-text crawl range 0 256 4 4
max server memory (MB) 128 2147483647 2147483647 2147483647
max text repl size (B) -1 2147483647 65536 65536
max worker threads 128 65535 0 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 16
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 0 0
open objects 0 2147483647 0 0
optimize for ad hoc workloads 0 1 0 0
PH timeout (s) 1 3600 60 60
polybase network encryption 0 1 1 1
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 1 1
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote data archive 0 1 0 0
remote login timeout (s) 0 2147483647 10 10
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
xp_cmdshell 0 1 0 0

The below details gives an example on how to enables advanced options, disables the Database Mail XPs and then re-disables the advanced options again.

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Database Mail XPs', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

Options that require SQL Server to restart will initially show the changed value only in the value column. After restart, the new value will appear in both the value column and the value_in_use column.