February 28, 2016 at 8:40 am
hi all,
Nice to meet everyone here. I used to be doing Oracle/Unix/Linux environment and as the topic goes, i am new to MS environment/SQL server. To be honest, i am hitting some bums and knocks now while trying to pick up the technology... and its pretty demoralizing 🙁
Hope you gurus can point me in the right direction on some concepts and understanding of MS/SQL server ...:-D
Here come the questions..
Q1) where is SQL server 2014 official documentation from Microsoft ? I try googling, but i always end up at 2016 technical documentation. 2016 seems to be pretty new, (try the Evaluation Center, it is still in preview)
Q2) Installing SQL server
I have completed installing SQL server 2014 but during the installation, i encounter this configuration part whereby you can choose the "Service Account" to "Logon As" for the individual SQL services.. Can i ask
- by choosing/specifying the service account to logon as, does it means that I got to logon as that choosen account in order to startup the service ?
- and the above is totally not related to "logging into" the database (OS authentication), which another account can be designated - meaning, the logon account for the service and logging into the instance/database(OS authentication) are 2 different thing and I can use 2 different account ?
Regards,
Noob
February 28, 2016 at 1:48 pm
Q1:
The documentation pages currently default to SQL Server 2016 (unfortunately, since it is not even officially released yet; it's in CTP - a fancy term for a public betatest).
However, on most documentation pages near the top, next to the text "SQL Server 2016", is a hyperlink called "Other versions". Click this and select a version from the dropdown.
Q2:
SQL Server runs as a service (a background process) on the server that it is installed on. When SQL Server starts, the OS has to know what rights to assign to this service. That is what the service account is for. The best practice is to create a domain account specifically for this purpose, and using group policy remove the "Log on interactively" (if I recall the name correctly) right. It does need the right to log on as a service, but that will be set automatically when SQL Server installs, or when you change the service account the right way (through SQL Server Configuration Manager). If you change the service account through the standard Windows Services control, you will have to assign this right (and lots of other permissions) manually.
You do not log on to SQL Server using the service account. SQL Server listens on various communication channels (such as TCP/IP and Named Pipes) for incoming connections. It then logs you on using either Windows Authentication (which means that Windows has already verified that you are who you claim you are when you logged on, and now merely passes a security token representing your domain account), or SQL authentication (which means that you type in a user name and password that is checked by SQL Server). The former method is the most secure, and by default is the only method allowed on new SQL Server installations.
So, long story short - service account is a special account used for the SQL Server process itself, which has permissions to read the database files, write to the backup directory, and do magic stuff in the OS to optimize the SQL Server process. You log in (preferably) using your own domain account. Which only works if someone with sysadmin privileges has granted access to your account in SQL Server (or if your account is a member of the sysadmin group on the domain).
I hope this helps. It can be confusing, I know, so do ask if you need further clarification.
February 29, 2016 at 4:51 am
Hi Hugo,
Thank you for your valuable input.
I would like to apologize if my questions sound rather irrelevant at times as I really have minimal expierience in windows environment.
1) I tried the "Other version" option and choose SQL Server 2014.
However, i am not able to find any concept related documentaion
(e.g. relationship between instance/database in SQL server, logical/physical structures in SQL server etc).
Did I look under the wrong place ?
2)
service account is a special account used for the SQL Server process itself,
which has permissions to read the database files, write to the backup directory, and do magic stuff in the OS to optimize the SQL Server process.
But who will grant the permission to the service account ?
and what account should we use to install SQL server 2014 then ?
e.g if i use "domain/accountA" to install SQL server, and during installation, i specify "domain/accountB" for starting certain SQL services - who, how does accountB gain the necessary rights to start the services ?
3) when talking about OS authentication (does it mean local, or remotely)
how does it work in the scenario of 1 connecting from a remote server/computer.
e.g.
Computer B (client)
Computer A (SQL server running) (added "domain/accountB" for OS authentication)
If a user login to Computer B using "domain/accountB",
does it means he/she is able to login to SQL Sever in Computer A (using OS authentication method) ?
or the user has to login to Computer A locally ( to be considered for OS authentication )
Regards,
Noob
February 29, 2016 at 6:09 am
szejiekoh (2/29/2016)
But who will grant the permission to the service account ?and what account should we use to install SQL server 2014 then ?
e.g if i use "domain/accountA" to install SQL server, and during installation, i specify "domain/accountB" for starting certain SQL services - who, how does accountB gain the necessary rights to start the services ?
The installer will grant the necessary permissions as part of the installation process.
does it means he/she is able to login to SQL Sever in Computer A (using OS authentication method) ?
Yes, she will.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 29, 2016 at 6:37 am
Hi GilaMonster,
Thanks for the reply.
The installer will grant the necessary permissions as part of the installation process.
You mean the installer will grant the necessary permissions to the "log on as" service account ?
How does the installer have the rights/permissions to grant the necessary permission to the service account ?
Do we need to install the installer using an Administrator account then ?
( i am new to windows, but i always thought to grant certain permission, you have to have the same permission or "greater" permission/rights to do so, such as an Administrator account)
does it means he/she is able to login to SQL Sever in Computer A (using OS authentication method) ?
Yes, she will.
But does UserB need to logon as "domain\accountB" in ComputerA where the SQL server is running to be consider as "OS authenticated" or UserB can login at any computer in the domain as "domain\accountB" and is still considered as OS authenticated and is able to access to the SQL server in Computer A remotely from any computer ?
Regards,
Noob
February 29, 2016 at 7:21 am
If you mean who will set up the service account and give it the right permissions, that would be your Active Directory/Windows Server/Infra/sysadmins depending on what you call them.
February 29, 2016 at 7:32 am
szejiekoh (2/29/2016)
UserB can login at any computer in the domain as "domain\accountB" and is still considered as OS authenticated and is able to access to the SQL server in Computer A remotely from any computer ?
Yes. She's logging into the domain, not into an individual computer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 29, 2016 at 8:09 am
GilaMonster (2/29/2016)
szejiekoh (2/29/2016)
UserB can login at any computer in the domain as "domain\accountB" and is still considered as OS authenticated and is able to access to the SQL server in Computer A remotely from any computer ?Yes. She's logging into the domain, not into an individual computer.
Hi GilaMonster,
so "OS authenticated" doesn't mean that the client have to be logon physically on the same machine the SQL server is running, it just means that the client is logged on the "OS/domain account" , which is granted access to the SQL server and the client can access the SQL server from any machine in the domain as long as she is using the correct "OS/Domain account" - am i right ?
February 29, 2016 at 8:11 am
Beatrix Kiddo (2/29/2016)
If you mean who will set up the service account and give it the right permissions, that would be your Active Directory/Windows Server/Infra/sysadmins depending on what you call them.
Hi Beatrix,
But as per Gilamonster has mentioned earlier, he mentioned that the installer will grant the right the permission to the Service account during installation. I am not sure how it works on the backend and what permissions are needed, but how does the installer have the correct/greater permission to grant permission to another account ?
This is why i am asking if we need to login as Administrator to run the installer ?
Regards,
Noob
February 29, 2016 at 11:01 am
Hi Noob (is that actaully your real name??),
However, i am not able to find any concept related documentaion
(e.g. relationship between instance/database in SQL server, logical/physical structures in SQL server etc).
Did I look under the wrong place ?
Not sure if I have a good link for you on this. But here are the most important things in a nutshell:
1. You can install SQL Server multiple times on the same server. Those multiple installations are called "instances". They share some executables and compate for the same resources, but other than that are considered just as separate as SQL Server installations on different servers. Installing multiple instances was a nice trick to make better use of over-provisioned servers. However, in the current days of virtualization that same effect can be achieved by creating multiple virtual servers and installing one instance per server.
2. Within each instance, there are multiple databases. Some of them are always there and reserved for system use (master, model, msdb, tempdb). The others are user databases. Those databases are mostly independent.
3. When accessing objects, the default is to use two-part naming: schema + object name. This means that the object exists in the current database (as set in the connection string or by the USE command). But you can add a databasename in front of the schema to access objects in another database, or add an instancename and a databasename to access objects in a database on another instance - which might or might not be on the same server. Of course, all this assumes that you have the privileges to access those objects.
I do not know much about Oracle, but I believe that schema and object are familiar concepts. The database and instance layer are added on top of that in SQL Server.
The relationships are: 1 (physical or virtual) server can host zero or more SQL Server instances; one instance hosts four or more databases, each database contains one or more schemas, and each schema contains zero or more objects - which may be tables, views, stored procedures, etc.
You mean the installer will grant the necessary permissions to the "log on as" service account ?
How does the installer have the rights/permissions to grant the necessary permission to the service account ?
Do we need to install the installer using an Administrator account then ?
Not sure if someone already answered this, so I will. Yes, you will need to have admin privileges when you install SQL Server. And that's why the installer can assign all required permissions to the service account(s) you specify.
I never had to have other admins request privileges before or after a SQL Server install, as Beatrix suggests. Perhaps that is an option if you install SQL Server without having sysadmin rights yourself? I have never been in that situation.
But does UserB need to logon as "domain\accountB" in ComputerA where the SQL server is running to be consider as "OS authenticated" or UserB can login at any computer in the domain as "domain\accountB" and is still considered as OS authenticated and is able to access to the SQL server in Computer A remotely from any computer ?
As Gail (GilaMonster) already said, you log in and gain access through your domain account. If the SQL Server instance is running on a different computer in the same domain, that just works. If it is part of another domain, you'll have to see the domain admins about setting up trust relationships between the domains.
March 1, 2016 at 5:17 am
Hi Hugo,
Thanks for the wonderful reply !
You mean the installer will grant the necessary permissions to the "log on as" service account ?
How does the installer have the rights/permissions to grant the necessary permission to the service account ?
Do we need to install the installer using an Administrator account then ?
Not sure if someone already answered this, so I will. Yes, you will need to have admin privileges when you install SQL Server. And that's why the installer can assign all required permissions to the service account(s) you specify.
So which account should I use to install SQL server ? What group does the account need to be in ?
Is it that I will use an Administrator Account (either local/domain administrator) to do the SQL server installation and specify normal user account as "logon as/service account" for the services ?
P.S. noob is not my real name... it just a nickname for the situation on myknowledge on SQL server at the moment 😉
Regards,
Noob
March 1, 2016 at 5:29 am
szejiekoh (3/1/2016)
So which account should I use to install SQL server ?
One that has administrative privileges on the server. That's all that's needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2016 at 4:54 am
You will find there are many similarities between SQL Server and Oracle, as well as many differences. It is a long time since I used Oracle, but my memory is that some of the concepts you are familiar with have different names in Oracle and SQL Server (easy to deal with) but there are a few places where the same name name is used in SQL Server and Oracle for completely different items (harder to deal with), so check out the 'books online' documentation to get things clear in your mind.
The two biggest differences between SQL Server and Oracle are a) the concept of instances and databases, b) How readers and writers are handled.
Oracle and SQL Server share the concept that a database is a container for tables. I think in Oracle there is a one to one relationship between instance and database, while in SQL Server it is a one to many relationship between instance and database - a single instance can host multiple databases. As has previously been said, SQL Server has a few 'system databases' that hold instance-level metadata and functionality needed by all databases. Additionally, all unit of work temporary data is held in the database known as tempdb, one effect of this is that user databases do not have a temporary area.
SQL Server, Sybase and DB2 share the same default way of dealing with readers and writers, while Oracle takes a different default. By default in SQL Server, locks taken by a reader can block a writer, and locks taken by a writer can block a reader. Oracle uses the concept of 'rollback segments' that mean readers never block writers and writers never block readers, except where schema changes are involved. Oracle also guarantees unit of work stability, in that a reader will get the data that was in the row when the unit of work was created, regardless of when the row is actually read.
If you turn on 'Snapshot Isolation' in SQL Server, then you get the same behaviour as in Oracle, but this is implemented in a different manner. Within SQL Server, information similar to Oracle's rollback segments is held in the tempdb database.
Some of the ways that SQL Server works may seem crazy to someone who has only known Oracle, but the same feeling applies in reverse. I have been fortunate enough to work with all of what were called the 'big four', as well as a number of other database managers. They all have their own way of doing particular tasks, but share the same core principle: keep active data in memory, minimise reads from disk, but get changes back to disk as soon as possible. No database manager is perfect, but the ones that are commercially successful have proven they are 'good enough' for the job in hand.
I hope you enjoy your learning curve with SQL Server. You may find a few things where you think SQL Server does it better than Oracle, but there will also be a few other things where Oracle does it better than SQL Server. The same applies with Sybase and DB2, Redshift, etc.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 2, 2016 at 9:12 am
Hi Edvassie and all,
Thanks for the detailed and thoughtful explanations!.
Being new to the forum, is there a way to mark all or most of the answers correct or as helpful at least ? :blink:
Back to the topic, yeap, the instance and database portion confuse me abit. 1 part I don't understand is, if the system databases holds metadata / login information / etc for the user databases, why doesn't it need to be backup consistently together with the user databases ?
I understand that in SQL server, user databases can be backup and restore separately. Wouldn't this contribute to consistency issue ?
T1 - system and user database created
T2 - system database backup
T3 - user created some objects (will this objects metadata (e.g. structure, statistics information etc) be recorded in system database ?)
T4 - user database backup.
T5 - server crash
-- if we restore T2 system database backup with T4 user database backup, wouldn't we lost the T3 objects metadata ? (maybe system database dont store them... i don't know.. )
Maybe, i am still stuck to the idea in Oracle that system tablespace and user tablespace/schemas are all part of 1 database. To have a consistent restore, the whole database (system/users etc) must be backup and restore together.
Hope to hear some advises.
1 more thing, its seems that there isn't much concept information about SQL server from Microsoft.. - am i not searching thoroughly enough ? Just find it hard to understand the working of SQL server without understanding the concept and architecture behind.
Thank you!
Regards,
Noob
March 2, 2016 at 9:16 am
szejiekoh (3/2/2016)
T3 - user created some objects (will this objects be recorded in system database ?)
No, they will not be in the system databases.
The system DBs store database info (what databases are part of the instance), login info (passwords, server-level permissions), jobs, backup history, stuff like that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply