Regarding filegroups

Dear all,
I was just wondering myself how could I do for to obtain to which filegroup
belong a specific table.
Imagine that you have primary, primary_history, secondary_history,
index_history, bla,bla
Any idea or though would be very appreciated.
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)What version of SQL Server? For SQL Server 2000, use the groupid column in s
ysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
> Dear all,
> I was just wondering myself how could I do for to obtain to which filegrou
p
> belong a specific table.
> Imagine that you have primary, primary_history, secondary_history,
> index_history, bla,bla
> Any idea or though would be very appreciated.
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)|||Hi Tibor,
It's ok. That's fine but I'm looking for user tables no indexes.
Where does sql stores that?
I'm seeing sysobjects table with xtype = 'u'
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:

> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||The physical aspects if a table is represented in sysindexes. Use WHERE indi
d IN (0,1). See Books
Online, sysindexes for more details about sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9EA44F73-094E-4458-8A0C-FF1BBD34123E@.microsoft.com...
> Hi Tibor,
> It's ok. That's fine but I'm looking for user tables no indexes.
> Where does sql stores that?
> I'm seeing sysobjects table with xtype = 'u'
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>|||I've seen how, using DMO...
Another possibility?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:

> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||USE pubs
SELECT sfg.groupname
FROM sysfilegroups AS sfg
INNER JOIN sysindexes AS si
ON si.groupid = sfg.groupid
WHERE si.id = OBJECT_ID('dbo.authors')
AND indid IN(0,1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9A0F784A-A179-4593-9C8A-1226B0941772@.microsoft.com...
> I've seen how, using DMO...
> Another possibility?
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>

Referencing composite Primary KEYS

Hi,
i want to make a reference from a table on itself.
The table has a composite Primary Key. But I just want to refernce the TEstCaseID.
So whats wrong? Can anyone help me?

CREATE TABLE dbo.TestCase (
Project_projectID VARCHAR(20) NOT NULL references Project,
testCaseID VARCHAR(50) NOT NULL,
PRIMARY KEY(Project_projectID, testCaseID),
FatherID VARCHAR(50) references TestCase(testCaseID)

)

THanx CreanFor logical reasons, you can only reference unique values. Otherwise, how would SQL Server (or any database engine) know which of many records you were referencing?|||:D Already found out. But thanx a lot

Referencing a composite foreign key

I can't find any examples on how to do the below. Can anyone advise?
I have an 'Address' table which has a primary key constructed from
'postcode' and 'house no'.
I have numerous other tables that reference this table with a foreign
key - how do I reference a composite foreign key such as this - It
isn't a matter of just entering an integer as I won't know what the
composite value for a postcode and house no is.
Am I right in thinking that I need to make the 'postcode' and 'house
no' a composite UNIQUE to ensure these are unique and add an additional
integer primary key to the address entity which the other tables can
then reference?
Thanks,
DJWYou can reference a composite key via:
alter table MyTable
add constraint FK1_MyTable foreign key (postcode, houseno)
references OtherTable (postcode, houseno)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"dwj" <danielwatkinslearn@.hotmail.com> wrote in message
news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>I can't find any examples on how to do the below. Can anyone advise?
> I have an 'Address' table which has a primary key constructed from
> 'postcode' and 'house no'.
> I have numerous other tables that reference this table with a foreign
> key - how do I reference a composite foreign key such as this - It
> isn't a matter of just entering an integer as I won't know what the
> composite value for a postcode and house no is.
> Am I right in thinking that I need to make the 'postcode' and 'house
> no' a composite UNIQUE to ensure these are unique and add an additional
> integer primary key to the address entity which the other tables can
> then reference?
> Thanks,
> DJW
>|||How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>|||Precisely. As long as ('QW1 4BP', 90) exists in OtherTable, then the INSERT
will succeed.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:438d4ecc$1_1@.glkas0286.greenlnk.net...
How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>

Refer to ROWGUID col in update trigger

When writing an Update trigger on a table that has a UniqueIdentifier as the
primary key, how to I refer to the current row being updated.
I am used to using @.@.identity when working with primary keys that are of
type int but a UniqueIdentifier cannot be an identity column. I suspect it
has something to do with the "Is ROWGUID" property but I can't find the
function for getting the GUID of the row being updated.
Thanks,
Andrew.There isn't one. If you need to know this then you should generate the Guid
beforehand and use it in the insert statement.
--
Andrew J. Kelly
SQL Server MVP
"Andrew" <sql@.ses.ca> wrote in message
news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> When writing an Update trigger on a table that has a UniqueIdentifier as
the
> primary key, how to I refer to the current row being updated.
> I am used to using @.@.identity when working with primary keys that are of
> type int but a UniqueIdentifier cannot be an identity column. I suspect
it
> has something to do with the "Is ROWGUID" property but I can't find the
> function for getting the GUID of the row being updated.
> Thanks,
> Andrew.
>|||I want to access the row that is being updated, not inserted. The GUID is
already there.
Does this mean it is not possible to use a trigger to update a
"DateOfLastUpdate" field if the primary key is a GUID? I realize date could
be updated if the update was performed using a stored procedure but I feel
it would be safer to have this functionality on the table itself.
Thanks,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> There isn't one. If you need to know this then you should generate the
Guid
> beforehand and use it in the insert statement.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > When writing an Update trigger on a table that has a UniqueIdentifier as
> the
> > primary key, how to I refer to the current row being updated.
> >
> > I am used to using @.@.identity when working with primary keys that are of
> > type int but a UniqueIdentifier cannot be an identity column. I suspect
> it
> > has something to do with the "Is ROWGUID" property but I can't find the
> > function for getting the GUID of the row being updated.
> >
> > Thanks,
> > Andrew.
> >
> >
>|||> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
I tend to use a wrapper like:
IF NOT UPDATE(ModDate)
Just to prevent recursion. :-)|||Thanks for your help. I had just "discovered" the inserted table but the
method I came up with is not as clean as yours.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiAkt8iRDHA.3236@.TK2MSFTNGP10.phx.gbl...
> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u53MXdiRDHA.2424@.tk2msftngp13.phx.gbl...
> > I want to access the row that is being updated, not inserted. The GUID
is
> > already there.
> >
> > Does this mean it is not possible to use a trigger to update a
> > "DateOfLastUpdate" field if the primary key is a GUID? I realize date
> could
> > be updated if the update was performed using a stored procedure but I
feel
> > it would be safer to have this functionality on the table itself.
> >
> > Thanks,
> > Andrew
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > > There isn't one. If you need to know this then you should generate
the
> > Guid
> > > beforehand and use it in the insert statement.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Andrew" <sql@.ses.ca> wrote in message
> > > news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > > > When writing an Update trigger on a table that has a
UniqueIdentifier
> as
> > > the
> > > > primary key, how to I refer to the current row being updated.
> > > >
> > > > I am used to using @.@.identity when working with primary keys that
are
> of
> > > > type int but a UniqueIdentifier cannot be an identity column. I
> suspect
> > > it
> > > > has something to do with the "Is ROWGUID" property but I can't find
> the
> > > > function for getting the GUID of the row being updated.
> > > >
> > > > Thanks,
> > > > Andrew.
> > > >
> > > >
> > >
> > >
> >
> >
>

