SQL Server for Beginners: Part I – From Desktop to Network

In an earlier series of articles, I wrote about getting started with Microsoft Access, which is one of the foremost desktop database packages available and an excellent tool for small to mid-size database projects. While it was not the first database software that I used, it was the one that took me from novice to professional status. Once I reached that level, however, I had to start learning more advanced tools in order to advance in my career and take on new projects. One important set of tools was Microsoft SQL Server which remains one of the most popular network database systems on the market.

While the transition was a little intimidating at first, it wasn’t that difficult in the long-run, especially since I was able to transfer much of the database knowledge that I had gained with Access. Like the desktop database, SQL Server is a relational database system which uses tables, relationships and queries to store information and make it available for retrieval and analysis. Unlike Access, SQL Server puts less focus on its own self-contained interface and more emphasis on making the data available over a network to any application that is capable of using the information.

If you go to the SQL Server home page and look for an actual definition of SQL Server, you’ll encounter a lot of explanations that are impressive to corporate I.T. managers and marketing professionals but don’t really serve the average person who is trying to learn how to use the product. If you’ve never used SQL Server before but have a basic idea of what a database is and perhaps some experience with desktop databases, the best way to give you a beginning perspective on SQL Server might be to compare a few of its features to those of Microsoft Access:

Microsoft Access SQL Server
An Access 2013 database can store up to 2 gigabytes of data in one database file. While larger databases can be created by creating links between files, these are absolute links to specific PC or network file locations and must be manually maintained. A single SQL Server 2012 database can store up to 524,272 terabytes of information (over 268 million times the Access 2013 limit). Up to 50 instances of SQL Server can be run on a single network server and each instance can hold 32,767 databases.  Data can be easily queried between databases without any attention to where the data is physically stored.
An Access database can have up to 255 concurrent users although performance starts to degrade long before that. That number also depends on the database being split with separate interface or front-end files which are then linked to a common back-end file which holds the actual data. SQL Server 2012 has a maximum of 32,767 user connections.
Access 2010’s only native method of preventing unauthorized users from accessing the data is a database password and individual file encryption. Once that password is obtained, all objects within the database are available to all users. SQL Server offers Integrated Windows security in addition to its own system of logins and passwords. Users can be granted and denied access to specific objects based on their Windows user accounts and group memberships.
Access uses standard SQL (Structured Query Language) for querying the database tables.  These queries permit one operation (SELECT, UPDATE, INSERT, etc..) per query. Visual Basic for Applications (VBA) or Access macros can be used to carry out multiple operations although this requires the application to be run from a trusted location on the machine due to the possibility of malicious code. SQL Server uses T-SQL (Transact-SQL) and compiled stored procedures which permit complex data manipulation and multiple operations directly on the server. These procedures can be quickly modified and re-deployed. This reduces the amount of data transfer between the server and the front-end application. This in turn increases the efficiency of the operations.

For more specifications see the following links:

Access 2010 Specifications /
Introduction to Access 2010 Security

Product Specifications for SQL Server 2012

If you are interested in pursuing an I.T. career, you can find a final, practical reason to develop your SQL Server skills by going to a site like CareerBuilder.com or Monster.com and doing some comparative searches for Microsoft Access and SQL Server. Notice the number of positions for each of these technologies, the prominence with which they are mentioned in the individual listings and the relative pay. That should give you a good idea of which is the more valuable skill. Having worked with both technologies for years, I can say that if a company is going to hire a new person, it’s probably going to be someone with the SQL Server experience and perspective while Microsoft Access projects are just as likely to be handed to an existing employee.

None of this is to say that desktop databases like Microsoft Access aren’t useful. As I said, Access is great for small department-level projects and it’s a wonderful training ground for database and basic programming skills. It has its place in the I.T. world. It also has some very passionate defenders from whom I would prefer not to get hate mail. In the end, the choice comes down to using the right tool for the right job.

One of the great things about SQL Server is that even though it’s a network database, you don’t need a nework in order to run it in your machine and study it in-depth. There are multiple editions of SQL Server including SQL Server Express which can be installed on your PC and accessed either through its own management tools or through another development package like Microsoft Visual Studio which includes the Express edition as part of its own installation. In the following chapters, I will guide you through downloading the appropriate packages, installing the software and getting familiar with some of its basic functions.

(This article is updated from an article series previously published by Comeau Software Solutions.)

Available on Amazon.com

Enfain 10 Pack 16 GB USB Flash Drive Red