After studying Log Shipping last week and reading up on it, it appears to be
the best solution for us. I am using to test servers to get it up and
running before releasing it to production.
I upgraded both servers from Standard to Enterprise edition. This allows me
the capabilities of using the DB Maint. Wizard to setup Log Shipping.
However, in setting it up, I am now encountering errors. I have researched
the errors on MS website but to no avail. Here is what I am doing and
getting:
1) I step through the Wizard as discussed in
http://msdn.microsoft.com/library/de...erver_8elj.asp
2) Upon finishing the wizrd I receive the error: "Unable to copy the
initialization file to \\SOURCE\data\file.bak"
Does anyone have any ideas on this? I have provided detailed steps below as
to my wizard selections.
Thanks,
Marty
Wizard:
1) Right clicked on the database on the SOURCE server.
2) Chose DB Maint. Plan
3) Clicked Next
4) Chose my DB then checked the Log Shipping option
5) Clicked Next
6) Clicked Next until the Specify the Transaction Log Share screen came up.
In this field I chose \\SOURCE\data
7) Clicked Next
8) Clicked ADD to add log shipping destination.
9) Chose the DESTINATION server.
10) Clicked OK
11) Clicked Next
12) Chose User Most Recent Backup File \\SOURCE\data\file.bak clicked Next
13) Clicked Next to the end
14) Clicked Finish
15) Error "Unable to Copy...\\SOURCE\data\file.bak" as mentioned above.
I tried with different login options and nothing works. When I tried it with
PERFORM A FULL DATABASE BACKUP NOW on step 12, I receive this error at the
end: "ERROR 3201: Cannot open Backup device '\\SOURCE\data\file.bak'. Device
error or device off-line. See the SQL Server error log for more details.
BACKUP DATABASE is terminating abnormally."
have a look at kb article 811890.
You also don't need the Enterprise Edition to do log shipping. SQL Server EE
has a log shipping wizard but you can roll your own. Here is a link to an
article which talks about how to do this:
http://www.sql-server-performance.co...g_shipping.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"M.Smith" <martys@.bennyhinn.org> wrote in message
news:ez1%23RkKKFHA.724@.TK2MSFTNGP10.phx.gbl...
> After studying Log Shipping last week and reading up on it, it appears to
be
> the best solution for us. I am using to test servers to get it up and
> running before releasing it to production.
> I upgraded both servers from Standard to Enterprise edition. This allows
me
> the capabilities of using the DB Maint. Wizard to setup Log Shipping.
> However, in setting it up, I am now encountering errors. I have researched
> the errors on MS website but to no avail. Here is what I am doing and
> getting:
> 1) I step through the Wizard as discussed in
>
http://msdn.microsoft.com/library/de...erver_8elj.asp
> 2) Upon finishing the wizrd I receive the error: "Unable to copy the
> initialization file to \\SOURCE\data\file.bak"
> Does anyone have any ideas on this? I have provided detailed steps below
as
> to my wizard selections.
> Thanks,
> Marty
> Wizard:
> 1) Right clicked on the database on the SOURCE server.
> 2) Chose DB Maint. Plan
> 3) Clicked Next
> 4) Chose my DB then checked the Log Shipping option
> 5) Clicked Next
> 6) Clicked Next until the Specify the Transaction Log Share screen came
up.
> In this field I chose \\SOURCE\data
> 7) Clicked Next
> 8) Clicked ADD to add log shipping destination.
> 9) Chose the DESTINATION server.
> 10) Clicked OK
> 11) Clicked Next
> 12) Chose User Most Recent Backup File \\SOURCE\data\file.bak clicked Next
> 13) Clicked Next to the end
> 14) Clicked Finish
> 15) Error "Unable to Copy...\\SOURCE\data\file.bak" as mentioned above.
> I tried with different login options and nothing works. When I tried it
with
> PERFORM A FULL DATABASE BACKUP NOW on step 12, I receive this error at the
> end: "ERROR 3201: Cannot open Backup device '\\SOURCE\data\file.bak'.
Device
> error or device off-line. See the SQL Server error log for more details.
> BACKUP DATABASE is terminating abnormally."
>
|||Thanks for the response Hilary...Acutally I have already read both of those
articles :-) I started my research with the one at sql-server-performance
and after researching thought that the wizard in EE would be easier.
So, I will step back and try to do it manually and see how that turns out.
Thanks again,
Marty
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecNEp4KKFHA.1172@.TK2MSFTNGP12.phx.gbl...
> have a look at kb article 811890.
> You also don't need the Enterprise Edition to do log shipping. SQL Server
> EE
> has a log shipping wizard but you can roll your own. Here is a link to an
> article which talks about how to do this:
> http://www.sql-server-performance.co...g_shipping.asp
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "M.Smith" <martys@.bennyhinn.org> wrote in message
> news:ez1%23RkKKFHA.724@.TK2MSFTNGP10.phx.gbl...
> be
> me
> http://msdn.microsoft.com/library/de...erver_8elj.asp
> as
> up.
> with
> Device
>
Showing posts with label reading. Show all posts
Showing posts with label reading. Show all posts
Friday, March 9, 2012
Log Shipping does not ship to secondary database
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
Log shipping between domains or different work groups is pretty picky. Here's a link I used when setting it up before.
http://support.microsoft.com/default.aspx/kb/321247/
This section may apply to your situation:
Cross Domain Log Shipping
If computers that are running SQL Server are placed in a multi-domain environment, Microsoft recommends that you set up two-way trusts between all domains that are involved in log shipping. However, if you cannot establish trusts between domains, you can use network pass-through security for log shipping. Refer to the section of this article that discusses the LocalSystem network account startup option for SQL Server-related services.
Why your restores are not working between instances is another story. Maybe try restoring the copied tran log manually and see if it works or generates any errors. Add some log files to the job steps directly and see if you get anymore information.
Good Luck|||
Hi Mark!Your post is certainly very helpful!
Could you correct me if i misunderstood the article please.
Does it mean that if i set up the SQL services log on account on the secondary database with the exact match of a local user account which exists on the primary
database server,
my secondary database SQL services will be able to access the primary database because it has bypass the network security?
Hope to hear your reply soon.
By the way i found out that certain ports has to be open to enable SQL services to execute the log shipping properly.
Port 135 - for the services
Ports 137, 138, and 139 or 445 need to be open too cuz im using UNC path for the folder sharing
This link will provide more information. Hope it will help others...
http://72.14.235.104/search?q
|||Yes, we synced the administrator passwords on both servers.
Good luck and thanks for the port info.|||
Hi Mark.
Turns out the log shipping still didnt work between the two servers even after opening the ports.
It keeps saying the network path could not be found each time the agent from the secondary database server tried to retrieve the
transaction log file to restore a new database.
Any idea if there is any other security settings that might be blocking the server from accessing the primary database
server network path? Or do you know if i have to enable any services?
My log shipping between the 2 instances work now though.
SQL Browser were disabled just had to enable it back.
Thanks in Advance!
|||If you have already done/tried the things below, I can't help you. There seems to be a network disconnect. Is there a trust set up between the 2 servers? Sorry, I'm not a network admin guy.
Local Network Account
You can use SQL Server to start under a locally-created network account. In the situation where there is network access required by a SQL Server process, which is the case if you have configured SQL Server to use log shipping, you can use network pass-through security. With pass-through security, all machines that will be accessed by SQL Server must have the same network account with the same password and appropriate permissions, configured locally. Additionally, when the SQL Server process requests resources from the second computer, traditional network security is bypassed if the same account (under which the requesting SQL Server service is started) exists with the same password. As long the account on the second computer is configured with enough permission to carry out the task that is requested by calling SQL Server, the task will be successful.Local System Account
You can also configure SQL Server to start under the Local System account. Modifying the password for the LocalSystem account may result in the failure of some services that are critical for system stability. This account is local to the computer where it resides, which means that the security context that SQL Server services uses is local. As stated in the Local Network Account section, you cannot use network pass-through security when you start SQL Server under the LocalSystem account because the passwords for the LocalSystem account on different computers are different. The starting of SQL Server under this account when network resource access is required will most likely result in the unsuccessful completion of tasks.Log Shipping does not ship to secondary database
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
Log shipping between domains or different work groups is pretty picky. Here's a link I used when setting it up before.
http://support.microsoft.com/default.aspx/kb/321247/
This section may apply to your situation:
Cross Domain Log Shipping
If computers that are running SQL Server are placed in a multi-domain environment, Microsoft recommends that you set up two-way trusts between all domains that are involved in log shipping. However, if you cannot establish trusts between domains, you can use network pass-through security for log shipping. Refer to the section of this article that discusses the LocalSystem network account startup option for SQL Server-related services.
Why your restores are not working between instances is another story. Maybe try restoring the copied tran log manually and see if it works or generates any errors. Add some log files to the job steps directly and see if you get anymore information.
Good Luck|||
Hi Mark!Your post is certainly very helpful!
Could you correct me if i misunderstood the article please.
Does it mean that if i set up the SQL services log on account on the secondary database with the exact match of a local user account which exists on the primary
database server,
my secondary database SQL services will be able to access the primary database because it has bypass the network security?
Hope to hear your reply soon.
By the way i found out that certain ports has to be open to enable SQL services to execute the log shipping properly.
Port 135 - for the services
Ports 137, 138, and 139 or 445 need to be open too cuz im using UNC path for the folder sharing
This link will provide more information. Hope it will help others...
http://72.14.235.104/search?q
|||Yes, we synced the administrator passwords on both servers.
Good luck and thanks for the port info.|||
Hi Mark.
Turns out the log shipping still didnt work between the two servers even after opening the ports.
It keeps saying the network path could not be found each time the agent from the secondary database server tried to retrieve the
transaction log file to restore a new database.
Any idea if there is any other security settings that might be blocking the server from accessing the primary database
server network path? Or do you know if i have to enable any services?
My log shipping between the 2 instances work now though.
SQL Browser were disabled just had to enable it back.
Thanks in Advance!
|||If you have already done/tried the things below, I can't help you. There seems to be a network disconnect. Is there a trust set up between the 2 servers? Sorry, I'm not a network admin guy.
Local Network Account
You can use SQL Server to start under a locally-created network account. In the situation where there is network access required by a SQL Server process, which is the case if you have configured SQL Server to use log shipping, you can use network pass-through security. With pass-through security, all machines that will be accessed by SQL Server must have the same network account with the same password and appropriate permissions, configured locally. Additionally, when the SQL Server process requests resources from the second computer, traditional network security is bypassed if the same account (under which the requesting SQL Server service is started) exists with the same password. As long the account on the second computer is configured with enough permission to carry out the task that is requested by calling SQL Server, the task will be successful.Local System Account
You can also configure SQL Server to start under the Local System account. Modifying the password for the LocalSystem account may result in the failure of some services that are critical for system stability. This account is local to the computer where it resides, which means that the security context that SQL Server services uses is local. As stated in the Local Network Account section, you cannot use network pass-through security when you start SQL Server under the LocalSystem account because the passwords for the LocalSystem account on different computers are different. The starting of SQL Server under this account when network resource access is required will most likely result in the unsuccessful completion of tasks.
Subscribe to:
Posts (Atom)