re-establishing mirror

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

If the witness server contacts the former principal, it should discover that mirroring was turned off and remove the metadata from master.

What version of SQL are you using? Build number?

Thanks,

Mark

|||

Witness came up but it did not remove the metadata. I am using SQL server 2005 sp1. Build number is 9.0.2047. Thanks

Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks.

|||

Thanks for the info.

"Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks."

1. There is no way to manually remove the witness information.

2. The principal server should be able to over write the witness information.

My guess is that there is a communication problem between the two servers. Did you change the port # for mirroring? Look in the errorlog. It should have more information about what went wrong. My guess is that you are setting up mirroring again and somehow changed the network configuration.

Thanks,

Mark

|||

Witness is showing the following error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://primary:5022'.

Primary server has the following error in the error log:

Database mirroring connection error 4 'An error occurred while receiving data: '1236(The network connection was aborted by the local system.)'.' for 'TCP://witness:5024'.

Server combination have been changed after the initial breakdown of database mirroring. So initial primary server is no longer the new primary server. So you are right about network settings being changed. What can I do so that I can reestablish the mirroring? Thanks.

|||

So, the only way that the witness can drop its mirroring information is to connect to one of the original servers and be told that the database is not there or the mirroring session is different. Now you can't change any of the connection parameters on the witness.

