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 SoftwareNow 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 SystemWithin Windows, you can choose to download SQL Server in one of three ways:
- Free trial evaluation
- Developer edition
- Express edition
LinuxIf 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
Mac OSIt’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.
AzureYou 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
Exploring SQL Server Management ToolsThere 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
GUI ToolsFirst, let’s review some of the most common GUI tools available via Microsoft SQL Server 2017.
Azure Data StudioFormerly 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 StudioThis 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.
DBeaverLooking 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):
- MS Access
Command Line ToolsNext, let’s take a look at some of the command line tools that users can leverage to manage SQL Server.
sqlcmdThis 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-cliThe 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-cliEspecially 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 ModuleWith 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 DatabaseOnce 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 ServerFirst, 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
- Database Name
- Server Group
The Database Creation ProcessOnce 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 TabThis 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 ScriptAt 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 DatabaseAfter 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 DatabaseYou 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 TablesFirst, 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.