Getting Started with SQL Server
SQL Server is a relational database management system, a ‘back-end’ for storing data. Data for an application needs to be stored somewhere, and SQL Server is a good place to store it.
One example that I always share with students (and mostly relatives, when they ask ‘just exactly what do you do again?’)…
Let’s say that you go to Amazon.com and open an account. When you reach the website Amazon.com, you are interacting with the Amazon web application. You enter your name, address, password, and credit card info. Where do you suppose that info gets stored? In a database of course. My job is to create tables, or, places that store specific data…maybe your address might be stored in the address table… and to write routines to INSERT data should you as a new customer decide sign up while at the site, UPDATE your data in case you change addresses, or DELETE routines so that if you get tired of shopping at Amazon, we can get rid of your information that is stored, and remove it completely from Amazon storage.
Db-engines.com continues to rank SQL Server very highly, and its popularity continues to increase. There are other methods to store data; a number of relational DBMSs, ‘NoSQL’ databases and variations (document oriented, key-value pair, others), cache databases, search databases, file system database, ‘memory-optimized’ database systems, etc. These are all valid databases types, and serve specific purposes.
From the Microsoft Developer Network (https://msdn.microsoft.com/en-us/library/mt590198(v=sql.1).aspx):
SQL Server is the foundation of Microsoft’s data platform, delivering mission-critical performance with in-memory technologies and faster insights on any data, whether on-premises or in the cloud.
Microsoft SQL Server is Microsoft’s enterprise RDBMS; Microsoft Access, an Office product, is also a relational database management system. This one however is for desktop or ‘departmental’, or small office database systems accessed either locally or through a browser. Read more at https://products.office.com/en-us/access. Smaller businesses can build databases in Access to run their departments or even their entire company, but for heavy-duty ‘enterprise’ stuff (generally those businesses with lot of users and locations that run across the WAN), larger companies will opt for SQL Server as their choice database system to store data.
We’re primarily concerned here with Windows Operating systems. If you are running a Mac, you can also participate with us by installing Parallels or Virtual Box, or a number of other virtual machine software to run Windows…and SQL Server. I don’t much about Parallels, other than it’s one of the most popular apps for a Mac, and it allows you to run both host and the Windows operating systems at the same time. Read up on that and other products:
Edit: A buddy of mine Kenneth Fisher who authors sqlstudies.com reminded me that SQL Server will run on the Linux operating system soon!
Before we begin learning about SQL Server, we need access to the software in some form or fashion, and so I’ll share with you what I know to get the platform so you can start learning.
There are several ways to get wired-up, and the first one that comes to mind is Azure. A good place to start learning and also the quickest and cleanest way to get connected is to use a free Microsoft cloud account. If you have a credit card, you can get an Azure account and begin using a cloud-based version of SQL Server. Azure comes with a $200.00 credit when you sign up, and it will not convert to a regular Azure account, one that is active and charges for usage, unless you specifically choose, so it’s definitely nice option that’s available.
We’ll look closer at Azure later, but for now let’s look at what’s available to us for download.
Versions – Which one do I download?
Read up on versions of SQL Server here; your best bet is to install the development version. This one gives you all of the features possible that come with the most complete version – Enterprise. The SQL Server developer edition, beginning with version 2014, is now free, and can be downloaded at https://www.visualstudio.com/dev-essentials/. This is really great change for the better, as for years we had to pay between $40 and $50 bucks, give or take, for the dev edition of SQL Server. You’ll have to go through a sign up process before you can download, but it takes only a few minutes and isn’t overly painful
Afterwards, you’ll need a separate download for the management and development tool (SQL Server Management Studio, or SSMS as it is often referred to), located at https://msdn.microsoft.com/en-us/library/mt238290.aspx. Lately, there seems to be an update for this every two weeks or so. This is the front end application tool that will allow you to access databases after you have installed SQL Server, view data, etc.
One note: if you are still running Windows 7, you will not be able to install the SQL Server 2016 sp1 database engine. You can install SSMS, but that’s it. Later versions such as Windows 8 or 10 are fine.
SQL Server Developer Edition
Here is an excerpt from the Q4 2016 licensing datasheet regarding the dev edition:
SQL Server 2016 Developer Edition provides a fully featured version of SQL Server software—including all of the features and capabilities of Enterprise Edition—licensed for development, test and demonstration purposes only.
Customers may install and run the SQL Server Developer Edition software on any number of devices. This is significant, because it allows customers to run the software on multiple devices (for testing purposes, for example) without having to license each non-production server system for SQL Server.
A production environment is defined as an environment that is accessed by end-users of an application (such as an Internet website) and that is used for more than gathering feedback or acceptance testing of that application.
SQL Server 2016 Developer Edition is now a free product, available for download from the Visual Studio Dev Essentials program.
We’ll go through this set-up in an upcoming blog post.
The Azure offer for the current Dev Essentials is pretty nice. From the Azure site:
After you join Visual Studio Dev Essentials, use your credit ($25/month for one year) on popular Azure services, including Virtual Machines, Web Apps, Cloud Services, Mobile Services, Storage, SQL Database, Content Delivery Network, HDInsight, and Media Services.
Azure databases are well suited for learning SQL Server. I have several SQL Azure databases running, and I’ve never come close to the $25.00 limit, so you should have no problem. When the year expires, simply sign up again under another email address.
While you’re at it, check out other offerings on the Dev Essentials site.
SQL Server Express
SQL Express is also available free for download. You might opt for this as it will be a smaller footprint on your local pc or workstation, and do just fine for our learning for now. I downloaded it and after the installation was complete, I got the following:
We’ll also install this edition in an upcoming post.
Notice the ‘Install SSMS’ button that I mentioned earlier. Since I had SSMS already installed, I simply entered the first part of the connection, localhost\SQLEXPRESS and was able to connect:
We will probably not need to be concerned about other editions, such as Enterprise or Standard, because they cost money and are not required for us to learn SQL Server.
What did we learn?
In this post, we barely scratched the surface on SQL Server and other database systems. Since we have decided to explore SQL Server, I’ve given you a little info on how to go about accessing SQL Server, which versions to download, and where to get those versions. In the next post, we’ll install both the SQL Express and Developer Edition database engines.