Setup a server just like on of the partners in the original scenario, which means have a sql server instance running on a server with the same name as one of the earlier partners AND have a mirroring endpoint up and running on the original port with the original settings.

The witness is using the orginal connection properties to log into the partner servers once a second. Once it gets to a sql server instance on the original ports, the witness will be told that database for which it wants to be a witness for doesn't exist or it isn't mirrored. At that point the witness will stop trying to be a witness.

As far as setting up the new witness, are you sure that the servers can connect to each other?

Thanks,

Mark

|||

I can't set up the original server. I don't have another computer to name as the original computer name and I can't change the name of the existing servers.

Current primary and witness can see each other. I created linked servers and verified queries.

When I am going through the wizard setting up mirroring on the primary server, security configuration is working fine. When I press start mirroring button, I am getting the error. Please let me know if you want to verify connection any other way or have any other ideas to set up mirroring. Thanks.

|||

So, if you can telnet into the endpoints from each server, then the endpoints on setup properly.

Is it still failing with the same error number? I believe that the witness server should be able to be a new witness server for this new database because the mirroring GUID will be different from the previous one.

But, to get rid of the original metadata for the first witness config, you need to have that server talk to sql on the original port and name.

Thanks,

Mark

|||If I can't get rid of original metadata, I can live with that. However I can't use the witness server because it is failing with the same error. Database name is same from the original one. I can try with a new database name to see if database name is the problem. I need to make this work with the original database name. The only other thing I can think of is reinstall mirror server. Thanks.|||I used a different database. Witness server did not work. Finally I completely uninstalled witness server and reinstalled wintness server. Re-established mirroring using the new mirror server. Now mirroring is working fine. So when things go wrong witness server will have certain stuff which will be only cleaned up if it can see the original servers. If it can't see the original servers then we can not only clean up witness server, that server will not be able to participate as witness server again. This could be an isolated situation, in Microsoft labs as Mark indicated, witness server works on unique guids, so one mirror combination should not effect others. In my case some thing went wrong. In rare cases like this it would be nicer if I can clean up the witness server rather than reinstall. Thanks very much Mark for all your help. On a different thread I asked about changing synchronous to asynchronous when witness and mirror are down. I would like to read your comments on that kind of thinking. Thanks again.|||

OK, so I'm sorry you had so much trouble with mirroring. The witness should have been able to be used as is with the metadata intact.

Since the information for the witness is stored in master. If you had a copy of master from before, you could have restored it and see what happens.

Since the database isn't on the witness server, there really isn't a good way to turn off the witness on the witness. We'll think about that.

Thanks,

Mark

|||I did not think about that. That's a good suggestion. Thanks Mark.|||I am having this very issue using certificate-based authentication. Am I correct in understanding that the current resolution, in absence of a backup of Master, is to uninstall and reinstall SQL Server 2005 SP1 on the witness server?|||

FYI, as it turned out in my case, I had been trying to use the IP address to configure the Witness (This worked for Principal and Mirror). E.g.:

ALTER DATABASE [AdventureWorks]
SET WITNESS = N'TCP://10.0.0.5:10001';
GO

