Fastest method to move data between two remote SQL servers?

Need to move approximately 2-5 GB of data between two servers located
in different states but on same WAN. What is the fastest method with
least intrusion on daily updates to file? (SQL Server 2000)
1. "Copy Server Object" task in DTS (any special options to enable?)
2. Use ODBC/Transformation within DTS.
3. Replication?
4. DROP / Select into via linked server?
5. Other options?P.S. I'm testing this out, but it appears step 1 and 2 are taking 6-8
hours each, so any advance help would be appreciated...|||2 options for a one-off
1 - backup and copy/restore on target server
2 - detach/copy/attach again on both servers.
But if you want to do this regularly then maybe 1 with jobs running scripts.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>|||I'm assuming the SQL Servers are reasonably fast, the bottle neck is the WAN
pipe, and the only scheduler available is SQL Agent.
Create a linked server from the source to the destination
On the source server have a job that does the following steps
Write a DTS package to export the data to a file
xp_cmdshell or operating system command within a job step: use gzip or
some other compression software to do reasonable compression. Gzip has
different compression levels.
xp_cmdshell or operating system command within a job step: copy the file
to the destination server (FTP is a little faster but less managable)
Use the linked server to start a job on the destination server or just
call a stored procedure
On the destination server, the job or stored procedure should
xp_cmdshell or operating system command within a job step: decompress the
file
bcp, bulk insert, or DTS (bulk loader) into SQL Server.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>|||Interesting! It'll take a bit to test and coordinate schedule times,
but I'll give it a shot. I believe you are correct on all your
assumptions too, althought I also suspect a little Disk IO competition.
Thanks!
FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
Option 1 did run during the day, when Option 2 had nighttime/early
morning.|||This is the time I usually get on my soap box about an enterprise batch
scheduler and preach to management.
If you have your source server start a job on the destination server after
the copy then you shouldn't have to worry about schedule times between the
servers.
Good luck.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129132709.159007.124640@.g49g2000cwa.googlegroups.com...
> Interesting! It'll take a bit to test and coordinate schedule times,
> but I'll give it a shot. I believe you are correct on all your
> assumptions too, althought I also suspect a little Disk IO competition.
>
> Thanks!
>
> FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
> Option 1 did run during the day, when Option 2 had nighttime/early
> morning.
>

Fastest method to move data between two remote SQL servers?

Need to move approximately 2-5 GB of data between two servers located
in different states but on same WAN. What is the fastest method with
least intrusion on daily updates to file? (SQL Server 2000)
1. "Copy Server Object" task in DTS (any special options to enable?)
2. Use ODBC/Transformation within DTS.
3. Replication?
4. DROP / Select into via linked server?
5. Other options?
P.S. I'm testing this out, but it appears step 1 and 2 are taking 6-8
hours each, so any advance help would be appreciated...
|||2 options for a one-off
1 - backup and copy/restore on target server
2 - detach/copy/attach again on both servers.
But if you want to do this regularly then maybe 1 with jobs running scripts.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegr oups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>
|||I'm assuming the SQL Servers are reasonably fast, the bottle neck is the WAN
pipe, and the only scheduler available is SQL Agent.
Create a linked server from the source to the destination
On the source server have a job that does the following steps
Write a DTS package to export the data to a file
xp_cmdshell or operating system command within a job step: use gzip or
some other compression software to do reasonable compression. Gzip has
different compression levels.
xp_cmdshell or operating system command within a job step: copy the file
to the destination server (FTP is a little faster but less managable)
Use the linked server to start a job on the destination server or just
call a stored procedure
On the destination server, the job or stored procedure should
xp_cmdshell or operating system command within a job step: decompress the
file
bcp, bulk insert, or DTS (bulk loader) into SQL Server.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegr oups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>
|||Interesting! It'll take a bit to test and coordinate schedule times,
but I'll give it a shot. I believe you are correct on all your
assumptions too, althought I also suspect a little Disk IO competition.
Thanks!
FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
Option 1 did run during the day, when Option 2 had nighttime/early
morning.
|||This is the time I usually get on my soap box about an enterprise batch
scheduler and preach to management.
If you have your source server start a job on the destination server after
the copy then you shouldn't have to worry about schedule times between the
servers.
Good luck.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129132709.159007.124640@.g49g2000cwa.googlegr oups.com...
> Interesting! It'll take a bit to test and coordinate schedule times,
> but I'll give it a shot. I believe you are correct on all your
> assumptions too, althought I also suspect a little Disk IO competition.
>
> Thanks!
>
> FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
> Option 1 did run during the day, when Option 2 had nighttime/early
> morning.
>

Fastest method to move data between two remote SQL servers?

