Microsoft SQL

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

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

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.)

select @@servername
go
select @@version
go
select serverproperty('edition')
go
This website uses cookies. By using the website, you agree with storing cookies on your computer. Also you acknowledge that you have read and understand our Privacy Policy. If you do not agree leave the website.More information about cookies