datasource on remote SQL server?

Disclaimer: I'm a web developer, not a DBA or network admin, so if the following is a totally stupid question, please be gentle--

I am trying to come up with a somewhat decent testing environment (they have no staging area) for a client for whom I'm converting a static HTML site to ASP pulling content from SQL Server.

I'm running Windows 2000 Professional at home and its slightly-watered-down version of IIS webserver, which is fine for testing ASP that doesn't talk to a database. My question is, is there any way that my home webserver can talk to their production SQL Server installation?

My working setup is this--I can connect via Remote Desktop to their webserver machine, which has a copy of Enterprise Manager installed, which has a database registration for a SQL Server installation on a physically separate machine. Given those couple of degrees of separation, I have no idea how locked-down the actual SQL Server box might or might not be as far as the outside world goes.

So, several questions:

Is there a way, without access to the machine console, to figure out if the SQL Server box has an Internet IP address I can try to set up as an ODBC DSN?

Given access to Enterprise Manager for that SQL Server install, are there settings I can check and/or configure to make this happen?

And if this is the sort of thing I wouldn't be able to resolve myself, is there anything specific I can ask the client's tech staff to see if I can get what I need?

I hope this is coherent. If the answer is "dude, you need to talk to the network admin" I'll do that... it's just I'd always rather learn how to do something myself when it's possible :)Since you can get a terminal session going between yourself and them, it sounds like you can get onto their network. If you are using the webserver's name to connect to the webserver, then you are using their DNS, and you are practically all set.

begin rant()
As a rule, no IP addresses should be used in code, except for raw
testing. Any server should be addressed by it's DNS or other network
name. This way, if an IP address changes, or a machine is replaced,
the application is not affected.
end rant;

If you can not use a DNS name (I have seen some VPN connections forbid using others' DNS Servers) for the SQL server on your local machine, and you must use IP addresses, I would say ping the name of the SQL Server, and see what comes back. Then ping that address from your local machine to make sure that you can get there.

As for how to connect to the DB itself, you will have to talk to their administrator. Ideally you should get a read-only login for the particular database you want to connect to. Many small shops use the sa login for all of their apps, but that is not very secure.

Hope this helps.|||Hi, thanks for your response.

I had tried creating a new datasource using the SQL server machine name while connected via terminal session to the webserver. I get an error:

Connection failed:
SQLState '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
Connection failed:
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL Server not found.

I feel your pain on IP addresses in code, but I only want it to try to create a datasource on my home machine, since creating a datasource using the machine name doesn't seem to be working. Pinging the SQL box from the webserver console just gets me an internal network IP; a ping of that IP from my home machine (with terminal session running) times out.

In any event, using an external IP, if it exists, would seem preferable to a machine name for my purposes, because it means I don't have to keep a remote desktop session open 24/7 for the next month.

I have no problem connecting to the DB when using Enterprise Manager from the webserver console--it's set up to use Windows authentication--I believe I could ask for & receive SA access if it were necessary though.|||When you get to the second screen of the ODBC Configuration (where it asks for a user and password), click on the Client Configuration button. See if you can change the NetLib to TCP/IP. Sometimes the NetLibs can be a little funny about actually finding a server. This would be on the client's webserver, of course.|||I did try that, in spite of not knowing what the heck I was doing--figured I couldn't break what wasn't work in the first place. No love, though--thanks for the suggestion.

My client is using <a href="http://links.10026.com/?link=http://www.neoteris.com/products.html">Neoteris</a> to manage remote sessions. I don't know much about the technical details of how it works (and because their support pages require a customer id and password, it seems they don't WANT me to know) but I found a diagnostic button to click and I can see where it is mapping a number of machine names on their network to IP addresses in my home network's internal space. I see the webserver machine name there; I don't see the SQL box machine name.

I broke down and emailed my contact to ask if they can set up remote access authorization to the SQL box too--I still don't like the notion of having to keep the Neoteris session (based in a java applet in a web browser) running 24/7. It's gonna be fun the first time my DSL hiccups and the client calls me all "I can't connect to the test site" and I'm on the other side of town--but it would be a solution.

Datasource error

