November 26, 2005 at 5:33 am
hi,
please clear my doubts.
1. How many INSTANCES can be open simultaneously in a MS SQL SERVER?
2. What are the daily activities of a typical DBA?
thanks
JK.
Thanks
Jay
http://www.sqldbops.com
November 26, 2005 at 5:48 am
#1 is much, much easier than #2. #2 there are some articles on, but the term "DBA" now is used for a rather broad set of duties. For instance, in my organization we have development DBAs, production DBAs, a data architect, etc. As for #1:
When SQL Server is configured to run under the default instance (no named instance), it configures itself to run under the service known as MSSQLSERVER. SQL Server Agent runs under SQLSERVERAGENT. By default it listens on TCP port 1433. It has a default named pipes entry, too. The default instance also has its own folder structure for both binary files and data files (usually C:\Program Files\Microsoft SQL Server\MSSQL\ and below). It also has its own registry locations.
However, SQL Server instances are different. While still running the same executible, the first thing you notice it is in a different directory that contains the name of the instance (default is C:\Program Files\Microsoft SQL Server\MSSQL$<Instance Name>\). It has its own registry locations. Also, it's services are MSSQL$<Instance Name≶ and SQLAgent$<Instance Name> respectively). Also, the setup will randomly pick a port for the SQL Server instance to listen on other than TCP port 1433 and set the instance to dynamically determine the prot whenever it starts up. If you don't change anything, the SQL Server instance will check to see if it can use the same port as when it was last up, if so, it'll reuse it. If not, it'll pick a different port and come up on that. Of course, you can hard code an instance to listen on a port, just like you can tell a default instance to listen on something other than TCP 1433.
Now, how do clients figure out how to connect to Server\Instance, especially when an instance could listen on just about any port? When a instance aware computer (MDAC 2.6 or higher is installed), it'll talked to UDP port 1434 on the SQL Server. That's the SQL Server listener service (it starts up the first time any SQL Server service starts but it's not a "service" in a traditional sense in that you can't look at the list of running services and see it). The client basically sends a communication that amounts to, "Tell me about yourself." The server responds back with the instances running on it and their network connectivity information.
HTH.
K. Brian Kelley
@kbriankelley
November 26, 2005 at 5:58 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply