More problems attaching a database

Hi Mike,

I've had a look at the white paper on use instances and also stopped using Management Studio, as you suggested. The problem is that I can run my application from VWD quite happily, until for no reason the application can't connect to the database. This happens ramdomly and is very frustrating. I have worked out a way to correct this problem with the following method (I don't know if this is correct but it's the only work around I know.

1) I close down VWD and open Management Studio,

2) I right click to attach a database, and then navigate to the .mdf file for the database, and click to attach it. I then delete the existing icon for that database (which I can't acess anyway, it only shows me the main icon but not the tables)

3) I rename the new instance of the database to the correct name (minus path info etc)

4) I create an SQL script as follows:

EXEC sp_grantlogin 'jon\ASPNET'

(This line executes correctly),

Then I enter these lines:

USE BalloonShop \*name of database \*

EXEC sp_grantdbaccess 'jon\ASPNET'

(At this stage when I execute the query, Management Studio informs me that this name already exists in the database and so doesn't execute)

5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.

I know I've got some configuration settings wrong somewhere but I find the whole user instance thing confusing. For the record I'm running the appllication through VWD's built in web server, I did originally have it through IIS but I moved it (this will explain the confusion of locations from my first post, I gave you the original location, sorry about that).

Any advice you can give would be gratefully recieved, ie is there an easy way to reconfigure my settings to make everything work as expected or have I got to start the database again from scratch (not a pleasent thought). Also is there an idiots guide to SQL Server Express anywhere that I can read.

Jon

ps I've just realised that I get the 'can't connect to database problem when running the application after I've made a change to the database from VWD ie adding a new stored proceedure, Don't know if this helps, but better you get the full picture.

I havn't read your first post.... what is the summary of what you are trying to acheive.

I have used SQL Express with VWD for a while now and have had no problems moving the database from my VWD environment to a production system. The first thing is that when using the VWD the Databases are created in the APP_Data directory and the connection string used inside the web.config file has a parameter that attaches the database at runtine to the Database Engine it also configures the security so only the Windows Account that has attached the Database can access the Database Files (User Instances). When moving to production systems or attaching it to an existing Engine the first step I would do is to take all of the files (MDF and LDF) for the database and put them in another directory, away from the ASP_Data directory in the root of your web application. Normally this would be the default data directories for the Database server that you are going to use. Next inside the management studio attach the database files and set up the security for the user that you are going to be using.

In my case here I normally create an application pool for the Web site to use in IIS and set the app pool to run under a user, I would then configure this user to have the access needed to run the database system ie Stored Procs and Table access. The last stage is to change the connection string in the web.config file for your application to reflect the new server. Making sure that the attach Database parameters are removed.

Hope this helps.

More Info - HELP - Cannot connect to SQL Server 2005 Express

I've installed the SQL Server 2005 Express and also the MS SQL Server Management Studio 2005 Express
But when i load the latter and try to connect to my database I get the below error message.

TITLE: Connect to Server

Cannot connect to DEVCOMP\SQLEXPRESS.

ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
Further Details:

Further to the above error me, I got the following when I clicked on "Surface Area Configuration for Services and Connections"

TITLE: Surface Area Configuration for Services and Connections - localhost
-
Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again.
-
ADDITIONAL INFORMATION:
[C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SmoEnum\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.SmoEnum.dll] InternalsVisibleTo declarations cannot have a version, culture, public key token, or processor architecture specified. (Microsoft.SqlServer.Smo)
-

|||Check to see if the SQL Server Express service is started.

Also, you may want to change the network settings to accept TCP/IP and remote connections. Check out this blog entry:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Let me know if this doesn't fix your problem.

Thanks,
Rob|||

The remote connectivity is disabled by default. You can activate it using the SQL Server 2005 Surface Area Configuration SQLSAC.EXE.

If localhost is not known, then either your hosts. file is corrupt or blocked by a firewall (for any tests, deactivate the local firewall temporarily). You can use 127.0.0.1 instead of localhost.

The hosts file is located in your windows directory under

C:\windows\system32\drivers\etc\hosts.

Regards
Axel

|||

I have checked out the blog and performed all the steps. I have restarted the services and still no luck.

I am a newbie to webdev so started with the sample personal website. I followed the instructions to the letter but cant get past Ctrl-F5 to initialize without the error.

Where do I goto from here?

Paul

Monitoring SqlServer 2000 performance with Express

Hi,

I'm using Visual Web Developer Express and Management Studio Express, and my web site is on a shared web host′, running SqlServer2000. I'm looking for software that enables me to monitor the server, but is it possible? The only apps I've found (and downloaded and installed and unistalled) so far need administrative rights to the server so they won't work on a shared web host.

All help would be welcome!

Thanks in advance,

Pettrer

Goto Administrative Tools...Performance.

Right mouse click on the graph and select "Add Counters..."

Drop down the Performance Objects listbox.

There are many performance counters installed with SQL Server, IIS and .NET.

|||

Hello,

This little app seems quite powerful, but it's only for times when one has physical access and the correct permissions to the db server, isn't it? I can only find SQLServer Express in the list of availabale servers, that is, my own computer's (inactive) web server.

I was thinking more of a third-party purchase, if any of those works in a hosted environment.

Thanks for replying!

Pettrer, Sweden

|||

>> but it's only for times when one has physical access and the correct permissions to the db server, isn't it?

A third party tool will also need permissions. In the Add Counters dialog, there's a pull down to select from other computers. However, I admit I've never tried or done this :)

