Although it’s been around since the late 1980s, Microsoft SQL Server continues to stand as one of the most reliable database management systems on the market.
In a digital age where tech-savvy tools threaten to usurp solid standbys, SQL has seen steady growth. In fact, it’s even undergone a recent resurgence, and one study even claimed it was the most popular database product in 2016.
If you’re new to SQL Server or SQL Server Express, you’ve come to the right place. Today, we’re sharing our complete guide on how to get started with the system and how you can use it to create custom database designs for your organization.
Ready to learn more? Let’s get started!
What is Microsoft SQL Server?
In short, SQL Server is a relational database management system (RDBMS) developed and marketed under the Microsoft umbrella.
Like most other RDBMS solutions, this version is built on top of SQL, which is a standard programming language used to interact with relational databases. Microsoft’s SQL Server connects to Transact-SQL (T-SQL). This is a set of programming extensions from Microsoft that enhance the functionality of core SQL.
Until 2016, Microsoft SQL was exclusively available on the Windows environment. Then, it became available on Linux and Docker as well. The most recent version, SQL Server 2017, became generally available in October 2016.
Now, 98 of the Fortune 100 companies utilize Microsoft SQL Server and business leaders around the world leverage it to create dynamic database solutions.
Are you ready to join their ranks? Before you get started with the tool, let’s explore how to use it.
Installing the Microsoft SQL Software
Now that you can download SQL Server on both Linux and Docker, you have an even wider variety of installation options. Let’s take a quick look at each one.
Windows Operating System
Within Windows, you can choose to download SQL Server in one of three ways:
- Free trial evaluation
- Developer edition
- Express edition
Compare the features available in each option and click the “Download Now” button to begin the installation process. When you’re prompted to choose an installation type, clicking “Basic” will install the database engine with the default configuration. If you wish to add extra features, you can select the “Custom” configuration. Then, you’ll need to read the license agreement and select an installation folder to complete the installation.
Linux
If you want to install SQL Server onto a Linux OS, you’ll first need to select the type of Linux distribution you’re running. Your options include:
- Install on Red Hat Enterprise Linux 7.3
- Install on Ubuntu Linux 16.04
- Install on SUSE Linux Enterprise Server v12 SP2
Once you’ve selected your type, you can follow the on-screen commands to complete the installation.
Mac OS
It’s now possible to run Microsoft SQL Server on a Mac, but there’s only one caveat. You have to install Docker first. This is the brand’s open-source project that allows business users to automatically deploy applications as self-sufficient containers capable of running on the cloud or on-site. If you haven’t already installed Docker, go ahead and do it now. Then, click the link that reads “Pull and Run SQL Server on a Linux Container on Docker” and follow the installation instructions.
Azure
You can also deploy SQL Server 2017 on your Azure platform with the help of a virtual machine. This cloud-based service allows users to connect their server to a variety of operating systems, including:
- Windows Server
- SUSE Linux Enterprise Server
- Red Hat Enterprise Linux
- Ubuntu Linux
Follow the instructions to download SQL Server for your specific operating system. Once the installation is complete, it’s time to decide which management tools you want to use.
Exploring SQL Server Management Tools
There are a number of database management tools designed to enhance your use of SQL Server. Some of these tools are available directly through Microsoft while others are third-party options. Regardless of their origin, most of these tools are designed with one aim in mind: to make your database administration and programming efforts as effective as possible. When you add these tools to your baseline SQL functionality, you’re able to perform advanced commands such as:
- Create databases
- Perform queries
- Manage individual users
- Run backups and restores
- Import and export data
Note that you won’t receive any server management tools with your initial installation of SQL Server 2017. In prior versions, including SQL Server 2016, some management tools were included in the installation as optional components. Now, they’re all provided separately. You can divide the tools into two main categories: Graphical User Interface (GUI) tools and Command Line Interface (CLI) tools. Let’s take a look at a few of the options available under each.
GUI Tools
First, let’s review some of the most common GUI tools available via Microsoft SQL Server 2017.
Azure Data Studio
Formerly known as SQL Operations Studio, this is a free tool that Microsoft provides. Windows, macOS and Linux users can access this tool to manage their SQL Server, Azure SQL Database, and Azure SQL Data Warehouse. This tool displays all of your databases and their associated objects in a user-friendly navigation menu, allowing you to take a closer look at any given database in seconds. From here, you can create new databases, query them, back them up, and more.
SQL Server Management Studio
This resource is for Windows users only. For years, this was the go-to GUI tool to help users navigate SQL Server. In fact, it was previously included as an optional component during every SQL Server installation on the Windows OS. While this is no longer the case, you can access the SQL Server Management Studio (SSMS) as a separate installation in the SQL Server Installation Center.
DBeaver
Looking for a free, open-source tool that works with almost every major database management system? DBeaver fits the bill. You can use this to expand your SQL Server functionality to the following systems (among others):
- MySQL
- Oracle
- PostgreSQL
- SQLite
- DB2
- MS Access
- Teradata
- MariaDB
- Firebird
- Derby
Command Line Tools
Next, let’s take a look at some of the command line tools that users can leverage to manage SQL Server.[/et_pb_text]
sqlcmd
This is a command-line utility that enables users to enter Transact-SQL statements, system procedures, and script files via several available modes, including:
- At the command prompt
- In SQLCMD mode’s Query Editor
- In a Windows script file
- In an operating system job step of an
- SQL Server Agent job
mssql-cli
The team at Microsoft built this command-line tool specifically for SQL Server, announcing and unveiling it in December 2017. While it’s similar in nature to sqlcmd, it offers more in terms of functionality. With mssql-cli, users can now access:
- Syntax highlighting
- Multi-line editing modes
- Pretty formats for query results
- T-SQL IntelliSense
sql-cli
Especially if you already have NodeJs installed, incorporating sql-cli is a cinch. You can install this interactive command-line tool in a single command.
SQL Server Powershell Module
With this command-line tool, you can script and automate your most routine administrative tasks to gain efficiency. Within your SQL Server environment, you’ll find two different PowerShell modules: SqlServer and SQLPS. While the SQLPS module might come with your initial installation, note that it is no longer updated and the version you receive might not be the most recent one.
To this end, SqlServer remains the most current and reliable version. You can also run PowerShell in your SQL Server Management Studio. To do so, right-click on the object that you want to work on (in the Object Explorer) and then select “Start PowerShell.” As you explore the platform, take note of the many different types of SQL Server management tools available.
While the ones listed above are representative examples, it’s important to consider your specific needs when making a selection. From diagnostics and maintenance to bulk data copying, there’s a tool for almost every requirement.
Creating Your First Database
Once you’ve installed the software and chosen your management tools, the real fun can begin. Now, you’re ready to create a database in SQL Server 2017! Here are a few steps to follow.
Connecting to the SQL Server
First, you’ll need to connect to your SQL Server. To do so, begin by launching your database management tool. For simplicity’s sake, we’re going to explain these next steps referencing the former SQL Operations Studio, now called Azure Data Studio. Before you can connect to the SQL Server, you’ll need to enter a few details, including your:
- Server name
- Authentication type (SQL login)
- User name
- Password
- Database Name
- Server Group
Under “Server name” you can select “local host.” Or, if you’re connecting to a different machine, enter the name of that machine.
The Database Creation Process
Once you’ve successfully connected to the SQL Server, you’re ready to create your database! This three-step process is simple. Let’s review what to do.
Make a New Query Tab
This tool is for GUI users only. If you’re using a command-line tool you can skip this step. If you’re operating within SSMS you’ll find the “New Query” button on your dashboard. You can also find this option by going to File > New Query. DBeaver users should go to SQL Editor > New SQL Editor. In either case, selecting this program will open a new query tab, unless you already have one open.
Run the Script
At the “Create” prompt, enter the following details: DATABASE YourSubject; For instance, you might create a database that’s centered on tracking inventory. In this case, your command would read: DATABASE Inventory; Note: If you don’t see a “Create” button, it might also be labeled as “Execute” or a similar wording depending on your OS. Click “Enter” to complete the demand.
Viewing Your Database
After you create it, your new database is instantly accessible. You’ll find it on the left panel, under the “Database” node. Not seeing it? Try refreshing your list to give it a few seconds to appear.
Enhancing Your Database
You did it, you’ve created your first database in SQL Server. The only problem is that it’s blank! Now, it’s time to add tables and data to turn it into a real business asset. Let’s learn the very first way to do this.
Creating Tables
First, run the script for your blank database. It was DATABASE Inventory; in the example above. Then, it’s time to make your tables. The prompt to do so begins with CREATE TABLE. Use these to assign your high-level subjects. Individual columns can fit within a subject, and you’ll add these on the lines below it.
To define and assign a column, you’ll start with its name, followed by its data type and any applicable constraints. You can expand each table (e.g. Widgets) to display all of the relevant content within it. From there, you can designate each column’s data type as well as any specified restraints.
Invest in Microsoft SQL Server Today
An effective database system might not be as flashy as other tech-savvy gadgets. However, it could be the most powerful player in your digital marketing toolkit.
Microsoft SQL Server provides enhanced security features, optimized team performance and a lower cost of ownership. When you’re ready to realize its benefits at your own office, we’d love to help.
We’re Microsoft specialists focused on helping our clients reach to meet their goals. Perhaps you’re migrating from an outdated SQL Server 2008 R2 environment or need a set of new stored procedures written. Our consultants have decades of experience and will walk you through your SQL Server needs step by step, so you’re always in the loop about what’s going on and can spread this knowledge to key stakeholders.
For more information, feel free to contact us today and move forward in confidence.
