Database Overwritten: machine

Showing posts with label machine. Show all posts

Sunday, March 25, 2012

Database Mirroring is not working at all in RTM

I tried all the scenarios .

1- three CLEAN machines in the same domain each has a default instance
2- 1 machine having principal and witness and another one having mirror
3- 1 machine has all

the service for all of them is running under 1 account which is the domain admin.

but still i always getting the same error 1418 "the nework address '...' can not be reached or does not exist ..." when i try to add the mirror server as a partner for the principal server from the principal server .

i tried using TSQL and management studio

this error is driving me crazy , i search on google even for it and in forums and BOL and everwhere .
i followed the exact steps .

You know that the mirroring is available only under trace flag in RTM version? You need to use TF-1400

Thanks

|||

This is Books Online info on this flag (1400):

Enables database mirroring. This trace flag is allowed only when using the -T trace# startup option during start up of the server instance. Therefore, enabling mirroring requires a restart of the server instance. For more information about using Setup options, see Using the SQL Server Service Startup Options.

Important:
Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.

After starting up a server instance using this trace flag, you can set up and evaluate database mirroring. For more information, see Setting Up Database Mirroring.

Trace flag 1400 is a temporary flag. This flag will be removed when database mirroring is made available for general production use.

|||Hi getting exactly the same problem, how and where exactly do you put the -T1400 ..? Ive put it what i think is the correct place, but still get the same error as you and the three operating modes under the mirroe tab are still grayed out..?

chris|||Basically, you need to modify the service that startups and add the flag to the end of the file path.|||

Open configuration tools, then choose SQL Server Configuration Manager. Double click on SQL Server; choose the advanced tab then startup parameters.
This is what mine looks like:

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf; -T1400

|||

i tried of course the Trace -T1400

but still the same

|||I was experiencing the same problem until I realised from the example above that there is a ; after the file path and before the trace switch. It's all working nicely now.|||

Hey all,

I am having the same problem. But the funny thing is that I had set up Mirroring on the three servers (Principal, Mirror and Witness) and tested it and everything was working fine unitil I needed to change the Server's computer name. I Stopped Mirroring, renamed my servers, dropped all the objects created during the setup of the mirror session(Master Key, Certificates, Endpoints, users, logins and even the database itself) on all the machines. then I followed the same steps to setup the mirroring session as before. I can proceed the setup until I get to the point of setup the partners. I connect to the Mirror server issue the Set Partner = 'TCP://PrincipalServer.Domain.ie:5024' and it works. Then I connect to the Principal and issue the the Set Partner ='TCP://MirrorServer.Domain.ie:5025' and what I get is this F*** Error 1418 saying that the server network address cannot be reached bla bla bla...

I have done follow the steps twice, so to make sure that I had made any mistakes. I check the ports on both servers to see if they are listen to 5024 and they are. I even reinstalled the damm server again with no success. I check to see if trace flag is enabled , I checked permissions, Endpoints, etc......... No success!

If there is anyone at the end of this line, who have an idea of what is going on, please give me a light. I don't know what else to look at.

regards,

adasilva

Database Mirroring is not working at all in RTM

I tried all the scenarios .

1- three CLEAN machines in the same domain each has a default instance
2- 1 machine having principal and witness and another one having mirror
3- 1 machine has all

the service for all of them is running under 1 account which is the domain admin.

i tried using TSQL and management studio

this error is driving me crazy , i search on google even for it and in forums and BOL and everwhere .
i followed the exact steps .

You know that the mirroring is available only under trace flag in RTM version? You need to use TF-1400

Thanks

|||

This is Books Online info on this flag (1400):

After starting up a server instance using this trace flag, you can set up and evaluate database mirroring. For more information, see Setting Up Database Mirroring.

Trace flag 1400 is a temporary flag. This flag will be removed when database mirroring is made available for general production use.

chris|||Basically, you need to modify the service that startups and add the flag to the end of the file path.|||

Open configuration tools, then choose SQL Server Configuration Manager. Double click on SQL Server; choose the advanced tab then startup parameters.
This is what mine looks like:

|||

i tried of course the Trace -T1400

but still the same

|||I was experiencing the same problem until I realised from the example above that there is a ; after the file path and before the trace switch. It's all working nicely now.|||

Hey all,

If there is anyone at the end of this line, who have an idea of what is going on, please give me a light. I don't know what else to look at.

regards,

adasilva

Database Mirroring is not working at all in RTM

I tried all the scenarios .

1- three CLEAN machines in the same domain each has a default instance
2- 1 machine having principal and witness and another one having mirror
3- 1 machine has all

the service for all of them is running under 1 account which is the domain admin.

i tried using TSQL and management studio

this error is driving me crazy , i search on google even for it and in forums and BOL and everwhere .
i followed the exact steps .

You know that the mirroring is available only under trace flag in RTM version? You need to use TF-1400

Thanks

|||

This is Books Online info on this flag (1400):

After starting up a server instance using this trace flag, you can set up and evaluate database mirroring. For more information, see Setting Up Database Mirroring.

Trace flag 1400 is a temporary flag. This flag will be removed when database mirroring is made available for general production use.

chris|||Basically, you need to modify the service that startups and add the flag to the end of the file path.|||

Open configuration tools, then choose SQL Server Configuration Manager. Double click on SQL Server; choose the advanced tab then startup parameters.
This is what mine looks like:

|||

i tried of course the Trace -T1400

but still the same

