about_dbaship.help.txt
|
TOPIC
about_dbaship SYNOPSIS Basic logshipping Configuration. LONG DESCRIPTION A basic logshipping configuration tool using just a few parameters. CONTENT ----------------------------------------------------------------------- USE CASE PREPARATION PARAMETERS WHAT WILL HAPPEN INSTALL STEP-BY-STEP OTHER USE CASES TROUBLESHOOTING USE CASE ---------------------------------------------------------------------- Database db1, db2 and db3 are located in SQL-PRIMARY server. We need to create logshipping to SQL-SECONDARY Server and a monitor in SQL-MONITOR server. All Primary database files are currently in drive C: PREPARATION ------------------------------------------------------------------- For SQL-SECONDARY server two drives have been provided to separate mdfs and ldfs; D: for mdfs and E: for ldfs. A new drive, S: has been provided in Primary and Secondary servers for logshipping files, path S:\MSSQL\shipping has been created in both drives A share is created in Primary server as \\SQL-PRIMARY\shipping pointing to S:\MSSQL\shipping Both servers agent accounts have been given full access to to this share PARAMETERS -------------------------------------------------------------------- $credential required A PSCredential object, sql login account with sysadmin in both servers $credential = get-credential sysadmin_acount -primary_conn required name of the primary server, if is an instance use: host\instance[,port] i.e. primaryary_conn 'SQL04\QA,12345' -primary_database required name of primary database -primary_root required path where logshipping backup files will be stored the leaf folder needs to match the share name as in \\host\leaf i.e: primary_root S:\MSSQL\shipping primary_conn SQL-PRIMARY then: primary_share = \\SQL-PRIMARY\shipping -secondary_conn name of the secondary server, if is an instance use: host\instance[,port] i.e. secondary_conn 'SQL04\QA,12345' -secondary_root required Path where logshipping files will be copy to, from primary server share this is also the path were the files will be restored from, to secondary server. -data_root required Path where secondary mdf database files will be located Just the root, a subfolder 'DATA' will be created under this root -log_root required Path where secondary ldf database files will be located Just the root, a subfolder 'Log' will be created under this root -databases Optional, list Provide a list if multiple databases are been configured at the same time i.e. -databases 'db1', 'db2', 'db3' -monitor Optional The server name of the monitor server if monitor is an instance use: host\instance[,port] i.e. monitor_server 'SQL99\Monitor,12345' Notes Primary_root and secondary_root will also contain the initalization backups Make sure enought space is available, see cleaning section to manage space WHAT WILL HAPPEN -------------------------------------------------------------- With these parameters: -primary_conn 'SQL-PRIMARY' ` -primary_database 'db1' ` -primary_root 'S:\MSSQL\shipping' ` -secondary_conn 'SQL-SECONDARY' ` -secondary_root 'S:\MSSQL\shipping' ` -data_root 'D:\MSSQL' ` -log_root 'E:\MSSQL' -databases 'db1', 'db2', 'db3' ` -monitor 'SQL-MONITOR' Primary Server Installation --------------------------- This folder structure will be created in primary server: S:\MSSQL '-- shipping |-- db1 |-- db2 |-- db3 '-- dbaship Databases will be flagged as logshipping and a log shipping Backup job will be set for each database. If Monitor server is provided a linked server to the monitor server is created and the monitor server is set up. A COPY_ONLY initialization backup will be created in each database folder. Secondary Server Installation ----------------------------- These folder structures will be created in secondary server: S:\MSSQL '-- shipping |-- db1 |-- db2 |-- db3 '-- dbaship D:\MSSQL '-- DATA E:\MSSQL '-- Log The initialization backup will be first copied from \\SQL-PRIMARY\shipping to the corresponding database folder, and then, restored in the secondary server. Two jobs will be created for each database, one to copy logshipping files and another to restore the files into the secondary server database If Monitor server is provided a linked server to the monitor server is created and the monitor server is set up. INSTALL ----------------------------------------------------------------------- In primary server ( first, then in secondary server ) $credential = get-credential sysadmin_acount set-shipParam ` -primary_conn 'SQL-PRIMARY' ` -primary_database 'db1' ` -primary_root 'S:\MSSQL\shipping' ` -secondary_conn 'SQL-SECONDARY' ` -secondary_root 'S:\MSSQL\shipping' ` -data_root 'D:\MSSQL' ` -log_root 'E:\MSSQL' -databases 'db1', 'db2', 'db3' ` -monitor 'SQL-MONITOR' A feed-back report will help you verify all parameters are ok After you review the response and there are not any errors install-shipConfig Once the process finishes check the monitor server or run get-shipMonitor Repeat installation in Secondary server to complete process That's it! If you need to create a very customized logshipping you may use the step-by-step guide below =============================== STEP-BY-STEP ================================ In Primary Server ----------------- Run the $credential and set-shipParam in the INSTALL step, above. add-shipFolder this comand will create this folder structure in primary server: S:\MSSQL '-- shipping |-- db1 |-- db2 |-- db3 '-- dbaship set-shipPrimary -run -fullmodel This command will set up the database for logshipping and create the backup job. A schedule will be created for the job. the -fullmodel switch will set the database to full model in case it is not. If you are running this process in an environment without a backup plan you may need to add the -bypass switch to bypass the check for full backups. backup-shipPrimary This command will create a copy_only backup of the primary database. If you are running this process in an environment without a backup plan you need to use the -fullbackup switch to create a full backup instead of a copy_only backup, BE CAREFULL!!! set-shipMonitorPrimary -monitor_server 'MONITOR-SQL' This command will create a linked server to MONITOR-SQL and set MONITOR-SQL as the monitor for Primary-SQL log shipping get-shipReport This command will let you check the check your logshipping configuration In Secondary Server ------------------- Run the $credential and set-shipParam in the INSTALL step, above. add-shipFolder This command will create these folder structures in secondary server: S:\MSSQL '-- shipping |-- db1 |-- db2 |-- db3 '-- dbaship D:\MSSQL '-- DATA E:\MSSQL '-- Log copy-shipBackup This command will copy the initialization backup from the primary server share to the secondary server corresponding shipping folder. If you have Administrator rights in the host you may use the -robocopy switch. restore-shipSecondary This command will restore the secondary database to the secondary server. A new database will be created ant the files stored in paths provided in parameters -data_root and -log_root. The database will be in NORECOVERY mode. set-shipSecondary This command will setup destination database as logshipping and create two Jobs, one to copy the logshipping files and other to restore the logshipping files. Schedules will be created for both jobs. After the restore job cathes up with backup and copy files, the database will switch to STANDBY mode. set-shipMonitorSecondary -monitor_server 'MONITOR-SQL' This command will create a linked server to MONITOR-SQL and set MONITOR-SQL as the monitor for Secondary-SQL log shipping At this point your database is logshipped and should be up and running. MONITORING (Optional) --------------------------------------------------------- If you provided parameter monitor_server, The LSAlert_SQL-MONITOR job and job alerts will be created in SQL-MONITOR server Don't forget to add the operators to the alert to receive the messages. You can now use the Transaction Log Shipping Status Report in SSMS from the Monitor server. OTHER CASES ------------------------------------------------------------------- Instances: use host\instance,port i.e. secondary_conn = SQL04\QA,12345 Environment with no backup plan If you wil get an error that database full backup was not found use switch -fullbackup Do not use this switch in environments with backup plan ================================== ADVANCED =================================== removing logshipping -------------------- to remove db2 logshipping configuration: run set-shipParams using -primary_database 'db2' and run remove-shipConfig This commnad will remove all configuration and Jobs associated with the database log shipping. TROUBLESHOOTING --------------------------------------------------------------- Error No full backup was found for database Action Verify that no backup plan is set for the database and use switch -fullbackup Error database is in SINGLE model Action use switch -fullmodel Must errors are asociated with the lack of permission to the servers agent account. See advanced configuration below for more details. |