I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012)
This is a ONE WAY situation.
When you have to failover, it will be easy as the logs from sql server 2008 can be restored to sql server 2012.
The stopping stone will be FAIL BACK - if sql server 2008 is Primary and SQL Server 2012 is secondary (standby) server, after failing over to sql server 2012, you wont be able to failback.
Note: SQL Server allows you to restore transactions logs from earlier versions of the product to higher vesions but the upgrade doesn't happen until recovery of the database occurs. And that's why you can't use STANDBY mode in this situation. It has to be in RESTORING mode only.
The only option for failback will be to upgrade the old sql server 2008 to 2012.
At first i took a full backup of the first one and restored it on the other one and than i set up the log shipping. The databases first backup-restore took a few hours.
Logshipping is an automated process of doing FULL backup, copying it to secondary server and restoring it in NO-RECOVERY state and then taking consequent log-backups from the primary server, copying them to secondary server and restoring them.
Depending on the size of the database backup (whether you are using compression or not) and the network bandwidth, it can take from mins to hours. Other way is to initialize secondary database from the FULL backup of primary database - restore an existing backup of the primary database to the secondary database
i wish to know: how does the log shipping process knows from when to start the logs it is shipping?
The history and status of log shipping operations are always saved locally by the log shipping jobs. The history and status of the backup operation are stored at the primary server, and the history and status of the copy and restore operations are stored at the secondary server. If you have implemented a remote monitor server, this information is also stored on the monitor server.
Refer to : Monitor Log Shipping for more details about the tables and stored procedures used to track log-shipping jobs and status.
does it start from when the last backup-restore process happened or from the moment the log shipping itself is set ?
It starts when you initialize the secondary database from the full backup of primary database restored with NO RECOVERY on the secondary server. Logshipping proces relies on the concept called Log-sequence number (LSN's) to link which log backup will be restored after the last log backup.
A picture is more than a thousand words !
Reference
Can i be sure that no information was lost in these few hours ?
Yes there would be no information lost - unless in these few hours the primary server crashed or the backup becomes corrupted (due to I/O subsystem). As soon as the full backup is restored, logshipping will continue taking log backups on primary and start copying it to secondary server and restoring them.
The minimum backup/copy and restore frequency is 1 min.
Note: Once you configure a database for logshipping, you dont have to take any additional log backups - as this will break the LOG CHAIN and the logshipping will start failing.
Full and differential backups can be taken, but NO log backups.
Read up on more on How to Perform SQL Server Log Shipping BY BRAD MCGEHEE
This is perfectly viable (and is how I moved several databases before). The outline of the process is this:
- Make sure the source database is in full recovery mode
- Configure the source to no NO_TRUNCATE transaction log backup (so the log is preserved)
- Full Backup the source. Preferably use backup compression
- Take your full backup (on a USB stick if it takes to long to copy over the network) to
the target
- Restore full backup on target
- Take a log backup of the source
- Move (using your fastest method as in 4) the log backup to the target an apply it there
- Configure mirroring in async mode (source and target can now mirror)
- Start regular transaction log backups again on the primary
- Wait for mirror to catch up (use the mirror admin tools to monitor)
- Set mirror to sync mode
- Move the app pointer and manually fail over the mirror
A thing you have to be careful about is that you have enough space to hold the transaction log while the move is happening. But a 40GB database is tiny, so I assume you wont have a lot of tlog traffic on it.
Another thing to watch out for is that you have the same security set up on the target as on the source. If not, you may end up with orphaned users when you swing over the server. This is less of an issue if you only use Windows logins.
Getting this right takes a bit of practise. I would advise to test it out on a development system first to make sure you have all the steps scripted nicely.
Best Answer
The Service Account for SQL Server needs to be granted access to the network share in question.
This indicates a need for you to configure SQL Server to start with a Windows Active Directory account. If you don't have access to a domain account, you will need to create an account on the SQL Server machine with a matching account name and password on the target network machine.
Use SQL Server Configuration Manager to modify the startup config of the SQL Server Service.
The only way to allow a service on one machine to access a network share is to provide network access with a username and password. Make sure the SQL Server service can access the network share before restarting SQL Server by logging onto the SQL Server machine using the same username and password, and confirm you can see the network folder.
You also need to ensure you aren't trying to restore from a "mapped" network drive, since the "mapping" won't necessarily be there for the SQL Server service to "see". Instead of using a mapped drive letter for the restore, use a UNC name, such as \\server\share\folder\backup.bak