Serverteknologi Database Cheat Sheet

Nevzat Tokmak
Nicholas Johansson
Rune Andreasen

^{Server­tek­nologi Database}

Open TCP port 1433
Run netsh.exe with admin permis­sions
firewall set portop­ening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = SUBNET profile = CURRENT

Powershell
Set-RD­MUs­erP­roperty -User $newUser -Property "­Add­" -Value $TRUE
Set-RD­MUs­erP­roperty -User $newUser -Property "­Edi­t" -Value $TRUE
Set-RD­MUs­erP­roperty -User $newUser -Property "­Del­ete­" -Value $TRUE

GUI
SSMS > "­SQL­-Se­rve­r" > Databases > "­Dat­aba­se" > Security > Users > Right Click on an user > Permis­sions > Owned Schemas

Alter User with Query
EXEC sp_add­rol­emember ' "­SCH­EMA­S" ', ' "­USE­RNA­ME" '

Create a Full Backup
Go to your Database in Object Explorer
Right click NORTHWIND -> Tasks -> Back UP -> OK
It creates our backup and sets it to default location

Create a Full Backup to non-de­fault location
Go to your Database in Object Explorer
Right click NORTHWIND -> Tasks -> Back UP
From here go to Destin­ation -> General -> Select Disk from Drop-down menu
Click Remove until all existing backup files have been removed.
Click Add and enter your desired Path.
It creates our backup and sets it to your changed path

In SSMS open SQL Activity Monitor by pressing
CTRL + ALT + A

View Log Files gathered from SQL
%Progr­amF­ile­s%­\Mic­rosoft SQL Server­\13­0\Setup Bootst­rap­\LOG\

Import and Export Wizard
Run the SQL server 2016 Import and Export Data Wizard
Click Next -> Pick Excel as Data source -> Choose Destin­ation SQL server and modify the settings -> Choose Copy Data -> Next -> Pick your Table to copy -> OK -> Check Run Immedi­ately -> Finish

1. Configure Distri­butor and publisher
SSMS > "­SQL­-Se­rve­r" > Right click Replic­ation > Configure Distri­bution > Act as own Distri­butor > When asked for publisher add second server
2. Creating Snapshot Public­ation
SSMS > "­Pub­lisher Server­" > Replic­ation > Right click Local Public­ations > New public­ations > Use following server > Add > Select Distri­butor > Select Database > Snapshot Public­ation > Choose Tables > Create Snapshot Immedi­ately
3. Create Subscr­iption
SSMS > "­Pub­lisher server­" > Replic­ation > Local Public­ations > Right click the new replic­ation > New Subscr­iption > Add Subscr­iption > Run all Agents at Distri­bution > Add SQL Server Subscr­iption > Choose a Path > Add New Database > Agent Schedule > Run Contin­uously > Initialize > Immedi­ately

Snapshot Agent
The Snapshot agent stores an executable file which stores schema and data of published tables and db objects. It's updated when synchr­oni­zation jobs are recorded.
Distri­bution Agent
Works with Snaphot to distribute to subscr­ibers. Moves transa­ctions held at the distri­bution db out to the subscr­ibers.
Merge Agent
It applies the initial snapshot to the Subscriber and moves increm­ental data changes that occur.
Queue Reader Agent
It runs at the Distri­butor and moves changes made at the Subscriber back to the Publisher.