That T-SQL statement, run on Principal, produced this error:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.0.0.5:10001'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

The Windows Event Log said:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://10.0.0.5:10001'.

The solution to my problems was when I put entries for Principal, Mirror, and Witness in each machine's C:\WINDOWS\system32\drivers\etc\hosts file.

Now it works.

|||

Hi,

I think i am having the same mirroring issues as you had.....in my case, server1 is the principal, server2 is the mirror and server2\witness is the witness..

i am getting the Msg 1456 error when i go through the mirroring wizard

in event log, i am getting the same error as you had...

can you please explain a little bit more on how to add entries in the hosts file?

thanks

re-establishing mirror

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

If the witness server contacts the former principal, it should discover that mirroring was turned off and remove the metadata from master.

What version of SQL are you using? Build number?

Thanks,

Mark

|||

Witness came up but it did not remove the metadata. I am using SQL server 2005 sp1. Build number is 9.0.2047. Thanks

Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks.

|||

Thanks for the info.

"Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks."

1. There is no way to manually remove the witness information.

2. The principal server should be able to over write the witness information.

My guess is that there is a communication problem between the two servers. Did you change the port # for mirroring? Look in the errorlog. It should have more information about what went wrong. My guess is that you are setting up mirroring again and somehow changed the network configuration.

Thanks,

Mark

|||

Witness is showing the following error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://primary:5022'.

Primary server has the following error in the error log:

Database mirroring connection error 4 'An error occurred while receiving data: '1236(The network connection was aborted by the local system.)'.' for 'TCP://witness:5024'.

Server combination have been changed after the initial breakdown of database mirroring. So initial primary server is no longer the new primary server. So you are right about network settings being changed. What can I do so that I can reestablish the mirroring? Thanks.

|||

So, the only way that the witness can drop its mirroring information is to connect to one of the original servers and be told that the database is not there or the mirroring session is different. Now you can't change any of the connection parameters on the witness.

Setup a server just like on of the partners in the original scenario, which means have a sql server instance running on a server with the same name as one of the earlier partners AND have a mirroring endpoint up and running on the original port with the original settings.

The witness is using the orginal connection properties to log into the partner servers once a second. Once it gets to a sql server instance on the original ports, the witness will be told that database for which it wants to be a witness for doesn't exist or it isn't mirrored. At that point the witness will stop trying to be a witness.

As far as setting up the new witness, are you sure that the servers can connect to each other?

Thanks,

Mark

|||

I can't set up the original server. I don't have another computer to name as the original computer name and I can't change the name of the existing servers.

Current primary and witness can see each other. I created linked servers and verified queries.

When I am going through the wizard setting up mirroring on the primary server, security configuration is working fine. When I press start mirroring button, I am getting the error. Please let me know if you want to verify connection any other way or have any other ideas to set up mirroring. Thanks.

|||

So, if you can telnet into the endpoints from each server, then the endpoints on setup properly.

Is it still failing with the same error number? I believe that the witness server should be able to be a new witness server for this new database because the mirroring GUID will be different from the previous one.

But, to get rid of the original metadata for the first witness config, you need to have that server talk to sql on the original port and name.

Thanks,

Mark

|||If I can't get rid of original metadata, I can live with that. However I can't use the witness server because it is failing with the same error. Database name is same from the original one. I can try with a new database name to see if database name is the problem. I need to make this work with the original database name. The only other thing I can think of is reinstall mirror server. Thanks.|||I used a different database. Witness server did not work. Finally I completely uninstalled witness server and reinstalled wintness server. Re-established mirroring using the new mirror server. Now mirroring is working fine. So when things go wrong witness server will have certain stuff which will be only cleaned up if it can see the original servers. If it can't see the original servers then we can not only clean up witness server, that server will not be able to participate as witness server again. This could be an isolated situation, in Microsoft labs as Mark indicated, witness server works on unique guids, so one mirror combination should not effect others. In my case some thing went wrong. In rare cases like this it would be nicer if I can clean up the witness server rather than reinstall. Thanks very much Mark for all your help. On a different thread I asked about changing synchronous to asynchronous when witness and mirror are down. I would like to read your comments on that kind of thinking. Thanks again.|||

