How to install SQL Server express on server ?

I have completed developing my web app. Now I want to deploy my website on server. The server doesn't have sql server so I am thinking about install sql server management studio express 2008 RS because it is available free.

OR

I just have to install SQL Server 2008 Express R2 from here http://www.microsoft.com/express/Database/ ? This is free and full version, ?

which one I need to install ?

while installing do I have to install .\SQLEXPRESS instance or not ? can connectionstring on server work with .\SQLEXPRESS or it needs server name with username and password ?

If I install it with default settings, how I will login in sql server ? Server name will be PC/Server name, right ?

what will be "Authentification" ? WINDOWS AUTH or SQL SERVER AUTH ? If it is SQL SERVER AUTH, then what will be the password ?

How to create username and password ? How to create login so that in my connection string I can use userID and password ?

Things to keep in mind while installation ?

What else I need ?

HELP

 

 

 

 

Hi Jeff,

Here are my thoughts on this...

Jeff Carter said:

have completed developing my web app. Now I want to deploy my website on server. The server doesn't have sql server so I am thinking about install sql server management studio express 2008 RS because it is available free.

OR

I just have to install SQL Server 2008 Express R2 from here http://www.microsoft.com/express/Database/ ? This is free and full version, ?

You can download the Express edition for free. You need to instal SQL Server 2008 Express (the one from http://www.microsoft.com/express/Database/) and check if your server runs on 32bit or 64bit CPU, to get the appropriate version. When installing SQL Server 2008, you will be asked for the Server name or you can use the DEFAULT instance.

Jeff Carter said:

while installing do I have to install .\SQLEXPRESS instance or not ? can connectionstring on server work with .\SQLEXPRESS or it needs server name with username and password ?

If I install it with default settings, how I will login in sql server ? Server name will be PC/Server name, right ?

what will be "Authentification" ? WINDOWS AUTH or SQL SERVER AUTH ? If it is SQL SERVER AUTH, then what will be the password ?

How to create username and password ? How to create login so that in my connection string I can use userID and password ?

Mainly, if you have installed SQL Server in your own machine, installing in your Windows Server 2008 should be really different. Mainly, if you install it with, lets say 'Administrator' user, you will be able to open the SQL Server using SQL Server Management Studio as sysdba using Windows Authentication for that user. The server name will be the name you will specify in the installation steps, but you should be also able to access it by only typing '.'. If you want to access your database with specific username/password, you will have to enable SQL Server and Windows Authentication mode (known as Mixed mode). You can do this with Right click on Server Name -> Properties -> on the new window click on -> Security -> and you have:

Server Authentication

• Windows Authentication
• SQL Server and Windows Authentication (you should select this)

Then, in Security folder -> Logins -> Right click and create New login. You can specify which db this user can access, its password and the permissions level.

 

Jeff Carter said:

Things to keep in mind while installation ?

While installation, it's recommended not to change the default name of your server.

Jeff Carter said:

What else I need ?

You can test your connection string to your server/database and automatically creating connection string using UDL files. I wrote a blog post about one year ago about this, you can find it on the following link: http://codeasp.net/blogs/hajan/microsoft-net/857/working-with-udl-universal-data-link-files

Jeff Carter said:

HELP

I hope I've helped ;)

Regards,
Hajan

 

thanks for the explaination.

so what I have understood is that I have to install SQL Server  express by using default settings and no need to install SSMS.

After I install SQL Server 2008 exp, I can login by server name (as pc name) and if I want to create a user then I can follow the steps above.

How I can find out the I.P address of the server ? because for testing I will be using url something like: http://192.168.1.2/mysite/default.aspx

As far as I know I also have to configure IIS and make virtual directory and make web folder as application, how to do that ?

what if server doesn't have IIS ?

Jeff Carter said:

thanks for the explaination.

so what I have understood is that I have to install SQL Server  express by using default settings and no need to install SSMS.

After I install SQL Server 2008 exp, I can login by server name (as pc name) and if I want to create a user then I can follow the steps above.

How I can find out the I.P address of the server ? because for testing I will be using url something like: http://192.168.1.2/mysite/default.aspx

As far as I know I also have to configure IIS and make virtual directory and make web folder as application, how to do that ?

what if server doesn't have IIS ?

Jeff, You will need SSMS if you want to manage your databases and also create users and other stuff much easily. SSMS is for Managing your SQL Server.

You can find the IP address of your server from command prompt. Go to Run and type cmd then type ipconfig. There is your server ip address.

For the IIS, usually in all Windows Servers, IIS Web server should be already installed. However, to check whether you have it preinstalled, go to Control Panel -> Administrative Tools and check if there is Internet Information Service (IIS) Manager. If you have it, then you have it installed and you can create/configure your apps. If you don't have it, then go to Programs and Features and click on the left side in Turn Windows features on or off... There in 'Features' you will find all the features that you can install in your server.

Hope this helps...

Regards,
Hajan

thank you hajan

1. In past I have installed SQL server and SSMS. Sorry for a newbie question but I am little confused now.

If in this situation I need to install SSMS then is it necessary to install SQL Server express ? why can't just SSMS can do the job ?

Doesn't SSMS install sql server also ? or I would need to install both SQL server and then SSMS specifically ?

 

2. While installation is .\SQLEXPRESS will be the default instance ?If I do so I know while login / opening SSMS I will have to login with server name ".\SQLEXPRESS"

if while login to SSMS I want server name to be "the name of the server / server's I.P address" , what things I need to do while installation to do this ?  sorry I am lost, I just don't want to mess up the installation process on the server.

Jeff Carter said:

thank you hajan

1. In past I have installed SQL server and SSMS. Sorry for a newbie question but I am little confused now.

If in this situation I need to install SSMS then is it necessary to install SQL Server express ? why can't just SSMS can do the job ?

Doesn't SSMS install sql server also ? or I would need to install both SQL server and then SSMS specifically ?

Well, SQL Server and SQL Server Management Studio are separate and should be installed separatelly. If you install SSMS and you haven't installed SQL Server previously, you won't have which server to manage. So, SQL Server is the Server you need for your databases to work... while SQL Server Management Studio is GUI that can help you manage your databases easier. You can still manage your databases from SQL Server command prompt, by typing commands :), if you don't have SQL Server Managemnet Studio. So, they are separate and you will need to install both, recommended first to install SQL Server and then SSMS. SQL Server is required for your application to work, while SSMS is not.

 