Hi, i've a problem with calling a report created with reporting service; i get this error :System.Web.Services.Protocols.SoapException : Impossible find datasource...... ( i translate from italian ) I'm sure ip i wrote is right , and , moreover, any ip datasource i put ,i get the same error. Thank you in advance for your help.

Hi,

From your description, it seems that you are using WebService as your datasource of your report, right?

And the error "System.Web.Services.Protocols.SoapException" indicates that the WebService failed to return the result which your report expected.

So I suggest that you should try to check your WebService to see if it can work correctly, we suppose that it returns a dataset typed datasource, then you can just write a simple project, remove the reporting service part, try to see if you can retrieve the return properly.

Thanks.

|||Hi Nai-Dong,
I use the webservice as result, distribuiting report I've developed with SQL Server Development intelligence studio.
I'm doing this for the first time , I'm studying how to use SQLServer reporting service.
The webservice "seems" to me that works properly because I see exposed all the methods, and moreover ,
calling the other methods that don't involve datasource , the report is correctly called.
I'm using XP .
Thank you.|||

Hi,

The webservice "seems" to me that works properly because I see exposed all the methods, and moreover , calling the other methods that don't involve datasource , the report is correctly called.

Well, if so. I think you should try to check if the database is available from the machine which host the web service. Another problem is the account. You should try to check if the account which runs the webservice exist in the web service identity of the report server. Also, try to check if the account which runs the webservice (ASPNET or IUSR_MachineName) has to permission to access the reporting service database. If not, add the account into the users of that database.

Besides, I suggest you to refer the following link, it may be helpful to you.

http://msdn2.microsoft.com/en-us/library/aa179578(SQL.80).aspx

Thanks.

Datadriven Subscription - Web Archive Improper formatting

Hi All,

I have a data driven subscription with Web Archive rendering format.

(SQL Reporting Service 2000). When a user gets this email, the format is not alligned properly, even, lines are missing in the report.

What could be the problem, is this a known issue? if yes, any patches available?

Please help me.

Thanks,

Nagesh

There are no replies to this query since one week...

Is this a silly query or tough one...

Databases Starting Repeatedly