OK, so I'm sorry you had so much trouble with mirroring. The witness should have been able to be used as is with the metadata intact.

Since the information for the witness is stored in master. If you had a copy of master from before, you could have restored it and see what happens.

Since the database isn't on the witness server, there really isn't a good way to turn off the witness on the witness. We'll think about that.

Thanks,

Mark

|||I did not think about that. That's a good suggestion. Thanks Mark.|||I am having this very issue using certificate-based authentication. Am I correct in understanding that the current resolution, in absence of a backup of Master, is to uninstall and reinstall SQL Server 2005 SP1 on the witness server?|||

FYI, as it turned out in my case, I had been trying to use the IP address to configure the Witness (This worked for Principal and Mirror). E.g.:

ALTER DATABASE [AdventureWorks]
SET WITNESS = N'TCP://10.0.0.5:10001';
GO

That T-SQL statement, run on Principal, produced this error:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.0.0.5:10001'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

The Windows Event Log said:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://10.0.0.5:10001'.

The solution to my problems was when I put entries for Principal, Mirror, and Witness in each machine's C:\WINDOWS\system32\drivers\etc\hosts file.

Now it works.

|||

Hi,

I think i am having the same mirroring issues as you had.....in my case, server1 is the principal, server2 is the mirror and server2\witness is the witness..

i am getting the Msg 1456 error when i go through the mirroring wizard

in event log, i am getting the same error as you had...

can you please explain a little bit more on how to add entries in the hosts file?

thanks

re-establishing mirror

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

If the witness server contacts the former principal, it should discover that mirroring was turned off and remove the metadata from master.

What version of SQL are you using? Build number?

Thanks,

Mark

|||

Witness came up but it did not remove the metadata. I am using SQL server 2005 sp1. Build number is 9.0.2047. Thanks

Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks.

|||

Thanks for the info.

"Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks."

1. There is no way to manually remove the witness information.

2. The principal server should be able to over write the witness information.

My guess is that there is a communication problem between the two servers. Did you change the port # for mirroring? Look in the errorlog. It should have more information about what went wrong. My guess is that you are setting up mirroring again and somehow changed the network configuration.

Thanks,

Mark

|||

Witness is showing the following error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://primary:5022'.

Primary server has the following error in the error log:

Database mirroring connection error 4 'An error occurred while receiving data: '1236(The network connection was aborted by the local system.)'.' for 'TCP://witness:5024'.

Server combination have been changed after the initial breakdown of database mirroring. So initial primary server is no longer the new primary server. So you are right about network settings being changed. What can I do so that I can reestablish the mirroring? Thanks.

|||

So, the only way that the witness can drop its mirroring information is to connect to one of the original servers and be told that the database is not there or the mirroring session is different. Now you can't change any of the connection parameters on the witness.

Setup a server just like on of the partners in the original scenario, which means have a sql server instance running on a server with the same name as one of the earlier partners AND have a mirroring endpoint up and running on the original port with the original settings.

The witness is using the orginal connection properties to log into the partner servers once a second. Once it gets to a sql server instance on the original ports, the witness will be told that database for which it wants to be a witness for doesn't exist or it isn't mirrored. At that point the witness will stop trying to be a witness.

As far as setting up the new witness, are you sure that the servers can connect to each other?

Thanks,

Mark

|||

I can't set up the original server. I don't have another computer to name as the original computer name and I can't change the name of the existing servers.

Current primary and witness can see each other. I created linked servers and verified queries.

When I am going through the wizard setting up mirroring on the primary server, security configuration is working fine. When I press start mirroring button, I am getting the error. Please let me know if you want to verify connection any other way or have any other ideas to set up mirroring. Thanks.

|||