Jeff Carter said:

2. While installation is .\SQLEXPRESS will be the default instance ?If I do so I know while login / opening SSMS I will have to login with server name ".\SQLEXPRESS"

if while login to SSMS I want server name to be "the name of the server / server's I.P address" , what things I need to do while installation to do this ?  sorry I am lost, I just don't want to mess up the installation process on the server.

If you don't have another instance, it should be .\SQLEXPRESS or .\MSSQLEXPRESS, you can see it in the installation wizard steps. Once installed, you can access the server using Windows Authentication with the Administrator user by typing the SERVERNAME only (assuming you don't change anything while installing the SQL Server - everything is set by default). So, keep the things as they are default in the installation process...

Hope this helps.

thank you for the info.

I did install everything on the computer and here is where I am stuck

I installed SQL Server 2008 Express R2 and I didn't have to install SSMS because after the installation of SQL Server 2008 I saw, SSMS in the start menu. So I am able to see use it.

During the installation process of SQL Server 2008 express r2 I kept all the default settings.

Right now when I open SSMS I login like this:

Server Name :PCNAME\SQLEXPRESS

Authentification : Windows Auth

Now I was confused how to make changes in webconfig to connect with database on SQL server.

This is what I was using:

<add name="myconnection" connectionString="Data Source=PCNAME\SQLEXPRESS;Initial Catalog=mydatabase;"
       providerName="System.Data.SqlClient" />

I also tried this:

<add name="myconnection" connectionString="Server=PCNAME\SQLEXPRESS;Database=mydatabase;"
       providerName="System.Data.SqlClient" />

And this:

<add name="myconnection" connectionString="Data Source=PCNAME\SQLEXPRESS;Initial Catalog=mydatabase;" />

But all the connection giver SQL network connection error and sometimes I t says login failed.

So I thoughy may be it requires login.

I created a user in SSMS > security > login > new user

But still I get error when I include the details in webconfig.

Also I have noticed that the user I created, when I disconnect in SSMS, and try to connect again with :

Server Name :PCNAME\SQLEXPRESS

Authentification : SQL Server Auth

User: the new username

Password: password

I GET " LOGIN FAILED"

(I also did the change in SERVER Name Properties > mixed authentification - still I can't login with the user)

Where I am going wrong, how to create user ? If I created user correclty then why I can;t login with that user ?

please HELP me

Hi Jeff,

Please try to use UDL file to create your connection string. See my blog post here: http://codeasp.net/blogs/hajan/microsoft-net/857/working-with-udl-universal-data-link-files.

Once you successfully connect with the wizzard, save it and then open the file with NOTEPAD. There will be your connection string. In the blog post I explain what you need to copy from the text in the file to put in your web.config.

This should work. If not, reply again.

Regards,
Hajan

hi hajan,

I tried it but when I put PCNAME\SQLPRESS in server name, it connects sucessfully

but when I try to see the database from the drop down menu I don't see my database, why ?

I can only see 3 system databases :master, tempdb and msdb

PLZ HELP ITS URGENT

FYI: I am in the list of local administrator on the server (if it helps)

 

Jeff, you may have two instances of SQL server running.

Instead of adding PCNAME/SQLPRESS, try adding only . (dot) and check the list of databases you will get.

Another way would be to see how do you connect to your db engine using SSMS where you have created your database. See the server name you use there. Moreover, you can check the server name by going to Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager. In the SQL Server Services, you will find the running instances of your SQL server, there you will see if you have multiple instances like SQLEXPRESS, MSSQLEXPRESS etc. Try connecting using . (dot) or .\SQLEXPRESS

Hope this helps.