I have four databases that show up in the database log as
starting over and over again. While this activity is
going on the web based application is unable to connect.
I have checked all available log files for errors but have
not received the first one as of yet.
I need to call on the experts in the field. Have any of
you ever seen this before? If so, what did you do to
correct the problem?
This is SQL Server 2000 Sp3 running on Windows 2000 Sp3
with 2GB of ram and two 2.4 GHZ processors.
I would appreciate anyone's help as this is a show stopper
for me. Thanks in advance.
A portion of log follows.
**********************************************
2003-09-29 09:53:38.42 server Microsoft SQL Server
2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195:
Service Pack 3)
2003-09-29 09:53:38.42 server Copyright (C) 1988-2002
Microsoft Corporation.
2003-09-29 09:53:38.42 server All rights reserved.
2003-09-29 09:53:38.42 server Server Process ID is 996.
2003-09-29 09:53:38.42 server Logging SQL Server
messages in file 'e:\Program Files\Microsoft SQL
Server\MSSQL\log\ERRORLOG'.
2003-09-29 09:53:38.42 server SQL Server is starting at
priority class 'normal'(4 CPUs detected).
2003-09-29 09:53:38.67 server SQL Server configured for
thread mode processing.
2003-09-29 09:53:38.67 server Using dynamic lock
allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2003-09-29 09:53:38.79 server Attempting to initialize
Distributed Transaction Coordinator.
2003-09-29 09:53:40.29 spid3 Starting up
database 'master'.
2003-09-29 09:53:40.74 spid3 Server name is 'HSMDB'.
2003-09-29 09:53:40.74 spid3 Skipping startup of clean
database id 5
2003-09-29 09:53:40.78 spid3 Skipping startup of clean
database id 6
2003-09-29 09:53:40.78 spid3 Skipping startup of clean
database id 7
2003-09-29 09:53:40.78 spid5 Starting up
database 'msdb'.
2003-09-29 09:53:40.78 spid6 Starting up
database 'hsmqa'.
2003-09-29 09:53:40.78 spid7 Starting up
database 'WebTrend'.
2003-09-29 09:53:40.78 server Using 'SSNETLIB.DLL'
version '8.0.760'.
2003-09-29 09:53:40.78 spid8 Starting up
database 'model'.
2003-09-29 09:53:40.78 spid9 Starting up
database 'WebData'.
2003-09-29 09:53:40.78 spid12 Starting up
database 'WebCache'.
2003-09-29 09:53:40.81 spid7 Analysis of
database 'WebTrend' (10) is 100% complete (approximately 0
more seconds)
2003-09-29 09:53:40.85 spid12 Analysis of
database 'WebCache' (12) is 100% complete (approximately 0
more seconds)
2003-09-29 09:53:40.92 server SQL server listening on
TCP, Shared Memory, Named Pipes.
2003-09-29 09:53:40.92 server SQL Server is ready for
client connections
2003-09-29 09:53:40.92 spid8 Clearing tempdb database.
2003-09-29 09:53:41.10 spid8 Starting up
database 'tempdb'.
2003-09-29 09:53:41.21 spid3 Recovery complete.
2003-09-29 09:53:41.21 spid3 SQL global counter
collection task is created.
2003-09-29 09:53:42.45 spid3 Launched startup
procedure 'hbi_unlock_objects'
2003-09-29 09:53:43.40 spid52 Using 'xpsqlbot.dll'
version '2000.80.194' to execute extended stored
procedure 'xp_qv'.
2003-09-29 09:54:26.79 spid53 Using 'xpstar.dll'
version '2000.80.760' to execute extended stored
procedure 'sp_MSgetversion'.
2003-09-29 09:54:30.64 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:54:30.75 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:54:30.84 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:44.43 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:44.53 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:44.62 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:45.98 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.07 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.17 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.26 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.36 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.45 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.57 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.67 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.76 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.86 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.95 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:47.04 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:47.14 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.23 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.32 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.42 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.53 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.62 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.76 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:47.86 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:47.95 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.21 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.31 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.40 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.93 spid53 Starting up
database 'hsmprod'.
2003-09-29 10:00:01.78 spid86 Starting up
database 'hsmtest'.
2003-09-29 10:00:01.87 spid86 Starting up
database 'hsmtrain'.
2003-09-29 10:00:01.98 spid86 Starting up
database 'hsmtest'.
2003-09-29 10:00:02.07 spid86 Starting up
database 'hsmtrain'.
2003-09-29 10:00:02.40 spid87 Starting up
database 'hsmtest'.
2003-09-29 10:00:02.51 spid87 Starting up
database 'hsmtrain'.Hi Thomas,
Such issues tend to be complex and require extensive research. I'd like to
set the right expectations and let you know that it may take a while for us
to help you narrow down the problem.
Please check to see if the "Auto close" is checked in the Option tab in the
property dialog of the database. If so, make sure this option is unchecked
and try to connect to SQL Server again. Does this resolve the problem?
If not, I would like you to provide me with more information regarding this
issue so that I can narrow down it.
Please make sure that the MSSQLServer service, for the SQL Server you are
trying to connect to, is started and running before doing any further
troubleshooting.
1. What do you mean by "the web based application is unable to connect"?
How do you connect SQL Server? Please describe it in detail. Do any error
messages occur?
2. Ping the server using the server name and IP address. See if the request
completes successfully or times out.
3. Can you connect to SQL Server using Query Analyzer? If not, does any
error message occur?
4. What is the MDAC version on the client machine? For more information
regarding how to check the MDAC version, please refer to the following
article:
301202 HOW TO: Check for MDAC Version
http://support.microsoft.com/?id=301202
5. Please provide the complete error log, you can send it at
v-yshao@.microsoft.com
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Most probably the autoclose database option turned on.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Thomas Rhodes" <thomas.rhodes@.nghs.com> wrote in message
news:1c7601c386a0$fb9485d0$a001280a@.phx.gbl...
> I have four databases that show up in the database log as
> starting over and over again. While this activity is
> going on the web based application is unable to connect.
> I have checked all available log files for errors but have
> not received the first one as of yet.
> I need to call on the experts in the field. Have any of
> you ever seen this before? If so, what did you do to
> correct the problem?
> This is SQL Server 2000 Sp3 running on Windows 2000 Sp3
> with 2GB of ram and two 2.4 GHZ processors.
> I would appreciate anyone's help as this is a show stopper
> for me. Thanks in advance.
> A portion of log follows.
> **********************************************
> 2003-09-29 09:53:38.42 server Microsoft SQL Server
> 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195:
> Service Pack 3)
> 2003-09-29 09:53:38.42 server Copyright (C) 1988-2002
> Microsoft Corporation.
> 2003-09-29 09:53:38.42 server All rights reserved.
> 2003-09-29 09:53:38.42 server Server Process ID is 996.
> 2003-09-29 09:53:38.42 server Logging SQL Server
> messages in file 'e:\Program Files\Microsoft SQL
> Server\MSSQL\log\ERRORLOG'.
> 2003-09-29 09:53:38.42 server SQL Server is starting at
> priority class 'normal'(4 CPUs detected).
> 2003-09-29 09:53:38.67 server SQL Server configured for
> thread mode processing.
> 2003-09-29 09:53:38.67 server Using dynamic lock
> allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
> 2003-09-29 09:53:38.79 server Attempting to initialize
> Distributed Transaction Coordinator.
> 2003-09-29 09:53:40.29 spid3 Starting up
> database 'master'.
> 2003-09-29 09:53:40.74 spid3 Server name is 'HSMDB'.
> 2003-09-29 09:53:40.74 spid3 Skipping startup of clean
> database id 5
> 2003-09-29 09:53:40.78 spid3 Skipping startup of clean
> database id 6
> 2003-09-29 09:53:40.78 spid3 Skipping startup of clean
> database id 7
> 2003-09-29 09:53:40.78 spid5 Starting up
> database 'msdb'.
> 2003-09-29 09:53:40.78 spid6 Starting up
> database 'hsmqa'.
> 2003-09-29 09:53:40.78 spid7 Starting up
> database 'WebTrend'.
> 2003-09-29 09:53:40.78 server Using 'SSNETLIB.DLL'
> version '8.0.760'.
> 2003-09-29 09:53:40.78 spid8 Starting up
> database 'model'.
> 2003-09-29 09:53:40.78 spid9 Starting up
> database 'WebData'.
> 2003-09-29 09:53:40.78 spid12 Starting up
> database 'WebCache'.
> 2003-09-29 09:53:40.81 spid7 Analysis of
> database 'WebTrend' (10) is 100% complete (approximately 0
> more seconds)
> 2003-09-29 09:53:40.85 spid12 Analysis of
> database 'WebCache' (12) is 100% complete (approximately 0
> more seconds)
> 2003-09-29 09:53:40.92 server SQL server listening on
> TCP, Shared Memory, Named Pipes.
> 2003-09-29 09:53:40.92 server SQL Server is ready for
> client connections
> 2003-09-29 09:53:40.92 spid8 Clearing tempdb database.
> 2003-09-29 09:53:41.10 spid8 Starting up
> database 'tempdb'.
> 2003-09-29 09:53:41.21 spid3 Recovery complete.
> 2003-09-29 09:53:41.21 spid3 SQL global counter
> collection task is created.
> 2003-09-29 09:53:42.45 spid3 Launched startup
> procedure 'hbi_unlock_objects'
> 2003-09-29 09:53:43.40 spid52 Using 'xpsqlbot.dll'
> version '2000.80.194' to execute extended stored
> procedure 'xp_qv'.
> 2003-09-29 09:54:26.79 spid53 Using 'xpstar.dll'
> version '2000.80.760' to execute extended stored
> procedure 'sp_MSgetversion'.
> 2003-09-29 09:54:30.64 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:54:30.75 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:54:30.84 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:44.43 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:44.53 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:44.62 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:45.98 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.07 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.17 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.26 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.36 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.45 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.57 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.67 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.76 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.86 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.95 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:47.04 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:47.14 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.23 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.32 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.42 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.53 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.62 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.76 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:47.86 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:47.95 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.21 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.31 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.40 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.93 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 10:00:01.78 spid86 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:01.87 spid86 Starting up
> database 'hsmtrain'.
> 2003-09-29 10:00:01.98 spid86 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:02.07 spid86 Starting up
> database 'hsmtrain'.
> 2003-09-29 10:00:02.40 spid87 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:02.51 spid87 Starting up
> database 'hsmtrain'.|||Michael:
First, Thank you for your reply. I appreciate the
professional level and degree of your response.
Second, yes the Auto Close was set. I have since removed
the check mark. I have several other databases running on
this server in support of the web based application I
mentioned. These other databases started normally and
remained so after I had restarted the server. If I had
given more thought to situation I could have compared the
properties between the problem databases and those that
functioned as expected. The problem ceased yesterday
morning around 10:00am even with the auto close still
set. I suspect the web application connection succeeded
and maintain its connection thereafter.
Just quickly, should others have interest, I'll respond to
your questions.
1. The web application could not connect from the web
application server. This is a separate server from the
database. I am not exactly sure how the vendor connects
to the database in the code behind the pages. No errors
reported back to Internet Explorer except that the page
could not be found. Basically the weblogic web server
could not start without the connection to the database.
2. I did ping all the servers associated with this
application. I receive successful results on all counts.
3. Yes I was able to connect to the database server using
Query Analyzer and was able to query tables and data from
the affected databases.
4. I have the latest MDAC installation that comes with
SQL SP3a.
Again, I thank you for your help.
Thomas
>--Original Message--
>Hi Thomas,
>Such issues tend to be complex and require extensive
research. I'd like to
>set the right expectations and let you know that it may
take a while for us
>to help you narrow down the problem.
>Please check to see if the "Auto close" is checked in the
Option tab in the
>property dialog of the database. If so, make sure this
option is unchecked
>and try to connect to SQL Server again. Does this resolve
the problem?
>If not, I would like you to provide me with more
information regarding this
>issue so that I can narrow down it.
>Please make sure that the MSSQLServer service, for the
SQL Server you are
>trying to connect to, is started and running before doing
any further
>troubleshooting.
>1. What do you mean by "the web based application is
unable to connect"?
>How do you connect SQL Server? Please describe it in
detail. Do any error
>messages occur?
>2. Ping the server using the server name and IP address.
See if the request
>completes successfully or times out.
>3. Can you connect to SQL Server using Query Analyzer? If
not, does any
>error message occur?
>4. What is the MDAC version on the client machine? For
more information
>regarding how to check the MDAC version, please refer to
the following
>article:
>301202 HOW TO: Check for MDAC Version
>http://support.microsoft.com/?id=301202
>5. Please provide the complete error log, you can send it
at
>v-yshao@.microsoft.com
>I am standing by for your response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Hi Thomas,
Thank you for taking time to write in your experience so that it may help
other customers who may encounter similar issues. As per your previous
post, I am going consider this issue as Resolved. However, should you have
any further questions or concerns, please feel free to send your post here.
I will continue to work with you.
Thanks for using MSDN newsgroup again.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Databases per Server

