Log Shipping overview

Database Log Shipping is been around since SQL Server 7.0, It's a High Availability solution at the Database Level. It is categorized as a "Warm Standby" which means that the Secondary Database Server or the Fail-Over Partner is not always in-sync with the Primary Database Server and the main reason for it is because the Primary Server does not send the transaction as it happens to the second database server. Log Shipping is controlled by the SQL Server Agent Job, what happen is the primary server has a backup job that back-ups the transaction log at a specified intervals to a network share, then the secondary server will copy the transaction log and apply it in the Secondary Server.

Log Shipping in action (Video)

Log Shipping in action

Configuration
1. Go to our Primary Server (SQL1)
2. Create a Shared Folder "Primary Logs"







Configuration
1. Please make sure that the Domain account that is running the SQLAgent has administrative rights in the Shared Folder.










Configuration
1. Please make sure that the Domain account that is running the SQLAgent has administrative rights in the Shared Folder.










Configuration
1. Connect to our Primary Server (SQL1)








Configuration
1. Right click on our EmployeeDB databse










Configuration
1. Select the Transaction Log Shipping Page
2. Check the Enable this as a primary database in a log shipping configuration tickbox.
3. Click the Backup Setting button.






Configuration
1. Type-in the Network path of our Shared Primary logs folder.
2. Click on the Schedule button.









Configuration
1. Configure the Backup Schedule of the Primary Database.
2. Click Ok button.








Configuration
1. Click Ok Button.











Configuration
1. Let's add a secondary server instance.
2. Click Add button.








Configuration
1. Click the Connect button.









Configuration
1. Let's connect to our Secondary Database Server (SQL2).
2. Click Connect button.








Configuration
1. Initialize the Secondary Database.
2. Go to the Copy Files Tab.








Configuration
1. Type-in the Destination Folder for copied files.
2. Click the Schedule button.









Configuration
1. Schedule the Copy Job.
2. Click Ok button.








Configuration
1. Go to the Restore Transaction Log Tab.
2. Choose a Database state when restoring a backup.
3. Click the Schedule button.












Configuration
1. Schedule the restore job.
2. Click Ok button.








Configuration
1. Check the Use a monitor server instance tickbox.
2. Click the Settings button.








Configuration
1. Let's connect to our Monitor Server.
2. Click Connect button.







Configuration
1. Click Ok button.








Configuration
1. Review the Log Shipping Configuration.
2. Click Close button.



Testing
1. Connect to our Primary Database
2. create a Transaction.





Testing
1. Connect to our Read-Only Secondary Server.
2. Select the transaction you have created.

Domain Name System

DNS
1. Log on to Domain Controller using an account with administrative access. Launch the DNS manager and configure the reverse lookup zone.
2. Press right mouse button over the Reverse lookup zone node and select New zone.



DNS
1. Press next button to continue








DNS
1. On the zone type page, select the Primary zone option and check the Store zone in Active Directory checkbox.
2. Press next button to continue






DNS
1. Specify the replication scope or accept the default option.
2. Press next button to continue







DNS
1. Type in the Network ID to identify the reverse lookup zone.
2. Press next button to continue







DNS
1. Specify dynamic update option.
2. Press next button to continue







DNS
1. Check that all of the parameters are correct. Press Back button should any changes be required.
2. Press Finish button.






DNS
1. Press the right mouse button over emaxx.com.ph forward lookup zone and select New Host (A).






DNS
1. Type in the hostname of the Primary Server and it's IP address. Also please check the Create Associated Pointer (Ptr) checkbox.
2. Press the Add Host button to add SQL1 DNS Record.






DNS
1. Type in the hostname of the Warm Standby Server and it's IP address. Also please check the Create Associated Pointer (Ptr) checkbox.
2. Press the Add Host button to add SQL2 DNS Record

Configuring Virtual Domain Controller

VMWare workstation settings:
1. Processor: 2
2. Memory: 512 MB
3. Hard disk: 10 GB
4. Network Adapter Card: 1





Network Adapter:
1. The network adapter must be assigned a static IP Address.
2. The Subnet mask must also be provided, both values must be correctly chosen given the networking configuration of the corporate LAN.
3. AD requires DNS, an address must be provided, in this case we can specify 127.0.0.1 (loopback interface address).


Active Directory:
1. Select Start -> Run and type in dcpromo.




Active Directory Installation Wizard:
1. Press Next button to continue.







Active Directory Installation Wizard:
1. Press Next button to continue.







Active Directory Installation Wizard:
1. As we are creating a new domain, select Domain Controllers for a new domain.
2. Press Next button to continue.





Active Directory Installation Wizard:
1. Create new Domain Page appears. Select Domain in a new forest option.
2. Press Next button to continue.





Active Directory Installation Wizard:
1. Specify the full DNS name of the domain to create.
2. Press Next button to continue.





Active Directory Installation Wizard:
1. Specify Domain NetBIOS name.
2. Press Next button to continue.






Active Directory Installation Wizard:
1. Customize the directories where the AD Database log files will be stored or leave the values at their defaults.
2. Press Next button to continue.




Active Directory Installation Wizard:
1. Customize the directories where the SYSVOL data will be stored or leave the value at their default.
2. Press Next button to continue.




Active Directory Installation Wizard:
1. DNS Registration Diagnostics page appears. Select Install and configure the DNS Server on this computer, and set this computer to use this DNS server as its preferred DNS server option.
2. Press Next button to continue.


Active Directory Installation Wizard:
1. Select default permission for user and group objects.
2. Press Next button to continue.





Active Directory Installation Wizard:
1. Select default permission for user and group objects.
2. Press Next button to continue.





Active Directory Installation Wizard:
1. Review the options summary.
2. Press Next button to continue.






Active Directory Installation Wizard:
1. If successful, the wizard should look like the example image provided.
2. Press Next button to continue.
3. Restart.

Architecture

We will be using 1 Server that will run 3 Virtual Windows Server 2003:
1. Domain Controller (Virtual)
2. Primary Database Server (Virtual)
3. Warm Standy Server (Virtual)
4. Monitor Server (Host)