locked
SQL 2008 SP1 - Database Mirroring Maintenance RRS feed

  • Question

  • Hello All !

    I have created a mirroring environment with SQL 2008 Standard Edition SP1: it runs ok. I have set the threshold with the Database Mirroring Monitor. I have tested the different thresholds, IDs errors are well written in windows event logs

    I want to monitor these threshold with a monitoring tool (sorry for publicity, it's BMC Patrol). When a warning threshold appears, an information is written in Windows Event Logs and in Patrol.We don't use email alerts, just send an information in a log.

    So:

    - how can I write a threshold and sending it into a file (which will read by Patrol in order to extract the right information and make the good validation) ?

    - I read in different web articles that's not a good idea to create maintenance plans for backups logs, reindex, check integrity for databases mirroring (on principal), on the mirrored I understand because the DB is not avalaible. So, is it right or wrong ?

    - Are there official recommandations from MS about these ? Do I realize special scripts SQL task ? if yes, which scripts ?

     


    Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2
    Wednesday, September 15, 2010 7:57 AM

Answers

  • Hi,

    <<- how can I write a threshold and sending it into a file (which will read by Patrol in order to extract the right information and make the good validation) ?

    When a threshold is exceeded, an event is logged to the Application Event log. If you want to write these event logs to a log file, you may need to manually write an application or use 3rd party to read them from Application Event log.

    >>- I read in different web articles that's not a good idea to create maintenance plans for backups logs, reindex, check integrity for databases mirroring (on principal), on the mirrored I understand because the DB is not avalaible. So, is it right or wrong ?
    - Are there official recommandations from MS about these ? Do I realize special scripts SQL task ? if yes, which scripts ?

    SQL Server Maintenance Plans pre-define some regular maintenance tasks which will help you easy to maintain databases. If you want more flexibility, you can create plans manually using your own custom Transaction-SQL scripts. Each maintenance plan runs as a SQL Server Agent job; however which may fail if it is configured as scheduled and run regularly because the current database becomes mirror database and cannot be accessed. For such situation, don’t configure a schedule for the maintenance plan and you can run manually each time.

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by RHUM2 Tuesday, November 2, 2010 2:16 PM
    Thursday, September 23, 2010 3:05 AM

All replies

  • Regardless of what you read on the Internet (not everything on the Internet is true...) Maintenance Plans are a good thing. There are circumstances where a Maintenance Plan "may" not be flexible enough but those situations are rare. MPs have been tested by the MS SQL Server Product team. They are the recommended method of performing standard maintence operations.

    Your best source of official documentation is SQL Server 2008 R2 Books Online.

    If you're using a third party tool for monitoring (like BMC Patrol) expect to do some manual scripting. You could start by taking a look at logparser. You could write a query with logparser to query the windows event log and output what you want to a file.

    http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07

    Wednesday, September 15, 2010 11:14 AM
  • Hello Stephen !

    Thanks for your response: I don't use SQL 2008 R2 but SQL 2008 SP1. It's not the same product.

    I bought a book on mirroring "Pro SQL Server 2008 Mirroring" (of Robert L. Davis and Ken Simmons) and they said this (page 29)  - Extract:

    "Use T-SQL scripts or stored procedure rather than database maintenance plans for performing routine maintenance. Maintenance plans do not support  database mirroring and will fail if they attempt to perform maintenance on the mirror server. Have your scripts or procedures check the state or state_desc_columns of the sys.databases system view for values of zero(0) or online, respectively".

    Maintenance plans are not too good if I refer to this book.

    Microsoft PSS does not support logparser in production. It is the reason why we use a third party tool.

     


    Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2
    Wednesday, September 15, 2010 2:50 PM
  • Maintenance plans (2008 SP1 or 2008 R2) work ok for me. Did you select "Ignore offline databases"?

    For an integrated approach to monitoring SQL Server, use Microsoft System Center 2007 R2, SQL Server Management Packs, and SQL Server Performance Warehouse.

    Thursday, September 16, 2010 1:57 AM
  • Hello Stephen !

    Thanks for your responses. I will try this option, but if you read my latest message, you see that it is not simple.

    Perhaps you have customers with full Microsoft technologies but it's not my case. I should adapt me to infrastructures customers, and like they have hosted and mutualized infrastructures tools, I must do with their technologies (monitoring tools under Unix / Linux with Windows agents for SQL server, Windows OS, Sharepoint, etc)

     


    Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2
    Thursday, September 16, 2010 8:17 AM
  • Yep, worked with many customer with your situation. Not seen a "nice" linux/unix/sun/xyz monitoring system for MS technologies yet. Basically, rich alerts are forward to 3rd party monitoring tools but typically most of the rich information is lost or disguarded along the way. Also the 3rd party tools tend only to be able to raise simple alerts - they don't know how to interpret those alerts or take corrective action.

    Can't really recommend you using MS tools that aren't supported in prod environment... but the alternatives typically don't produce better results. :-)

    Thursday, September 16, 2010 8:29 AM
  • Hi,

    <<- how can I write a threshold and sending it into a file (which will read by Patrol in order to extract the right information and make the good validation) ?

    When a threshold is exceeded, an event is logged to the Application Event log. If you want to write these event logs to a log file, you may need to manually write an application or use 3rd party to read them from Application Event log.

    >>- I read in different web articles that's not a good idea to create maintenance plans for backups logs, reindex, check integrity for databases mirroring (on principal), on the mirrored I understand because the DB is not avalaible. So, is it right or wrong ?
    - Are there official recommandations from MS about these ? Do I realize special scripts SQL task ? if yes, which scripts ?

    SQL Server Maintenance Plans pre-define some regular maintenance tasks which will help you easy to maintain databases. If you want more flexibility, you can create plans manually using your own custom Transaction-SQL scripts. Each maintenance plan runs as a SQL Server Agent job; however which may fail if it is configured as scheduled and run regularly because the current database becomes mirror database and cannot be accessed. For such situation, don’t configure a schedule for the maintenance plan and you can run manually each time.

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by RHUM2 Tuesday, November 2, 2010 2:16 PM
    Thursday, September 23, 2010 3:05 AM