All,

I have 2 web applications running on 2 W2K servers. Each server has a local SQL server(2000). We are about to upgrade the servers to Windows 2003. The web applications are running classic ASP and VB COM+ objects. We are going to take the opportunity to seperate SQL from the web server. What will end up with is 2 web servers running the applications and a single database server with 2 databases servering up the data. We will upgrade the SQL2K to SQL2K5.

My question is how will the SQL performance be affected? What's the best place to locate the COM+ objects? What kind of pitfalls do I need to be aware of?

Thanks

BB

Make sure that the consolidated box has suitable hardware to support both apps (memory, disks, processors). Also, remember that now the a failure of the SQL Server will bring down both while if one database starts hogging resources, the other would be adversely affected. Similarly you'll only have one set of system databases so make sure that tempdb is on a fast set of disks.

Certainly one area you may need to check any conflicts arising from any SQL Server configurations that may differ on the current boxes and also any login conflicts. Remember, also that future patches will be applied at the server level and both apps will need to be able to run on the same SQL version.

You are able to run multiple instances on one box which would allow you to allocate resources better to each application or could consider virtual servers which is becoming more and more popular these days.

HTH!

|||

Great advice - thanks!

What about the increase in network traffic. Is there something to do hardware wise? Like adding a second NIC card? Will that even make a difference?