Need to move approximately 2-5 GB of data between two servers located
in different states but on same WAN. What is the fastest method with
least intrusion on daily updates to file? (SQL Server 2000)
1. "Copy Server Object" task in DTS (any special options to enable?)
2. Use ODBC/Transformation within DTS.
3. Replication?
4. DROP / Select into via linked server?
5. Other options?P.S. I'm testing this out, but it appears step 1 and 2 are taking 6-8
hours each, so any advance help would be appreciated...|||2 options for a one-off
1 - backup and copy/restore on target server
2 - detach/copy/attach again on both servers.
But if you want to do this regularly then maybe 1 with jobs running scripts.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>|||I'm assuming the SQL Servers are reasonably fast, the bottle neck is the WAN
pipe, and the only scheduler available is SQL Agent.
Create a linked server from the source to the destination
On the source server have a job that does the following steps
Write a DTS package to export the data to a file
xp_cmdshell or operating system command within a job step: use gzip or
some other compression software to do reasonable compression. Gzip has
different compression levels.
xp_cmdshell or operating system command within a job step: copy the file
to the destination server (FTP is a little faster but less managable)
Use the linked server to start a job on the destination server or just
call a stored procedure
On the destination server, the job or stored procedure should
xp_cmdshell or operating system command within a job step: decompress the
file
bcp, bulk insert, or DTS (bulk loader) into SQL Server.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129059378.818147.103050@.f14g2000cwb.googlegroups.com...
> Need to move approximately 2-5 GB of data between two servers located
> in different states but on same WAN. What is the fastest method with
> least intrusion on daily updates to file? (SQL Server 2000)
>
> 1. "Copy Server Object" task in DTS (any special options to enable?)
> 2. Use ODBC/Transformation within DTS.
> 3. Replication?
> 4. DROP / Select into via linked server?
> 5. Other options?
>|||Interesting! It'll take a bit to test and coordinate schedule times,
but I'll give it a shot. I believe you are correct on all your
assumptions too, althought I also suspect a little Disk IO competition.
Thanks!
FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
Option 1 did run during the day, when Option 2 had nighttime/early
morning.|||This is the time I usually get on my soap box about an enterprise batch
scheduler and preach to management.
If you have your source server start a job on the destination server after
the copy then you shouldn't have to worry about schedule times between the
servers.
Good luck.
"Mnemonic" <sunlinmj@.hotmail.com> wrote in message
news:1129132709.159007.124640@.g49g2000cwa.googlegroups.com...
> Interesting! It'll take a bit to test and coordinate schedule times,
> but I'll give it a shot. I believe you are correct on all your
> assumptions too, althought I also suspect a little Disk IO competition.
>
> Thanks!
>
> FYI. Yesterday Option 1 took 15 hours, and option 2 took 8 hours.
> Option 1 did run during the day, when Option 2 had nighttime/early
> morning.
>

Fast Uploading Data from Client to SQL2000 Database

Does anyone know how to upload (bulk) data from a client (written in Excel VBA) to a remote SQL2000 database? Of coarse I tried "INSERT INTO" and rst.addnew but I noticed this is much, much slower as downloading from the same remote database.
Thanks.It tends to be faster if you can do this the other way round i.e. get SQL Server to load it from Excel rather than using Excel to load it into SQL Server.|||You can try to use BCP function or SQL DTS to import into the relevant table. This will automate your process and make it much faster. Sometimes, it depend on the indexing,size of a table or the design of the database for performance.|||BCP uses the filesystem which I cannot access from the client v.v.

FAQ: Are there any whitepapers about building a Disaster Recovery site at a remote location for

Hi,

Sorry for the wide distribution.

I'm trying to find any useful whitepapers about how to effectively build and operate a disaster recovery site at a remote location for SQL Server 2000. Does anyone know where to find such information?

I also know that one good option for my customer is using the Mirroring feature of SQL Server 2005. What are the other options? Is Replication an effective one for a mission-critical database (online banking)?

Thanks in advance

here are some articles and white papers that may be helpful to you:

SQL Server 2005 Failover Clustering White paper
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

Database Mirroring in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

SQL Server 2000 High Availability Series
https://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/harag01.mspx

SQL Server 2000 Backup and Restore
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

|||

I'm not trying to endorse products and I'm not claiming to know the most about this topic. There are many people that have a lot more insight into the specifics but these are intended to give you some quick and dirty information.

I haven't been able to find anything that specifically addresses the DR for SQL Server 2000 that is all encompassing. In general there are somethings that you could consider that vary in costs as your recovery techniques become more sophisticated.

Some options that are available within SQL 2000 are:

Log shipping: I believe that you send log backups and restore them at the far end without recovery. When a disaster occurs you restore the last log backup with the WITH RECOVERY option and your off and running on your backup server.

