What is SQL Server, 2 page, 500 words

I have a 2 page description of Working With Sql Server attached.

Could you please do some sanity check on its content. Please note that this is a high-level description and does not include all necessary details. All I want is to make sure it doesn't contain wrong statements.

Note: This is prepared for person with little programming skills and interest in the subject.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

BitsqueezerConnect With a Mentor Commented:

if you want to write a document about SQL Server for "person with little programming skills and interest in the subject" then the informations you provided are in my eyes partially not relevant and partially also wrong.

If you want to describe SQL Server the first thing would be to describe what a database server in general is, not how to install it or how many instances can be installed (even many developers never have installed an SQL Server by themselves or know about many possible instances of the server). On the other hand you do not even mention that one instance can manage multiple databases at the same time which is even more important (a big difference to Access which consists only of one database at a time).

"The instances are installed on the hard disk" - that's of course always the case with any program and offers no meaningful information. So if you want to tell something about the installation it would be more interesting to tell that a SQL Server can spread the files it uses for the databases over multiple ressources which can also be on different computers.

"and user interacts with them via SSMS" - of course not a user, only a developer, like Kelvin already said.

"The leading string before the instance name (USER-PC\SQLEXPRESS12) is the local computer name." - That's wrong, the leading string is not necessarily the local computer name but the name of the server which hosts the database engine (which also not necessarily hosts the database files). You can of course install SQL Server locally on your computer, but that's not the "normal" case, normally it is a server on the network.

I would also remove the analogy to the restaurant as this does not really work.

The security model is also wrong described. With all three authentication modes the sysadmin of the database server (and not the server admin, which has normally nothing to do with the database installed on it) needs to add a user as a database login user as first step, otherwise he cannot login to the server generally. These logins, which are created basically on the security area for the whole database server, then needs to be added (mapped) to the database(s) the user should work with. For example, adding a user "U1" as login user gives no permissions to the target database which should be used for an application. If you have "DB1" and "DB2" and "U1" should only use "DB1" you need to map him to "DB1".
That's also not all, next step is to add him to a role (standard is "public" only). All steps from here can also be done by a user which is added to the "db_owner" role so this is not necessarily be done by the sysadmin of the database server. The reason is that a sysadmin could for example be a database hoster service which offers databases for external developers, so he can give this external customer a db_owner right for a specific database (i.e."DB1" only) so he can work with everything about "DB1" but nothing else on the database server (of course also some permissions to standard databases like the temp database etc.).

So back to your doc:
"Windows Authentication:
SQL Server trusts user name and password used for Windows login." - no, a trust would be general and what you say is that it is enough to have a valid Windows login. That's wrong, the only advantage of a Windows login is that the user don't need to enter additional user/password information and that it is not sent over the network like with SQL Server login, but to access the server he needs of course to be added by a sysadmin as login for this server (otherwise a user in a domain but be able to login to any SQL Server on the whole company - would be very fatal).

"Local SQL Server Installation." - no, of course not. It's the same on local or remote server, the only thing which you perhaps got from a local installation is that you are admin on your computer (otherwise you would not be able to install SQL Server) and so you are automatically added as sysadmin for this server. Look into the "security" folder in the main root, you'll find your username as login for this database server.

"User may click on   and select an SQL Server instance to see and work with all databases in a SQL Server with almost no additional restrictions." - no, very, very wrong. That's also the case only in the above described scenario where you were added as sysadmin during installation. Any other user must be inserted as login user and exactly mapped to a database to work with it. Normally you would also not add a user directly but a domain group instead and insert the user there, then the group would be assigned to a role, makes the life with permission a lot easier.

The rest you described is also wrong, Mixed or SQL Server login modes have nothing to do with local or network installation, and also the server admin normally has nothing to do with the sysadmin (which is the highest privilege on SQL Server while the server admin has admin/domain admin rights on the Windows server itself, CAN of course be the same person, but intentionally and normally is not).

Additionally, a new installation of any SQL Server is cutted off from the network to allow secure configuration prior to first access from network. You need to enable the network protocols in the SQL Server Configuration Manager which is a separate tool and you also need to enable the remote access (since 2008 version) in the database instance itself.