Thanks

database?

I am trying to built a web database using the visual studio web developer 2005 express. client will be able to access the database in the intranet server. I found out that i can built a database using the web developer itself so what is the different from built database using the sql server? so where should i built my database in?I would suggest that you put the SQL Server database on your client's SQL Server. I have no experience with Visual Studio Web Developer Express but, if it's like Pro, there's a "App_Data" folder that allows you to create SQL Server databases (.mdf). You must have SQL Server 2005 express to do this but it's highly unlikely that your client will use SQL Server 2005 Express as their Production copy.

database uptime indicator

Hi there,
I am interested to explore the MSSQL databases uptime monitoring and would
like to publish the indicators in web UI, does anyone has done this before
or having any idea how to do it?
thanks.> I am interested to explore the MSSQL databases uptime monitoring and would
> like to publish the indicators in web UI, does anyone has done this before
> or having any idea how to do it?
SELECT login_time FROM master.dbo.sysprocesses WHERE spid=1
RADl0PASlV

database uptime indicator

Hi there,
I am interested to explore the MSSQL databases uptime monitoring and would
like to publish the indicators in web UI, does anyone has done this before
or having any idea how to do it?
thanks.
> I am interested to explore the MSSQL databases uptime monitoring and would
> like to publish the indicators in web UI, does anyone has done this before
> or having any idea how to do it?
SELECT login_time FROM master.dbo.sysprocesses WHERE spid=1
RADl0PASlV