There must be a way to get it to work. From the help:

Choosing the computer to use for monitoring
When monitoring computers remotely, you have some options for how to collect data. For example, you could run performance logging on the administrator's computer, drawing data continuously from each remote computer. In another case, you could have each computer running the service to collect data and, at regular intervals, run a batch program to transfer the data to the administrator's computer for analysis and archiving.

Centralized data collection (that is, collection on a local computer from remote computers that you are monitoring) is simple to implement because only one logging service is running. You can collect data from multiple systems into a single log file. However, it causes additional network traffic and might be restricted by available memory on the administrator's computer. To do centralized data collection, use the Add Counters dialog box to select a remote computer while running System Monitor on your local computer.

Distributed data collection (that is, data collection that occurs on the remote computers you are monitoring) does not incur the memory and network traffic problems of centralized collection. However, it does result in delayed availability of the data, requiring that the collected data be transferred to the administrator's computer for review. To do distributed data collection, use Computer Management on a local computer to select a remote computer on which to collect data.

When monitoring remote computers, note that the remote computer will only allow access to user accounts that have permission to access it. In order to monitor remote systems from your computer, you must start the Performance Logs and Alerts service using an account that has permission to access the remote computers you want to monitor. By default, the service is started under the local computer's "system" account, which generally has permission only to access services and resources on the local computer. To start this under a different account, use the Run As option when configuring data collection, or use Services under Computer Management and update the properties of the Performance Logs and Alerts service.

|||

Hi again,

Thanks for your replies. I found the option you mentioned but didn't see a "remote" option or the like. In any case, I bellieve I wont be able to access the server's system folder anyway. I don't know much about this, but it seems as if the majority of these apps are diving into the system, which may be a prerequisite, at least for diagnosing the internal performance. What I'm looking for is how well the server performs, how long before it jams and so on, and it might not be possible to it this way.

Pettrer

Monitoring SQL Server with MOM