Replication: The freshness of the data at the far end will depend on the replication model chosen. Real time can be very demanding if you're planning on replicating every table in every database on your SQL Server. Generally speaking there is less overhead the more specific you could be. There are some functionality constraints that you will have to investigate to see how well they will work for you. For example if you add a column to a table that is being replicated you will have to manually add that column to the subscription for the subscribers to receive it. This is a very simple example.

Some options that are available within SQL 2005

Log shipping: Works similarly to SQL 2000.

Replication: Much improved over SQL 2000. Subscriptions automatically add columns added to tables now. Very slick.

Database Mirroring: Just had this functionality enabled with SP1. There is a lot to consider with this model. There is a primary database server, a secondary or backup database server, and a witness database server. The witnesses function is to control when the failover happens amongst other things. One of the downfalls are incorrect failovers.There are instances of the witness server incorrectly identifying a failed primary server and forcing traffic to the backup. Also I'm not sure how smooth the failback works.

Warm database server just needing to have SQL databases restored. This is a very low cost and not very real time option. This is SQL version independent.

There are several different third party High Availability/ Disaster Recovery products that are out there. Depending on how sophisticated a failover you'll need to have.

Sonasoft - Sonasafe for SQL Server - www.sonasafe.com

Neverfail - Neverfail for SQL Server - www.neverfailgroup.com

This is a very broad topic to try to address I'm sorry if this information hasn't been helpful. If you'd like to compare notes, I'd be glad to discuss some of my findings. I'm going through a very similar experience right now. Except we are a SQL 2005 only shop.

The other thing that you didn't mention is whether or not you're considering clustering. This adds a whole additional level of complexity to the considerations.

Drew Flint

FAQ: Are there any whitepapers about building a Disaster Recovery site at a remote location

Hi,

Sorry for the wide distribution.

I'm trying to find any useful whitepapers about how to effectively build and operate a disaster recovery site at a remote location for SQL Server 2000. Does anyone know where to find such information?

I also know that one good option for my customer is using the Mirroring feature of SQL Server 2005. What are the other options? Is Replication an effective one for a mission-critical database (online banking)?

Thanks in advance

here are some articles and white papers that may be helpful to you:

SQL Server 2005 Failover Clustering White paper
http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en

Database Mirroring in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

SQL Server 2000 High Availability Series
https://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/harag01.mspx

SQL Server 2000 Backup and Restore
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

|||

I'm not trying to endorse products and I'm not claiming to know the most about this topic. There are many people that have a lot more insight into the specifics but these are intended to give you some quick and dirty information.

I haven't been able to find anything that specifically addresses the DR for SQL Server 2000 that is all encompassing. In general there are somethings that you could consider that vary in costs as your recovery techniques become more sophisticated.

Some options that are available within SQL 2000 are:

Log shipping: I believe that you send log backups and restore them at the far end without recovery. When a disaster occurs you restore the last log backup with the WITH RECOVERY option and your off and running on your backup server.

Replication: The freshness of the data at the far end will depend on the replication model chosen. Real time can be very demanding if you're planning on replicating every table in every database on your SQL Server. Generally speaking there is less overhead the more specific you could be. There are some functionality constraints that you will have to investigate to see how well they will work for you. For example if you add a column to a table that is being replicated you will have to manually add that column to the subscription for the subscribers to receive it. This is a very simple example.

Some options that are available within SQL 2005

Log shipping: Works similarly to SQL 2000.

Replication: Much improved over SQL 2000. Subscriptions automatically add columns added to tables now. Very slick.

Database Mirroring: Just had this functionality enabled with SP1. There is a lot to consider with this model. There is a primary database server, a secondary or backup database server, and a witness database server. The witnesses function is to control when the failover happens amongst other things. One of the downfalls are incorrect failovers.There are instances of the witness server incorrectly identifying a failed primary server and forcing traffic to the backup. Also I'm not sure how smooth the failback works.

Warm database server just needing to have SQL databases restored. This is a very low cost and not very real time option. This is SQL version independent.

There are several different third party High Availability/ Disaster Recovery products that are out there. Depending on how sophisticated a failover you'll need to have.

Sonasoft - Sonasafe for SQL Server - www.sonasafe.com

Neverfail - Neverfail for SQL Server - www.neverfailgroup.com

This is a very broad topic to try to address I'm sorry if this information hasn't been helpful. If you'd like to compare notes, I'd be glad to discuss some of my findings. I'm going through a very similar experience right now. Except we are a SQL 2005 only shop.

The other thing that you didn't mention is whether or not you're considering clustering. This adds a whole additional level of complexity to the considerations.

Drew Flint

Failure...SQL Server does not allow remote connections