database uptime indicator

Hi there,
I am interested to explore the MSSQL databases uptime monitoring and would
like to publish the indicators in web UI, does anyone has done this before
or having any idea how to do it?
thanks.> I am interested to explore the MSSQL databases uptime monitoring and would
> like to publish the indicators in web UI, does anyone has done this before
> or having any idea how to do it?
SELECT login_time FROM master.dbo.sysprocesses WHERE spid=1
RADl0PASlV

database updates not reflected in report

Iam using visual web developer express with sql server 2005 with advanced services. Iam designing my database reports with sql server reporting services(SQL server business interlligence development studio) and they are running correctly. Problem is, when i add records to my database and then go back and run my reports, the new records effects are not reflected in my new report runs.May be i should say my reports do not update. they only retrieve the data that was already in the database table during the time of report design. I dont know a way of forcing updates to be reflected in the reports.

Help Appreciated.

The updates aren't instant. Try closing the report and reopening after you make an update.

Database Trouble

Hey if anybody can help me with this i will appreciate this....I Have Installed the MSDE database for the web Matrix however when i tried to establish connection in the web matrix it's tellling me that my connection is not available and I am unable to establish connection..If it helps I am on chapter 8 In the book of Begining Dynamic Websites w/Asp.net written by david sussman...Thank in advance.Did you follow the instructions onInstalling MSDE for ASP.NET Web Matrix?

How are you trying to establish a connection. through the Web Matrix Data window? Or through code?

Are you trying to connect via Windows authentication, or SQL Server authentication?

What is the exact error message?

Terri|||sorry for a late reply I could never find these posts after i put them up. Well I tried using the disk that comes along witht the book. I then uninstalled the software becuase of a possible computer problem. I then tried to re-install it. the disk runs the software and then displays a error invlaid message. Also due to this will my program tak e effect. Meaning will some of them not appear due to the .NETframework error?

Database Transfer Services

Does SQL Express support Database Transfer services? If not does it have something like that. I am trying to transfer data from my database to a web server but cannot seem to find the 2005 equivalent to SQL 2000 Database Transfer Services

Express does byde fault support Data Transformation Services or its replacement Integration Services. Some people ahve been able to get it to work by downloading the Advanced Express pack. However thats not really going to help you with the webs erver problem as much.

You can just detach the database and copy it, you could also search for the database publishing wizard on the Microsoft site and also on http://www.codeplex.com

|||

I just got some more information. apparently a database dump gets done at night on to an SQL 2000 server. What sort of conversion needs to be done to make the database compatible with 2000?

So SQL Express is dumping a database on to a 2000SQL server.

|||There is not an easy way of taking a SQL2005 database and loading it onto a SQL2000 Server with express. SSIS is your best shot, there are some threads from a while back on import/export with express.