RE-add witness fails

I start with 3 servers, in High Safety mode with witness. I disconnect the network cable for the witness. Then I remove the witness from the mirroring session:

ALTER DATABASE db1 SET WITNESS OFF

I reconnect the witness network cable. Now I cannot add the witness back to the mirroring session (either with TSQL or thru the GUI). Here is the error message:

Msg 1433, Level 16, State 4, Server SERVERA, Line 1
All three server instances did not remain interconnected for the duration of the ALTER DATABASE SET WITNESS command. There may be no witness associated with the database. Verify the status and when necessary repeat the command.

I have to stop mirroring (SET PARTNER OFF) and then reconfigure a new mirroring session to be able to include the witness.

If I don't disconnect the network cable, I am able to remove and re-add the witness to the session, no problem.

Is this behavior expected?

Thanks in advance.

Regards,

Michael Lawson

I found my problem: my witness was at RTM. After upgrading to SP1 I was able to re-add the witness in the scenario I gave.

Read a file name from network folder automatically for a BULK INSERT

Hi again all,

Is there a way to read a file name automatically from a network folder? I can successfully bulk insert from this particular folder. The next step is as I add files, I wish to bulk insert the latest file added so the program must make that determination and import that specific file. I can delete the older files if necessary and save them elsewhere but it would still be nice to be able to read the file name. I then wish to store the name of this file, whatever it is, into a field called "SourceFileName" in my table that I am bulk inserting into. Does anyone have an example in dynamic SQL? Thanks.

ddaveDECLARE @.FilePathToSQLFiles VARCHAR(2000)
DECLARE @.Path VARCHAR(2000)

SET @.FilePathToSQLFiles = 'C:\'

CREATE TABLE #SQLFiles ( SQLFileName VARCHAR(2000))

SET @.Path = 'dir /b "' + @.FilePathToSQLFiles + '*.sql"'

INSERT INTO #SQLFiles
EXECUTE master.dbo.xp_cmdshell @.Pathsql

Re SQL Resolution Service

Hi,
I am a novice to SQL Server. I work in the area of network security. In my s
tudy of the SQL Slammer/Sapphire worm, I came across SQL Resolution Service
which listens on UDP Port 1434. It seems that this service is used by client
s to get the list of named
instances, to exchange keep-alive messages, and for opening a registry key (
the slammer worm cause). I would like to know what are its other uses and ot
her acceptable commands by the service. After my futile search on MSDN I am
posting a message here.
Any pointers or links regarding this are more than welcome.
Thanks in advance,
Bhagya
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.SQL Resolution Service on UDP 1434 is only used to support
multi-instances. It's not used with SQL Server 7 as that
version doesn't support named instances. It's not used by
the SQL Server instance or directly by clients to connect to
SQL Server. It's just to enumerate the instances on a server
and find the listening port for the specific instance.
If you try to connect to YourServer\YourNamedInstance and
that's what you specify for the connection, it hits UDP 1434
to use the SQL Server Resolution Service to find what port
number YourServer\YourNamedInstance is listening on. You can
bypass that by specifying the port yourself and then there
is no need to go through UDP 1434.
-Sue
On Wed, 28 Jul 2004 01:08:02 -0700, SqlJunkies User
<User@.-NOSPAM-SqlJunkies.com> wrote:

>Hi,
>I am a novice to SQL Server. I work in the area of network security. In my study of
the SQL Slammer/Sapphire worm, I came across SQL Resolution Service which listens o
n UDP Port 1434. It seems that this service is used by clients to get the list of na
med
instances, to exchange keep-alive messages, and for opening a registry key (the slammer worm
cause). I would like to know what are its other uses and other acceptable commands by the s
ervice. After my futile search on MSDN I am posting a message here.
>Any pointers or links regarding this are more than welcome.
>Thanks in advance,
>Bhagya
>--
>Posted using Wimdows.net NntpNews Component -
>Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports P
ost Alerts, Ratings, and Searching.|||I am looking for what are the uses of the resolution service which
runs on UDP 1434 and what commands it takes. I want to look at how did
the slammer worm succeed in triggering the vulnerability. From my
search on the Internet it seems that a command can be sent that starts
with '0x04' followed by some string, which results in opening a
registry entry on the server. What is the purpose of this command? Is
it for creating new named intances? If so, why would you allow anybody
to create new named instance on the server without any authentication?
any thoughts or ideas?
Thanks,
Bhagya
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<tg4fg0tm3lodstc7d5cllmj6g5guhh21
pt@.4ax.com>...[vbcol=seagreen]
> SQL Resolution Service on UDP 1434 is only used to support
> multi-instances. It's not used with SQL Server 7 as that
> version doesn't support named instances. It's not used by
> the SQL Server instance or directly by clients to connect to
> SQL Server. It's just to enumerate the instances on a server
> and find the listening port for the specific instance.
> If you try to connect to YourServer\YourNamedInstance and
> that's what you specify for the connection, it hits UDP 1434
> to use the SQL Server Resolution Service to find what port
> number YourServer\YourNamedInstance is listening on. You can
> bypass that by specifying the port yourself and then there
> is no need to go through UDP 1434.
> -Sue
> On Wed, 28 Jul 2004 01:08:02 -0700, SqlJunkies User
> <User@.-NOSPAM-SqlJunkies.com> wrote:
>
security. In my study of the SQL Slammer/Sapphire worm, I came across
SQL Resolution Service which listens on UDP Port 1434. It seems that
this service is used by clients to get the list of named instances, to
exchange keep-alive messages, and for opening a registry key (the
slammer worm cause). I would like to know what are its other uses and
other acceptable commands by the service. After my futile search on
MSDN I am posting a message here.[vbcol=seagreen]|||The internals of the Listener service are not available. Documentation on
the how the Listener service works with SQL
is included in SQL Books Online.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