Hi all,
We've got a product which is three tier,
using a .NET Client -> .NET Server which in turn connects to a Sql
Server 2005 Instance.
On top of this we also have a Windows Service which runs nightly
processes also written in .NET
and connects directly to the SQL Server instance.
It's all working pretty well, except that in some installations the
Nightly Processes service
is failing to connect to the SQL Server instance.
We're getting the following message:
__________________________________________________ _______________
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
........
__________________________________________________ _______________
This has us confused. The nightly processes is using an indentical
connection string
to the .NET Server. The .NET application is connecting fine
everytime.
The service and the application server are sitting on the same machine
as the SQL Server instance,
so no remote connection should be needed.
The problem seems to happen only on Windows Server Machines/SQL Server
installations vs WinXP/SQLExpress, however that could be unrelated.
Any thoughts appreciated.
Cheers
Chris.
Do ou use TCP port ?
If yes do you use autopmatic selection of port number ?
If yes it is preferable to fixed a tcp port to connect to SQL server, using
autoamtic selction of port might affect such connection trouble
Hope it helps
serge
"theinvisibleGhost" wrote:

> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> __________________________________________________ _______________
> 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
> ........
> __________________________________________________ _______________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>
|||Not as far as I'm aware. It's certainly not referenced in the
connection string?
|||See my whitepaper on connecting... it might turn up some issues that you had
not considered.
http://betav.com/blog/billva/2006/06/getting_and_staying_connected_1.html
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"theinvisibleGhost" <theinvisibleGhost@.yahoo.com> wrote in message
news:1178703769.427072.296430@.q75g2000hsh.googlegr oups.com...
> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> __________________________________________________ _______________
> 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
> .......
> __________________________________________________ _______________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>
|||Cheers Bill,
I've taken a good look through your page.
I think the most likely scenario might be that the service
is running the local-system account and this may not
have access for some reason, the SQL services are running
under a different windows logon.
The SQL Server instance is running in mixed mode security.
I know where to find the permissions, how can I find out if "Local
System"
has access?
Cheers
Chris
|||OK i've checked and LocalSystem already has Connect SQL access,
so I don't think it's that...
Hmmmm...

Failure...SQL Server does not allow remote connections

Hi all,
We've got a product which is three tier,
using a .NET Client -> .NET Server which in turn connects to a Sql
Server 2005 Instance.
On top of this we also have a Windows Service which runs nightly
processes also written in .NET
and connects directly to the SQL Server instance.
It's all working pretty well, except that in some installations the
Nightly Processes service
is failing to connect to the SQL Server instance.
We're getting the following message:
________________________________________
_________________________
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
.......
________________________________________
_________________________
This has us confused. The nightly processes is using an indentical
connection string
to the .NET Server. The .NET application is connecting fine
everytime.
The service and the application server are sitting on the same machine
as the SQL Server instance,
so no remote connection should be needed.
The problem seems to happen only on Windows Server Machines/SQL Server
installations vs WinXP/SQLExpress, however that could be unrelated.
Any thoughts appreciated.
Cheers
Chris.Do ou use TCP port ?
If yes do you use autopmatic selection of port number ?
If yes it is preferable to fixed a tcp port to connect to SQL server, using
autoamtic selction of port might affect such connection trouble
Hope it helps
serge
"theinvisibleGhost" wrote:

> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> ________________________________________
_________________________
> 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
> ........
> ________________________________________
_________________________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>|||Not as far as I'm aware. It's certainly not referenced in the
connection string?|||See my whitepaper on connecting... it might turn up some issues that you had
not considered.
http://betav.com/blog/billva/2006/0...onnected_1.html
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"theinvisibleGhost" <theinvisibleGhost@.yahoo.com> wrote in message
news:1178703769.427072.296430@.q75g2000hsh.googlegroups.com...
> Hi all,
> We've got a product which is three tier,
> using a .NET Client -> .NET Server which in turn connects to a Sql
> Server 2005 Instance.
> On top of this we also have a Windows Service which runs nightly
> processes also written in .NET
> and connects directly to the SQL Server instance.
> It's all working pretty well, except that in some installations the
> Nightly Processes service
> is failing to connect to the SQL Server instance.
> We're getting the following message:
> ________________________________________
_________________________
> 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
> .......
> ________________________________________
_________________________
> This has us confused. The nightly processes is using an indentical
> connection string
> to the .NET Server. The .NET application is connecting fine
> everytime.
> The service and the application server are sitting on the same machine
> as the SQL Server instance,
> so no remote connection should be needed.
> The problem seems to happen only on Windows Server Machines/SQL Server
> installations vs WinXP/SQLExpress, however that could be unrelated.
> Any thoughts appreciated.
> Cheers
> Chris.
>|||Cheers Bill,
I've taken a good look through your page.
I think the most likely scenario might be that the service
is running the local-system account and this may not
have access for some reason, the SQL services are running
under a different windows logon.
The SQL Server instance is running in mixed mode security.
I know where to find the permissions, how can I find out if "Local
System"
has access?
Cheers
Chris|||OK i've checked and LocalSystem already has Connect SQL access,
so I don't think it's that...
Hmmmm...