Microsoft SQL Server - MSSQL / SQLSERVER
Replication
Publisher Setup
- Log into Studio Using the server name - and not localhost or ip address
- Run Through Publisher Wizard
- Use “Transaction Publication” Publication Type
- Select Database & Table(s) you want to replicate
- Create Snapshot
- Use SQL Server Agent Service Account for Snapshot agent. SQLSERVERAGENT neeeds file access to the MSSQL\repldata folder.
- Enter Publisher Name - “gor_test”
This creates “distribution” mdf and ldf files in the MSSQL\DATA folder.
Subscriber
- Create database - but NOT any tables.
- I using the Push approch (pushed from Pubisher to Subscriber) - all config done on Publisher.
- Needed to add SQLSERVERAGENT with read / write access to the repldata folder
- Create a SQL user on the Subscriber with owners rights to the specific database.
- On the PUBLISHER in Subscriber Properties set this as the Subscriber Connection
Making Changes to a Table on the publisher WITHOUT changing publisher / subscriber setup
- 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.
If you do need to make changes that require you 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
General TIPS
Creating CSV Files Using BCP and Stored Procedures
Backup & Restore from Command line
Single User Mode
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"
How to tell what version of SQL you are running is (Standard / Express etc.)
- https://technet.microsoft.com/en-us/library/ms165662(v=sql.90).aspx
select @@servername go
select @@version go
select serverproperty('edition') go
SP_config
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 |
To Enable / Disable an advanced option
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;
Does the config change require a restart?
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.
SQL Recovery Mode / Large Transaction Logs
If the SQL database has large transactions logs - when a SQL server restarts the Database can go into recovery mode for an extended period of time.
To View the Transaction Logs
dbcc sqlperf(logspace)
To commit the transaction logs to the database
checkpoint
You usually need to run this command more than once. After it completes run the dbcc sqlperf(logspace) again to confirm. After running this twice it the transaction log space used for the database should be < 1%.
Once the transaction space has been reduced - you should be able to shrink the Transaction log file to reduce space used on the Server. To do this run the shrink command and you can set the file size you want to shrink the Transaction log file to. You can use the GUI . Select the database → Right Click → Task → Shrink → Files Select File Type → Logs Select “Reorganize pages before releasing unused space - Shrink file to: ” and enter the file size.
To view the command line that the above GUI would run, select (at the top of the page)
Script → Script Action to New Window
This should give an output similar to below:
USE [eGMasterDB] GO DBCC SHRINKFILE (N'eGMasterDB_LOG_Device' , 2048) GO
What do you do when SQL Server is unusually slow?
SPN issues
Cannot generate SSPI context" error when using Windows authentication to connect SQL Server
I found that the SPN (ServicePrincpalName) - were not in AD as they should be. there should be two similar to below:
- MSSQLSvc/MY-SERVER-NAME.mydomain.com:1433
- MSSQLSvc/MY-SERVER-NAME.mydomain.com
you can use the tool - download and run from a client machine - to confirm
you can check AD attributes using
This query will return back the LDAP object name for the server “MY-SERVER-NAME”
dsquery computer -name MY-SERVER-NAME
Example Response:
"CN=MY-SERVER-NAME,OU="W2019",OU=Servers,DC=mydomain,DC=com" And then using this - you can find out all its LDAP attributes: <code> dsquery * "CN=MY-SERVER-NAME,OU="W2019",OU=Servers,DC=mydomain,DC=com" -attr *
Check the Attributes “servicePrincpalName:”
Parsing a string with a separator
ID PV1 PV2 1 boo MFA|1|boo|5:a:b:c 2 blah MFA|2|boo|6:2 3 bop MFA|3|boo|7:3 4 banana blah 5 a MFA.1.blah.2 6 b MFA.1.blah.3
Using String_Split - seems the easiest way to do this - but you need a uniue ID / Primary Key - which you can group off.
WITH SplitData AS ( SELECT ID, PV2, VALUE AS PartValue, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS PartNumber FROM RCD CROSS APPLY STRING_SPLIT(PV2, '|') WHERE PV2 LIKE 'MFA|%' ) SELECT ID, PV2, MAX(CASE WHEN PartNumber = 1 THEN PartValue END) AS Part1, MAX(CASE WHEN PartNumber = 2 THEN PartValue END) AS Part2, MAX(CASE WHEN PartNumber = 3 THEN PartValue END) AS Part3, MAX(CASE WHEN PartNumber = 4 THEN PartValue END) AS Part4, MAX(CASE WHEN PartNumber = 5 THEN PartValue END) AS Part5, MAX(CASE WHEN PartNumber = 6 THEN PartValue END) AS Part6, MAX(CASE WHEN PartNumber = 7 THEN PartValue END) AS Part7, MAX(CASE WHEN PartNumber = 8 THEN PartValue END) AS Part8, MAX(CASE WHEN PartNumber = 9 THEN PartValue END) AS Part9, MAX(CASE WHEN PartNumber = 10 THEN PartValue END) AS Part10 FROM SplitData GROUP BY ID, PV2 ORDER BY ID;`
ID PV2 Part1 Part2 Part3 Part4 Part5 Part6 Part7 Part8 Part9 Part10 1 MFA|1|boo|5:a:b:c MFA 1 boo 5:a:b:c NULL NULL NULL NULL NULL NULL 2 MFA|2|boo|6:2 MFA 2 boo 6:2 NULL NULL NULL NULL NULL NULL 3 MFA|3|boo|7:3 MFA 3 boo 7:3 NULL NULL NULL NULL NULL NULL
An alternative approach is to use the PARSENAME function. However this allows only up to 4 fields. So we would need to use different seperators - which becomes very messy.
Why only 4? Because, PARSENAME (Transact-SQL) Returns the specified part of an object name. The parts of an object that can be retrieved are the object name, owner name, database name, and server name. example: SELECT PARSENAME('[Server].database.schema.table', 1) AS 'Object Name';
SELECT ID ,PV2 ,LEFT(PV2, CHARINDEX(':', PV2) - 1) AS Part1 ,PARSENAME(REPLACE(PV2, '|', '.'), 4) AS MFA1 ,PARSENAME(REPLACE(PV2, '|', '.'), 3) AS MFA2 ,PARSENAME(REPLACE(PV2, '|', '.'), 2) AS MFA3 ,PARSENAME(REPLACE(LEFT(PV2, CHARINDEX(':', PV2) - 1), '|', '.'), 1) AS MFA4 FROM RCD WHERE PV2 LIKE 'MFA|%'
ID PV2 Part1 MFA1 MFA2 MFA3 MFA4 1 MFA|1|boo|5:a:b:c MFA|1|boo|5 MFA 1 boo 5 2 MFA|2|boo|6:2 MFA|2|boo|6 MFA 2 boo 6 3 MFA|3|boo|7:3 MFA|3|boo|7 MFA 3 boo 7