So, if you can telnet into the endpoints from each server, then the endpoints on setup properly.

Is it still failing with the same error number? I believe that the witness server should be able to be a new witness server for this new database because the mirroring GUID will be different from the previous one.

But, to get rid of the original metadata for the first witness config, you need to have that server talk to sql on the original port and name.

Thanks,

Mark

|||If I can't get rid of original metadata, I can live with that. However I can't use the witness server because it is failing with the same error. Database name is same from the original one. I can try with a new database name to see if database name is the problem. I need to make this work with the original database name. The only other thing I can think of is reinstall mirror server. Thanks.|||I used a different database. Witness server did not work. Finally I completely uninstalled witness server and reinstalled wintness server. Re-established mirroring using the new mirror server. Now mirroring is working fine. So when things go wrong witness server will have certain stuff which will be only cleaned up if it can see the original servers. If it can't see the original servers then we can not only clean up witness server, that server will not be able to participate as witness server again. This could be an isolated situation, in Microsoft labs as Mark indicated, witness server works on unique guids, so one mirror combination should not effect others. In my case some thing went wrong. In rare cases like this it would be nicer if I can clean up the witness server rather than reinstall. Thanks very much Mark for all your help. On a different thread I asked about changing synchronous to asynchronous when witness and mirror are down. I would like to read your comments on that kind of thinking. Thanks again.|||

OK, so I'm sorry you had so much trouble with mirroring. The witness should have been able to be used as is with the metadata intact.

Since the information for the witness is stored in master. If you had a copy of master from before, you could have restored it and see what happens.

Since the database isn't on the witness server, there really isn't a good way to turn off the witness on the witness. We'll think about that.

Thanks,

Mark

|||I did not think about that. That's a good suggestion. Thanks Mark.|||I am having this very issue using certificate-based authentication. Am I correct in understanding that the current resolution, in absence of a backup of Master, is to uninstall and reinstall SQL Server 2005 SP1 on the witness server?|||

FYI, as it turned out in my case, I had been trying to use the IP address to configure the Witness (This worked for Principal and Mirror). E.g.:

ALTER DATABASE [AdventureWorks]
SET WITNESS = N'TCP://10.0.0.5:10001';
GO

That T-SQL statement, run on Principal, produced this error:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.0.0.5:10001'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

The Windows Event Log said:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://10.0.0.5:10001'.

The solution to my problems was when I put entries for Principal, Mirror, and Witness in each machine's C:\WINDOWS\system32\drivers\etc\hosts file.

Now it works.

|||

Hi,

I think i am having the same mirroring issues as you had.....in my case, server1 is the principal, server2 is the mirror and server2\witness is the witness..

i am getting the Msg 1456 error when i go through the mirroring wizard

in event log, i am getting the same error as you had...

can you please explain a little bit more on how to add entries in the hosts file?

thanks

re-establishing mirror

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

If the witness server contacts the former principal, it should discover that mirroring was turned off and remove the metadata from master.

What version of SQL are you using? Build number?

Thanks,

Mark

|||

Witness came up but it did not remove the metadata. I am using SQL server 2005 sp1. Build number is 9.0.2047. Thanks

Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks.

|||

Thanks for the info.

"Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks."

1. There is no way to manually remove the witness information.

2. The principal server should be able to over write the witness information.

My guess is that there is a communication problem between the two servers. Did you change the port # for mirroring? Look in the errorlog. It should have more information about what went wrong. My guess is that you are setting up mirroring again and somehow changed the network configuration.

Thanks,

Mark

|||

Witness is showing the following error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://primary:5022'.

Primary server has the following error in the error log:

Database mirroring connection error 4 'An error occurred while receiving data: '1236(The network connection was aborted by the local system.)'.' for 'TCP://witness:5024'.

Server combination have been changed after the initial breakdown of database mirroring. So initial primary server is no longer the new primary server. So you are right about network settings being changed. What can I do so that I can reestablish the mirroring? Thanks.

