Show Menu
Cheatography

Serverteknologi Database Cheat Sheet by

Made by

Nevzat Tokmak
Nicholas Johans­son
Rune Andrea­sen

Serve­rte­knologi Database

Relation Database

A relational database is a set of formally described tables from which data can be accessed or reasse­mbled in many different ways without having to reorganize the database tables. The standard user and applic­ation progra­mming interface (API) of a relational database is the Structured Query Language (SQL). SQL statements are used both for intera­ctive queries for inform­ation from a relational database and for gathering data for reports.

MS SQL Archit­ecture

External Protoc­ols
Shared memory - Named pipes - TCP/IP - Virtual Interface Adapter (VIA)
Database Engine
Database Tables­/In­dexes - Type System Transa­ctions - Events­/Ex­ception Triggers - TSQL - Stored Procedure - SQLCLR
Storage Engine
Transa­ction Services & File Manager (Utili­ties) – Buffer Manager & Lock Manager (Access Methods)
Query Proces­sor
Parser - Optimizer - SQL Manager - Database Manager - Query Executer
SQL OS API
Lock Manager – Synchr­oni­zation Services – Thread Scheduler – Buffer Pool – Memory Manger – I/O Manager

Database Design and Resource use

Database planning
Planning the database is very important in terms of creating a resour­ceful and working database. Even though it seems obvious – It can be one of the most demanding tasks, when creating a relational database.
Docume­ntation
The importance of docume­ntation of a database, will make it easier for mainta­ining and altering changes further ahead in the database.
Naming conven­tions
Even if you consider naming your tables, columns etc. to be easy – It is very important to use clear and unders­tan­dable name giving for your database – So it can be understood by everyone who need to work on the database.
Normalize
It very important to know the rules of normal­ization in terms of relational database structure. If the principles of normal­ization are done right. Your database will be efficient and logical to everyone. It is very important that your normal­ization is done right up to the Third Normal­iza­tion.
Data types
Giving your design the right data types is crucial for a working database. Therefore is very important to give your columns in your database the most approp­riate data type for each and give you better unders­tanding of the data.
Primary keys
It is very important in terms of relational database, that your primary and natural key is set correctly. Be sure that you define a primary key and natural key for your tables – It can be very important when you start to break up your tables when you start to normalize.
Data Integrity
Data integrity is very important when you work with RDBMS and it is one of the core functions to uphold the data integrity in your database.
Indexing
A proper indexing is a key to a successful database. It can be very complexed to index your database and deserves quite focus when creating your database.
Testing
Testing your relational database is very important and can be crucial to avoid system failures. Quality assurance is there for key in database creating.
 

Install SQL Server

Hardware Minimum Requir­eme­nts
SQL Server requires a minimum of 6 GB of available hard-disk space.
SQL Server requires Super-VGA (800x600) or higher resolution monitor.
Memory: Express Edition 512 MB, All other Editions 1 GB
Processor Speed: x64 Processor: 1.4 GHz
Processor Type: x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support
Install SQL Server
Run setup.exe from your media -> Instal­lation -> Choose "New SQL Server stand-­alone instal­lation or add features to an existing instal­lat­ion­" -> Next -> Check I agree to the terms -> Next
Advance to Install Setup Files and check for updates, if not move on and check your instal­lation is correct. Click Next.
On Instal­lation type choose new or add features to existing and click Next -> Advance -> Name your Instance ID -> Create Service Account -> Advance to Install page -> Reboot when Required to -> Your instal­lation is now complete

Configure Windows Firewall

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

Tabeller

MASTER
The master database records all the system­-level inform­ation for a SQL Server system.
MODEL
The model database is used as the template for all databases created on an instance of SQL Server.
MSDB
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail
TEMPDB
The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server or connected to SQL Database. Tempdb is used to hold: Temporary user objects that are explicitly created. Internal objects that are created by the database engine. tempdb is re-created every time SQL Server is started

Add users (RDMS)

Powe­rsh­ell
$newUser = New-RD­MUser -Name "­Log­inN­ame­" -Creat­eSQ­LSe­rve­rLogin -Integ­rat­edS­ecu­rity
GUI
SSMS > "­SQL­-Se­rve­r" > Right click on Security > New - Login
Creating an user in Query
CREATE LOGIN "­USE­RNA­ME"
WITH PASSWORD = 'Pa$$w­0rd'
The Password need to follow the Windows policy requir­ement
GO
CREATE USER "­USE­RNA­ME" FOR LOGIN "­USE­RNA­ME"
GO

User Permis­sions (RDMS)

Powe­rsh­ell
Set-R­DMU­ser­Pro­perty -User $newUser -Property "­Add­" -Value $TRUE
Set-R­DMU­ser­Pro­perty -User $newUser -Property "­Edi­t" -Value $TRUE
Set-R­DMU­ser­Pro­perty -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" '
 

Backup of Database

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

Recovery of Database

Recovery of Full Database Backup
Right click NORTHWIND -> Restore Databa­se... -> From Device -> Add -> Browse your file -> OK -> Select point to restore

Optimize Database Operations

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 & Export Database MS Tool

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

Setup Replic­ation

1. Configure Distri­butor and publis­her
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­ipt­ion
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.
Dist­rib­ution 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.

Support Cheatography!

 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          More Cheat Sheets by Nicholade