Microsoft SQL Server - Basics

Video: Install SQL Server 2016 and allow remote access

Video: Install SQL Server Management Studio SSMS

Contents

SQL Server – Strong Points. 1

SQL Server – Strong Points

Some strong points to consider:

· Performance – It handles most things pretty well, for example: a bulk insert with a random string primary keys makes some other servers choke.

· Security – Being able to use Windows users and avoid passwords in connection strings is good.

· User Interface – Very clean an obvious management software.

· Diagramming – Diagramming within SSMS is fine. The diagram is “live” and will update the database if you make changes.

· Backup & restore on the server. Many other database servers don’t have a built in backup and restore you have to do it in a separate client process.

SQL Server Notes

Some general notes on SQL Server:

· Don’t install server components you will never use

· The SQL Server Browser Service is only required when multiple database server instances are installed on the host

· The SQL Server Agent Service can be used for running various tasks like backups, alerts, and data transfers. You don’t necessarily need to use it (you can backup databases with the Framework for example).

· Don’t use an instance name unless you absolutely must (it just adds confusion) YOU NEED TO CHANGE DEFAULT IN EXPRESS EDITION

· The “Full Text Search” services are used for special searches on things like columns containing Word documents. You don’t necessarily need to use it (the Northwind 2008 sample uses them).

· For remote access port 1433 needs to be open

· For remote access TCP/IP needs to be enabled on the server using “SQL Server 2016 Configuration Manager” (not in SSMS)

· In a workgroup if you want to use Windows authentication (no password in connection string) you need to add a user with the same name and password as a Windows local user

· Windows user accounts (like “SpludlowUser”) need a “database login” setting up on the database server.

· You can set a “database login” to have the “sysadmin” database server role, this means it can do anything, handy in test environments so you don’t have to assign database users (described on next line).

· Each database then needs a “database user” assigned to the “database login” that allows individual permissions for the database (unless they are sysadmin).

· SSMS does not have to be installed on the Server but is often a good idea.

· SSMS can be installed on development machines.

· Mixed security mode means you can create native database logins (not using a Windows user) this will mean the credentials (with password) are supplied in the connection string.

· The native login “sa” is the default sysadmin.

· Windows authentication may seem a little awkward on WORKGROUP networks but it’s worth the extra hassle.

· The install media is dumped in “C:\SQLServer2017Media” (by default) you can remove this if you want.

· SQL Server keeps the main data files in “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA” (by default) in an emergency data recovery can be performed with these files.

· You may want to store the main data files on a separate fast disk if you are looking for high performance.

Run a command

Find the database you want to run commands against, right click, then select “New Query” (this is slightly misleading because your command may not be a query), you get a text editor style window to enter your commands. At the top right you should see the “SQL Editor Toolbar”, it has a dropdown list that you can change the database you are working on and next to it is a “Execute” button, this should be all you need for now. If the toolbar is missing turn it on through View->Toolbars.

Management

The management program for SQL Server is SSMS (SQL Server Management Studio).

Diagrams

NOTE: SQL Server’s diagrams in SSMS are “live” if you change a column then save the diagram it will attempt to make the changes immediately to the database, unlike the other way of doing it where the diagram is “off line” and when you want to update the database you run the “synchronise” function of the diagramming program.

To create a diagram from and existing database: SSMS->Database-> “Database Diagrams” folder right click this folder and select “New Database Diagram” you get a list of tables, you can select all by selecting the top one then with shift down select the bottom. Save the diagram, as you have not made any changes yet the database will not be changed but the diagram will be saved in the database (not a file).

To Tidy up a diagram: select all tables in the diagram CRTL-A then right click on any of the tables blue headers, select “Table View->Standard” then using the same menu do “Auto size selected tables” then “Arrange Selection”, (Make sure all tables are selected). You should now have something to work with, move the tables around how you like them.

To update the diagram: Find within the SSMS->Database-> “Database Diagrams”-> Double click and start editing. When you save your diagram it will attempt to make any changes to the database (it automatically synchronises).

To create a new diagram: Create an empty database and add a diagram as in the create diagram step above (it will be empty). Save the model. Start work adding tables… save when you want.

Error: “Saving changes in not permitted. The changes you have made require the following tables be dropped and re-created”

Un-Tick the checkbox at Tools->Options->Designers->Table and Database designers->Prevent saving changes that require table recreation

Note: If there is a problem synchronising the diagram to the database the diagram will still save, but it will be un-synchronised, next time you open the diagram it shows as if it had been edited, just closing it will attempt to synchronise again until it can.

Error: “Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15517)”

You can get this error if you have a database that you restored from a backup made on another host. It’s happening because a database’s owner user (dbo) is not recognizing the database login (which is a Windows User) from the other host. Fix it by running following command against the database in question:

sp_changedbowner 'DOMAINORHOST\UserName'