So if you want to compare the security model with the real world, you may compare it to the security model of a company: You are blocked in the entry as guest or new employee and get no access to the buildings behind the fence (=no domain user). Next level is to get an access card to enter the ground (=added as domain user because you are a new employee of the company). But you cannot enter any building (database server/database). So you get additional permissions by the security of the company to enter a specific building where your workplace is, you cannot enter any other building (=added as login on the database server). You can now go to your workplace but you can't access the filing cabinet as you have no key for it. So the department lead (=sysadmin) or the cabinet owner (=db_owner/db_securityadmin) can give you a key (=add the login as database login). Now you can open the cabinet but you see further locks on any kind of object so you cannot see the contents of the cabinet (=you can open the database but you cannot access anything as long as you are only in the "public" role). One of the three others (sysadmin/db_owner/db_securityadmin) must give you access to each of these objects by assigning you to a role with specific rights (like "db_datareader" to see and open anything but not changing anything). All these steps are necessary in any case. Because you can have an access card of your company which you can hold before a card reader (=Windows Domain Login) to get the permissions the admins gave to you or you can enter some digits on a keyboard (=SQL Server user and login input) to get the same access.

Also this analogy is not perfect, but at least a lot better than the restaurant (you work with data which is available to anyone at the same time, getting food is made especially for you and nobody else will eat the same food at the same time which is physically not possible). There is also no quality difference between a simple user and a developer and a sysadmin: If they can access data, they all get the same data with the same comfort. Eating in a fast-food restaurant surely is something else then eating in the high level restaurant, even if both offers the same food.

Finally, as Kelvin said above, SQL Server is far more complex and cannot be reduced even to the database engine (which is a lot more complex than this). Think of other services like analysis services, notification services, reporting services, integration services, all components of one installation of SQL Server. So depending on what you want to achieve with this document this should all be mentioned and at least described in short.


Kelvin SparksConnect With a Mentor Commented:
Hello Mike

What you've written is a very basic introduction to setting up and interacting with SQL Server. Yes, you can have multiple instances of SQL server and as you say with different instance names.

SQL Server is a database engine - but it is a very complex one. In a nutshell, it stores and protects data. SSMS is a tool provided to interact with SQL Server - generally by administrators or application developers - NEVER by users. Not only does SQL Server store data, but it also devlivers differing views of that data (views) and allows interaction with the data using T-SQL (Transact SQL). This is using through stored procedures, functions and sql passed by user applications.

As you delve deeper into it, you find so many more layers of complexity, that it is almost an OS itself. Not only does it manage its data, but can interact with data and objects outside of its own databases.

I know you've come from an MS Access background (like me). If you take a secured access application, and image moving everything (including code modules) to SQL Server except the forms and reports (less their underlying code) then you're starting to see some of the range of things sql server does. One thing sql server has, that Access does not is its own scheduling system (SQL Agent). SQL Server - especially the later versions, can make very good use of PowerShell and there are PowerShell applets for SQL Server.

Hope this helps (although I suspect that this raises more questions than it answers.

Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You need to review the SQL Server Security section. Is nothing that wrote and the analogy with the restaurant is far to be accurate.

Windows authentication vs SQL Server authentication vs Mixed authentication doesn't have nothing to do with local or network installation.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LowfatspreadConnect With a Mentor Commented:
"Note: This is prepared for person with little programming skills and interest in the subject."

I agree that the security section is very wrong.

There are only now 2 modes of authentication (2012/2014...) Windows and Mixed...

And all they do is specify how your users will be identified to the database engine so that object/procedure permissions can be validated...

My real point is I'm still not clear who/what your audience is intended to be...
Is it just a How to get to query analyzer and run some sql?
   or How to control user access to data ?


or something else...
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thanks to all,

Could my restaurant analogy be improved so I can keep it? It is good to draw links between what people already know and what they are struggling to learn.

Who are the users for this writing are:
- Myself to make sure I understand it.
- My college kid and friends.
- Number of my ex-project managers (now friends) who could really use some knowledge about SQL Server.
- All above who also need to use some basic T-SQL.

There are many good comments and I will improve the doc file using them.

David ToddConnect With a Mentor Senior DBACommented:

This might be out of scope, but I'd be tempted to describe the acronym acid to them, to show how complex the issue of storing data is, and what exactly the database does for them.

Are you specifically describing SQL? vs what? (Oracle/DB2/MySQL/etc) or client-server rdbms's in general? (not counting Access as while it is a rdbms, it isn't properly client-server ...)

Now what is the end-game? College kid is different to ex-project managers. Can one assume that project managers know about database servers? In what field were they project managing in? Surely IT project managers should have a fundamental grasp of what a client-server rdbms can do for them.

David ToddSenior DBACommented:
PS Are you selling SQL or your services to manage existing SQL instances, and install new ones?
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
To All,

Thank you for great comments and corrections. I have reduce from 500 words down to 380 words while improving its content per your comments.

The revised version is at http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28599879.html if you like to take another look at it.

Todd, I am preparing to get MTA 98-364. I should have moved away from MS Access longtime agao.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.