|||I was experiencing the same problem until I realised from the example above that there is a ; after the file path and before the trace switch. It's all working nicely now.|||

Hey all,

If there is anyone at the end of this line, who have an idea of what is going on, please give me a light. I don't know what else to look at.

regards,

adasilva

sql

Database Mirroring is not working at all in RTM

I tried all the scenarios .

1- three CLEAN machines in the same domain each has a default instance
2- 1 machine having principal and witness and another one having mirror
3- 1 machine has all

the service for all of them is running under 1 account which is the domain admin.

i tried using TSQL and management studio

this error is driving me crazy , i search on google even for it and in forums and BOL and everwhere .
i followed the exact steps .

You know that the mirroring is available only under trace flag in RTM version? You need to use TF-1400

Thanks

|||

This is Books Online info on this flag (1400):

After starting up a server instance using this trace flag, you can set up and evaluate database mirroring. For more information, see Setting Up Database Mirroring.

Trace flag 1400 is a temporary flag. This flag will be removed when database mirroring is made available for general production use.

chris|||Basically, you need to modify the service that startups and add the flag to the end of the file path.|||

Open configuration tools, then choose SQL Server Configuration Manager. Double click on SQL Server; choose the advanced tab then startup parameters.
This is what mine looks like:

|||

i tried of course the Trace -T1400

but still the same

|||I was experiencing the same problem until I realised from the example above that there is a ; after the file path and before the trace switch. It's all working nicely now.|||

Hey all,

If there is anyone at the end of this line, who have an idea of what is going on, please give me a light. I don't know what else to look at.

regards,

adasilva

Database Mirroring hangs on ALTER DATABASE SET PARTNER

Hi,

Here is the scenario. I currently have a single XP Professional machine with three SQL Server instances installed. I'm trying to set up a database mirroring session.

The mirror instance was created with an endpoint listening on port 5151. The principal instance endpoint listens on port 5150. The witness listens on port 5152.

After creating the endpoints, I back up the database I wish to mirror on the principal instance, and then restore with NORECOVERY on the mirror instance.

I then execute the following code on the mirror instance:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5151'

This works fine. I then go to the principal to add it as a partner too:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5150'

The query runs indefinitely. I finally stop it after several minutes, and I see in the object browser that the database has the following in parenthesis "(Principal, Synchronizing)". I then cannot drop, modify, or do anything with the principal database.

Ideas?

hmmmm....

the first ALTER DATABASE should always work (as long as the db has been restored with NORECOVERY) because it puts the database in a "waiting to be contacted" mode.

you should be using the FQDN instead of the IP. i think you are getting away with that because you are on the same server.

your port mappings are not right. if the mirror is listening on 5151, then the principal should be issuing its ALTER DATABASE with the port 5151 on it, not 5150 as you have indicated.

thanks,
mark|||Thanks for the response, Mark.

I went ahead and tried switching the ALTER DATABASE to point to the mirror port, and get the following message:

Msg 1418, Level 16, State 1, Line 3

The server instance "TCP://192.168.0.2:5022" is not running or does not exist. Check the name and reissue the command.

You said I should be on FQDN (but unfortunately this the only machine where I can test this - and it isn't on a domain). What's more, I used the GUI to set up mirroring, and it worked.

Right now my goal is to see if I can get it to work using just Transact-SQL . (I thought to capture what SQL Server was doing via the Wizard with Profiler, but I'm getting an "Cannot retrieve trace definition for SQL Server version
9.0.1187." error on that).

Thanks for any thoughts you may have on this...

|||

we aren't testing using the direct TCP-IP address for setting up database mirroring, so you need to use the FQDN.

there is a section on troubleshooting database mirroring in BOL specifically designed for this error message. please look at that.

specifically,
1. for the FQDN use the output described in the troubleshooting giude to use the output from ipconfig to set the name.
2. you should be able to run sqlservr.exe as LocalSystem as long as the instances are on the same machine.

mark

|||Thank you Mark.|||

I use this script to setup test mirrored db

-- On Principal
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

create database testMirror
--sp_helpdb testmirror

alter database testmirror
modify file (name=testmirror_log, size=1,maxsize=2,filegrowth=1)

alter database testmirror
modify file (name=testmirror, Maxsize=3,filegrowth=2)

alter database testMirror set recovery full

-- We use SQL lite speed if you don't have then you are missing out, however you can use SQL backup

exec master..xp_backup_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.init=1
exec master..xp_backup_log @.database='testMirror', @.filename='\\SERVERA\\sqlbak\testMirror_log.lsb',@.init=1

-- On mirror
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

exec master..xp_restore_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.With='NoRecovery'

exec master..xp_restore_log @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror_log.lsb',@.With='NoRecovery'
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERA:5022'

-- On principal
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERB:5022'

-- testing failing over, run on principal
ALTER DATABASE testMirror SET PARTNER FAILOVER

-- once testing is completed, break the mirror and drop the database
ALTER DATABASE testMirror SET PARTNER off
drop database testMirror

--NOTE: I have set this up not using FQDN and it works OK, also this script is for between servers

-- I have tried setting this up on a VISTA machine running 2 instances of SQL (dev edition) however I can get mirroring to work YET, I think I'm hitting a firewall issue (could be wrong), my ports are open correctly and I can see then via netstat - a

-- I'm getting errors like The Network address XYZ can't be reached or does not exist

-- Oh The joys of firewalls and ports....