ratio of disk traffic to network traffic

What is a normal ratio of disk traffic to network traffic on a SQL
Server? I've got 5x the disk traffic as network traffic and wondering
whether this indicates inefficiency in query design.The answer is, as is so often with computers and software, "it depends."
If you are talking about actual disk reads/writes, then...
For a well designed and configured OLTP database, the ratio between disk and
NIC bytes can be very small -smaller than 1:2 (with adequate memory and
fully cached data).
However, for OLAP (or Reporting Servers), the ratio can be very high (I've
seen over 1000:1) since a lot of output in that usage is summary data.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"DCole" <cole.consulting@.gmail.com> wrote in message
news:1161978008.508056.73230@.m7g2000cwm.googlegroups.com...
> What is a normal ratio of disk traffic to network traffic on a SQL
> Server? I've got 5x the disk traffic as network traffic and wondering
> whether this indicates inefficiency in query design.
>|||DCole wrote:
> What is a normal ratio of disk traffic to network traffic on a SQL
> Server? I've got 5x the disk traffic as network traffic and wondering
> whether this indicates inefficiency in query design.
>
There isn't a "normal" ratio for such a thing. For instance, consider
the following OVERLY SIMPLIFIED example:
1. I submit a query to SQL - "SELECT DISTINCT TOP 1 Address1 FROM
CustomerAddress", less than 40 characters being piped in as network traffic.
2. CustomerAddress contains 50 million rows, with Address1 being the
first line of a customer address, defined as a VARCHAR(255). There are
no indexes on the CustomerAddress table.
3. To find all of the distinct Address1 values, SQL is going to scan
the Address1 table, looking at each of the 50 million rows - lots of
disk I/O.
4. The TOP 1 clause will result in only a single row being returned by
the query, containing a single VARCHAR(255) column - AT MOST 255
characters of data will be returned.
So, you can see there is very little network traffic, but a large amount
of disk I/O produced. Query efficiency, indexes, schema design, all
will have a direct effect on the amount of disk I/O that you see.
Network I/O means very little in relation to disk I/O.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

ratio of disk traffic to network traffic

What is a normal ratio of disk traffic to network traffic on a SQL
Server? I've got 5x the disk traffic as network traffic and wondering
whether this indicates inefficiency in query design.The answer is, as is so often with computers and software, "it depends."
If you are talking about actual disk reads/writes, then...
For a well designed and configured OLTP database, the ratio between disk and
NIC bytes can be very small -smaller than 1:2 (with adequate memory and
fully cached data).
However, for OLAP (or Reporting Servers), the ratio can be very high (I've
seen over 1000:1) since a lot of output in that usage is summary data.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"DCole" <cole.consulting@.gmail.com> wrote in message
news:1161978008.508056.73230@.m7g2000cwm.googlegroups.com...
> What is a normal ratio of disk traffic to network traffic on a SQL
> Server? I've got 5x the disk traffic as network traffic and wondering
> whether this indicates inefficiency in query design.
>|||DCole wrote:
> What is a normal ratio of disk traffic to network traffic on a SQL
> Server? I've got 5x the disk traffic as network traffic and wondering
> whether this indicates inefficiency in query design.
>
There isn't a "normal" ratio for such a thing. For instance, consider
the following OVERLY SIMPLIFIED example:
1. I submit a query to SQL - "SELECT DISTINCT TOP 1 Address1 FROM
CustomerAddress", less than 40 characters being piped in as network traffic.
2. CustomerAddress contains 50 million rows, with Address1 being the
first line of a customer address, defined as a VARCHAR(255). There are
no indexes on the CustomerAddress table.
3. To find all of the distinct Address1 values, SQL is going to scan
the Address1 table, looking at each of the 50 million rows - lots of
disk I/O.
4. The TOP 1 clause will result in only a single row being returned by
the query, containing a single VARCHAR(255) column - AT MOST 255
characters of data will be returned.
So, you can see there is very little network traffic, but a large amount
of disk I/O produced. Query efficiency, indexes, schema design, all
will have a direct effect on the amount of disk I/O that you see.
Network I/O means very little in relation to disk I/O.
Tracy McKibben
MCDBA
http://www.realsqlguy.com