|||

So, the only way that the witness can drop its mirroring information is to connect to one of the original servers and be told that the database is not there or the mirroring session is different. Now you can't change any of the connection parameters on the witness.

Setup a server just like on of the partners in the original scenario, which means have a sql server instance running on a server with the same name as one of the earlier partners AND have a mirroring endpoint up and running on the original port with the original settings.

The witness is using the orginal connection properties to log into the partner servers once a second. Once it gets to a sql server instance on the original ports, the witness will be told that database for which it wants to be a witness for doesn't exist or it isn't mirrored. At that point the witness will stop trying to be a witness.

As far as setting up the new witness, are you sure that the servers can connect to each other?

Thanks,

Mark

|||

I can't set up the original server. I don't have another computer to name as the original computer name and I can't change the name of the existing servers.

Current primary and witness can see each other. I created linked servers and verified queries.

When I am going through the wizard setting up mirroring on the primary server, security configuration is working fine. When I press start mirroring button, I am getting the error. Please let me know if you want to verify connection any other way or have any other ideas to set up mirroring. Thanks.

|||

So, if you can telnet into the endpoints from each server, then the endpoints on setup properly.

Is it still failing with the same error number? I believe that the witness server should be able to be a new witness server for this new database because the mirroring GUID will be different from the previous one.

But, to get rid of the original metadata for the first witness config, you need to have that server talk to sql on the original port and name.

Thanks,

Mark

|||If I can't get rid of original metadata, I can live with that. However I can't use the witness server because it is failing with the same error. Database name is same from the original one. I can try with a new database name to see if database name is the problem. I need to make this work with the original database name. The only other thing I can think of is reinstall mirror server. Thanks.|||I used a different database. Witness server did not work. Finally I completely uninstalled witness server and reinstalled wintness server. Re-established mirroring using the new mirror server. Now mirroring is working fine. So when things go wrong witness server will have certain stuff which will be only cleaned up if it can see the original servers. If it can't see the original servers then we can not only clean up witness server, that server will not be able to participate as witness server again. This could be an isolated situation, in Microsoft labs as Mark indicated, witness server works on unique guids, so one mirror combination should not effect others. In my case some thing went wrong. In rare cases like this it would be nicer if I can clean up the witness server rather than reinstall. Thanks very much Mark for all your help. On a different thread I asked about changing synchronous to asynchronous when witness and mirror are down. I would like to read your comments on that kind of thinking. Thanks again.|||

OK, so I'm sorry you had so much trouble with mirroring. The witness should have been able to be used as is with the metadata intact.

Since the information for the witness is stored in master. If you had a copy of master from before, you could have restored it and see what happens.

Since the database isn't on the witness server, there really isn't a good way to turn off the witness on the witness. We'll think about that.

Thanks,

Mark

|||I did not think about that. That's a good suggestion. Thanks Mark.|||I am having this very issue using certificate-based authentication. Am I correct in understanding that the current resolution, in absence of a backup of Master, is to uninstall and reinstall SQL Server 2005 SP1 on the witness server?|||

FYI, as it turned out in my case, I had been trying to use the IP address to configure the Witness (This worked for Principal and Mirror). E.g.:

ALTER DATABASE [AdventureWorks]
SET WITNESS = N'TCP://10.0.0.5:10001';
GO

That T-SQL statement, run on Principal, produced this error:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.0.0.5:10001'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

The Windows Event Log said:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://10.0.0.5:10001'.

The solution to my problems was when I put entries for Principal, Mirror, and Witness in each machine's C:\WINDOWS\system32\drivers\etc\hosts file.

Now it works.

|||

Hi,

I think i am having the same mirroring issues as you had.....in my case, server1 is the principal, server2 is the mirror and server2\witness is the witness..

i am getting the Msg 1456 error when i go through the mirroring wizard

in event log, i am getting the same error as you had...

can you please explain a little bit more on how to add entries in the hosts file?

thanks