SQL Server 2005

[Tony Johansson]

Hello!

SqlConnection myConnection = new
SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" +
"Database=northwind");

When I have this Integrated Security=True does this mean that I will
automatically have access to this database norhwind
when I have logged into windows ?

My next question this entry sqlexpress is the connection string is the
instance name for the sql server according to a book a reading.
What does this actually mean because I have never heard about instance name
for a Sql Server ?

//Tony

 

[Alberto Poblacion]

SqlConnection myConnection = new
SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" +
"Database=northwind");

When I have this Integrated Security=True does this mean that I will
automatically have access to this database norhwind
when I have logged into windows ?

Not necessarily. It means that your Windows credentials will be passed
to Sql Server. This will only grant you access to the database if Sql Server
is configured to accept those credentials. A default install of Sql Server
maps the Administrators group to the sysadmin role in Sql Server, so every
Windows Administrator will be granted access to all databases in Sql Server.
Plain users will not be able to acess the database unless you add their
credentials into Sql Server.

My next question this entry sqlexpress is the connection string is the
instance name for the sql server according to a book a reading.
What does this actually mean because I have never heard about instance
name
for a Sql Server ?

During installation of Sql Server you are prompted to select either a
"default instance" (you can only have one default instance) or a "named
instance", which allows you to install several copies of Sql Server in the
same machine if you provide different names for those instances. The Express
version of Sql Server that is provided with Visual Studio defaults to the
instance name "SqlExpress" during installation.

 

[Marc Gravell]

When I have this Integrated Security=True does this mean that I will

automatically have access to this database norhwind
when I have logged into windows ?

Well, it will try to connect to the database with the executing user's
credentials, if that is the question. So "yes", /if/ you have a login
to that SQL Server associated with your windows account.

What does this actually mean because I have never heard about instance name
for a Sql Server ?

A named instance is basically a way to run multiple servers (each with
their own databases and configuration) on a single host while keeping
more isolation than just databases (on a single server instance) would
allow. Under the bonnet I believe each maps to a TCP port (or ports),
but using a name is more convenient in many cases (but there are some
tricks if you are coming through a firewall to a database cluster).
The express editions install themselves as .\sqlexpress.

Marc

 

[Pavel Minaev]

Hello!

What is the reason for having several copies of sql Server in the same
mashin ?
The only reason I can imagine is when having different versions ?

Different instances can have very different configurations (e.g., to
name some of the more obvious, one may have TCP/IP enabled, another
may have it disabled). Also, since they are different processes, one
crashing for whatever reason would not disturb the rest.

 

[Tony Johansson]

Hello!

What is the reason for having several copies of sql Server in the same
mashin ?
The only reason I can imagine is when having different versions ?

Do you agree with me ?

//Tony

 

[Marc Gravell]

Other scenarios:

You can use a single host to co-host several logical servers that (in
production) would have isolated hardware - in particular this lets me
have an isolated dev and QA setup on the same machine, i.e.

..\dev (databases: foo, bar)
..\test (databases: foo, bar)

(without having the overhead of lots of VM instances etc, which might
be another option - but with different OS licencing).

This might also be a sensible approach if you either want to replace
several old servers with a single big grunty box (comms room space/
power is expensive), or if you want to host things on a single machine
today, with a view to separating them later as usage increases
(without having to worry about any accidental cross-database ties).

Security would be another issue, as would redundancy; if I recall,
some of the failover-cluster options in SQL 2005/2008 mandate named
instances.

Marc