We have recently installed MOM and wish to customise the SQL Management Pack.
What are the top alerts (10-20) we should be looking to alert on?This very much depends on your requirements. We are monitoring the following
events:
1. severe SQL Server errors (severity 17 or higher)
2. a database becomes inaccessible (excluding the cases in which the
database is being restored or offline)
3. the SQL instance becomes inaccessible
4. any port binding failure
5. any backup failure
6. any SQL instance restarts
7. clustered SQL instance not running on the preferred node
8. clustered SQL instance failover
9. cluster node not up (mostly the one that doesn't have any SQL instance
running)
10. # of connections exceeding a threshold (had some rough apps not cleaning
up after itself)
11. Free space below a certain threshold
Linchi
"Court" wrote:
> We have recently installed MOM and wish to customise the SQL Management Pack.
> What are the top alerts (10-20) we should be looking to alert on?|||Many thanks for that.
"Linchi Shea" wrote:
> This very much depends on your requirements. We are monitoring the following
> events:
> 1. severe SQL Server errors (severity 17 or higher)
> 2. a database becomes inaccessible (excluding the cases in which the
> database is being restored or offline)
> 3. the SQL instance becomes inaccessible
> 4. any port binding failure
> 5. any backup failure
> 6. any SQL instance restarts
> 7. clustered SQL instance not running on the preferred node
> 8. clustered SQL instance failover
> 9. cluster node not up (mostly the one that doesn't have any SQL instance
> running)
> 10. # of connections exceeding a threshold (had some rough apps not cleaning
> up after itself)
> 11. Free space below a certain threshold
> Linchi
> "Court" wrote:
> > We have recently installed MOM and wish to customise the SQL Management Pack.
> > What are the top alerts (10-20) we should be looking to alert on?|||Hi, is it possible for you to provide example event id's?
"Linchi Shea" wrote:
> This very much depends on your requirements. We are monitoring the following
> events:
> 1. severe SQL Server errors (severity 17 or higher)
> 2. a database becomes inaccessible (excluding the cases in which the
> database is being restored or offline)
> 3. the SQL instance becomes inaccessible
> 4. any port binding failure
> 5. any backup failure
> 6. any SQL instance restarts
> 7. clustered SQL instance not running on the preferred node
> 8. clustered SQL instance failover
> 9. cluster node not up (mostly the one that doesn't have any SQL instance
> running)
> 10. # of connections exceeding a threshold (had some rough apps not cleaning
> up after itself)
> 11. Free space below a certain threshold
> Linchi
> "Court" wrote:
> > We have recently installed MOM and wish to customise the SQL Management Pack.
> > What are the top alerts (10-20) we should be looking to alert on?

Monitoring SQL Server with MOM

We have recently installed MOM and wish to customise the SQL Management Pack
.
What are the top alerts (10-20) we should be looking to alert on?This very much depends on your requirements. We are monitoring the following
events:
1. severe SQL Server errors (severity 17 or higher)
2. a database becomes inaccessible (excluding the cases in which the
database is being restored or offline)
3. the SQL instance becomes inaccessible
4. any port binding failure
5. any backup failure
6. any SQL instance restarts
7. clustered SQL instance not running on the preferred node
8. clustered SQL instance failover
9. cluster node not up (mostly the one that doesn't have any SQL instance
running)
10. # of connections exceeding a threshold (had some rough apps not cleaning
up after itself)
11. Free space below a certain threshold
Linchi
"Court" wrote:

> We have recently installed MOM and wish to customise the SQL Management Pa
ck.
> What are the top alerts (10-20) we should be looking to alert on?|||Many thanks for that.
"Linchi Shea" wrote:
[vbcol=seagreen]
> This very much depends on your requirements. We are monitoring the followi
ng
> events:
> 1. severe SQL Server errors (severity 17 or higher)
> 2. a database becomes inaccessible (excluding the cases in which the
> database is being restored or offline)
> 3. the SQL instance becomes inaccessible
> 4. any port binding failure
> 5. any backup failure
> 6. any SQL instance restarts
> 7. clustered SQL instance not running on the preferred node
> 8. clustered SQL instance failover
> 9. cluster node not up (mostly the one that doesn't have any SQL instance
> running)
> 10. # of connections exceeding a threshold (had some rough apps not cleani
ng
> up after itself)
> 11. Free space below a certain threshold
> Linchi
> "Court" wrote:
>|||Hi, is it possible for you to provide example event id's?
"Linchi Shea" wrote:
[vbcol=seagreen]
> This very much depends on your requirements. We are monitoring the followi
ng
> events:
> 1. severe SQL Server errors (severity 17 or higher)
> 2. a database becomes inaccessible (excluding the cases in which the
> database is being restored or offline)
> 3. the SQL instance becomes inaccessible
> 4. any port binding failure
> 5. any backup failure
> 6. any SQL instance restarts
> 7. clustered SQL instance not running on the preferred node
> 8. clustered SQL instance failover
> 9. cluster node not up (mostly the one that doesn't have any SQL instance
> running)
> 10. # of connections exceeding a threshold (had some rough apps not cleani
ng
> up after itself)
> 11. Free space below a certain threshold
> Linchi
> "Court" wrote:
>