Loading ...
Sorry, an error occurred while loading the content.

Re: MUGH-SQLCON Methods to use reporting server as a backup server

Expand Messages
  • sunil john
    Dear Kuldeep, Q.1. What is background of your system and who majorly use this? Thanks for the mail, this system is an online card charging system, so people
    Message 1 of 4 , Aug 11 1:21 AM
    • 0 Attachment
      Dear Kuldeep,


      Q.1. What is background of your system and who majorly use this?
      Thanks for the mail, this system is an online card charging system, so people will use for charging and to check there account statements. In my scenario Log shipping, database mirroring and replication will not work because i want to delete old records from the production db and which should not happen on my backup db (which i am planning to use from reporting).

      Q.2. While using existing system, have users anytime reported any performance issues?
      If we use this same system for reporting after a long time, performance issue will come for both the application and reports for avoid this am planning for this.
      I think in my scenario i can use only SSIS packages. no other option i think right?

      Best Regards
      Sunil John

      ----- Original Message ----
      From: Kuldeep Chauhan <mrkuldeepchauhan@...>
      To: sqlcon@yahoogroups.com
      Sent: Wednesday, August 8, 2007 6:33:27 PM
      Subject: Re: MUGH-SQLCON Methods to use reporting server as a backup server













      Your statements are little contradicting, from the details provided by you....I guess that you don't have any high availability solution in your production environment. I have couple of questions and would appreciate if you can provide answers of them



      Q.1. What is background of your system and who majorly use this?



      Q.2. While using existing system, have users anytime reported any performance issues?



      Q.3 What is the high availability mechanism do you have currently?





      With the given set of information, I can think of two options at this moment.....BTW, first one can be ruled out as you don't have SQL Server 2005 Ent. edition.



      1. With SQL Server 2005 Ent edition, we can set-up a database mirroring between production and backup (mirror) server and on the mirror database, we can create a snapshot (snapshot feature is only available in SQL Server 2005 Ent edition). A snapshot can help you do all your reporting job, it work as an a point in time copy of your production. This is indeed a best approach for high load production scenarios.



      2. Create an ETL (Extract Transform & Load) solution using SSIS packages to apply insert & updates in backup (reporting) database tables and schedule it to run in every 15~20minutes. This will definitely require an average development effort for developing SSIS packages.





      Thanks,

      Kuldeep Chauhan

      Database Consultant



      sunil john <suniljk7@yahoo. com> wrote:





      Dear all,



      We are using SQL 2005 Std edition on

      windows 2003.



      We have

      production server which is running 24/7 and can't afford any downtime. So for

      our clients who needs to check reports everything we are planning to create a

      reporting server which will be populated with all data’s from production

      server, so the running query will not affect the production server.



      Please

      guide me with all possible methods to attain this.



      Requirement: -



      Reporting

      server should be updated with data from production server at least by every 30

      minutes.



      We are

      planning to delete records periodically from production server but this should

      not happen on the reporting server.



      (ie this

      reporting server should be a backup which will carry the whole data)



      Both Log

      shipping and Replication can't be used here.. Because in this method when i

      delete records (old records) from production server this will also remove the

      rows from reporting server which i don't want.



      (The only

      method which i see now is using a trigger or DTS, data will be pushed to

      reporting sever which i don't think a good method)



      Please

      guide me the with the methods i can attain this



      Best Regards

      Sunil John



      Send instant messages to your online friends http://uk.messenger .yahoo.com



      [Non-text portions of this message have been removed]



      ------------ --------- --------- ---

      Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.



      [Non-text portions of this message have been removed]














      <!--

      #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean, sans-serif;}
      #ygrp-mlmsg table {font-size:inherit;font:100%;}
      #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean, sans-serif;}
      #ygrp-mlmsg pre, code {font:115% monospace;}
      #ygrp-mlmsg * {line-height:1.22em;}
      #ygrp-text{
      font-family:Georgia;
      }
      #ygrp-text p{
      margin:0 0 1em 0;}
      #ygrp-tpmsgs{
      font-family:Arial;
      clear:both;}
      #ygrp-vitnav{
      padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
      #ygrp-vitnav a{
      padding:0 1px;}
      #ygrp-actbar{
      clear:both;margin:25px 0;white-space:nowrap;color:#666;text-align:right;}
      #ygrp-actbar .left{
      float:left;white-space:nowrap;}
      .bld{font-weight:bold;}
      #ygrp-grft{
      font-family:Verdana;font-size:77%;padding:15px 0;}
      #ygrp-ft{
      font-family:verdana;font-size:77%;border-top:1px solid #666;
      padding:5px 0;
      }
      #ygrp-mlmsg #logo{
      padding-bottom:10px;}

      #ygrp-vital{
      background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
      #ygrp-vital #vithd{
      font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform:uppercase;}
      #ygrp-vital ul{
      padding:0;margin:2px 0;}
      #ygrp-vital ul li{
      list-style-type:none;clear:both;border:1px solid #e0ecee;
      }
      #ygrp-vital ul li .ct{
      font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;padding-right:.5em;}
      #ygrp-vital ul li .cat{
      font-weight:bold;}
      #ygrp-vital a {
      text-decoration:none;}

      #ygrp-vital a:hover{
      text-decoration:underline;}

      #ygrp-sponsor #hd{
      color:#999;font-size:77%;}
      #ygrp-sponsor #ov{
      padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
      #ygrp-sponsor #ov ul{
      padding:0 0 0 8px;margin:0;}
      #ygrp-sponsor #ov li{
      list-style-type:square;padding:6px 0;font-size:77%;}
      #ygrp-sponsor #ov li a{
      text-decoration:none;font-size:130%;}
      #ygrp-sponsor #nc {
      background-color:#eee;margin-bottom:20px;padding:0 8px;}
      #ygrp-sponsor .ad{
      padding:8px 0;}
      #ygrp-sponsor .ad #hd1{
      font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:122%;}
      #ygrp-sponsor .ad a{
      text-decoration:none;}
      #ygrp-sponsor .ad a:hover{
      text-decoration:underline;}
      #ygrp-sponsor .ad p{
      margin:0;}
      o {font-size:0;}
      .MsoNormal {
      margin:0 0 0 0;}
      #ygrp-text tt{
      font-size:120%;}
      blockquote{margin:0 0 0 4px;}
      .replbq {margin:4;}
      -->








      Send instant messages to your online friends http://uk.messenger.yahoo.com

      [Non-text portions of this message have been removed]
    • Sandeep Chanda
      Hi Sunil, Decide on the following rules: 1) Scalability vs. Real Time availability a. Creating a ETL package will definitely help you have a
      Message 2 of 4 , Aug 11 2:17 AM
      • 0 Attachment
        Hi Sunil,

        Decide on the following rules:


        1) Scalability vs. Real Time availability

        a. Creating a ETL package will definitely help you have a scalable system, but at the same time will it be real time? What will trigger the Primary database to push changes to the backup db (Used by the report server)? If you schedule the package then there are fair chances of the report displayed being time bound.

        b. If you are not so worried about real time availability, I would say Kuldeep has suggested the best available approach.

        c. Alternatively you can plan a subscription based environment where the reports can be pushed to a file share and you can then display them using a web interface. (Cost effective approach).

        2) Scale up vs. Scale Out

        a. You can have a good server and have both the report server and catalog installed there. Educating the users to not use reports at peak transaction times should also help. (It has a huge cost when both are simultaneous and on the same box). Also use features such a snapshot compression etc to optimize performance.

        b. If you have a low cost server for reports (Assuming the database is on a 2 processor and above) and want significant performance gains then ideally have the Reports server engine on the database server and have the Report Catalogue on the other server.

        c. Again YMMV based on the hardware configuration.

        Regards
        Sandeep Chanda
        (Available on YAHOO : sandeep_chanda, MSN : sandeep.chanda@... )

        From: sqlcon@yahoogroups.com [mailto:sqlcon@yahoogroups.com] On Behalf Of sunil john
        Sent: Saturday, August 11, 2007 1:51 PM
        To: sqlcon@yahoogroups.com
        Subject: Re: MUGH-SQLCON Methods to use reporting server as a backup server


        Dear Kuldeep,

        Q.1. What is background of your system and who majorly use this?
        Thanks for the mail, this system is an online card charging system, so people will use for charging and to check there account statements. In my scenario Log shipping, database mirroring and replication will not work because i want to delete old records from the production db and which should not happen on my backup db (which i am planning to use from reporting).

        Q.2. While using existing system, have users anytime reported any performance issues?
        If we use this same system for reporting after a long time, performance issue will come for both the application and reports for avoid this am planning for this.
        I think in my scenario i can use only SSIS packages. no other option i think right?

        Best Regards
        Sunil John

        ----- Original Message ----
        From: Kuldeep Chauhan <mrkuldeepchauhan@...<mailto:mrkuldeepchauhan%40yahoo.com>>
        To: sqlcon@yahoogroups.com<mailto:sqlcon%40yahoogroups.com>
        Sent: Wednesday, August 8, 2007 6:33:27 PM
        Subject: Re: MUGH-SQLCON Methods to use reporting server as a backup server

        Your statements are little contradicting, from the details provided by you....I guess that you don't have any high availability solution in your production environment. I have couple of questions and would appreciate if you can provide answers of them

        Q.1. What is background of your system and who majorly use this?

        Q.2. While using existing system, have users anytime reported any performance issues?

        Q.3 What is the high availability mechanism do you have currently?

        With the given set of information, I can think of two options at this moment.....BTW, first one can be ruled out as you don't have SQL Server 2005 Ent. edition.

        1. With SQL Server 2005 Ent edition, we can set-up a database mirroring between production and backup (mirror) server and on the mirror database, we can create a snapshot (snapshot feature is only available in SQL Server 2005 Ent edition). A snapshot can help you do all your reporting job, it work as an a point in time copy of your production. This is indeed a best approach for high load production scenarios.

        2. Create an ETL (Extract Transform & Load) solution using SSIS packages to apply insert & updates in backup (reporting) database tables and schedule it to run in every 15~20minutes. This will definitely require an average development effort for developing SSIS packages.

        Thanks,

        Kuldeep Chauhan

        Database Consultant

        sunil john <suniljk7@yahoo. com> wrote:

        Dear all,

        We are using SQL 2005 Std edition on

        windows 2003.

        We have

        production server which is running 24/7 and can't afford any downtime. So for

        our clients who needs to check reports everything we are planning to create a

        reporting server which will be populated with all data's from production

        server, so the running query will not affect the production server.

        Please

        guide me with all possible methods to attain this.

        Requirement: -

        Reporting

        server should be updated with data from production server at least by every 30

        minutes.

        We are

        planning to delete records periodically from production server but this should

        not happen on the reporting server.

        (ie this

        reporting server should be a backup which will carry the whole data)

        Both Log

        shipping and Replication can't be used here.. Because in this method when i

        delete records (old records) from production server this will also remove the

        rows from reporting server which i don't want.

        (The only

        method which i see now is using a trigger or DTS, data will be pushed to

        reporting sever which i don't think a good method)

        Please

        guide me the with the methods i can attain this

        Best Regards

        Sunil John

        Send instant messages to your online friends http://uk.messenger .yahoo.com

        [Non-text portions of this message have been removed]

        ------------ --------- --------- ---

        Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.

        [Non-text portions of this message have been removed]

        <!--

        #ygrp-mlmsg {font-size:13px;font-family:arial, helvetica, clean, sans-serif;}
        #ygrp-mlmsg table {font-size:inherit;font:100%;}
        #ygrp-mlmsg select, input, textarea {font:99% arial, helvetica, clean, sans-serif;}
        #ygrp-mlmsg pre, code {font:115% monospace;}
        #ygrp-mlmsg * {line-height:1.22em;}
        #ygrp-text{
        font-family:Georgia;
        }
        #ygrp-text p{
        margin:0 0 1em 0;}
        #ygrp-tpmsgs{
        font-family:Arial;
        clear:both;}
        #ygrp-vitnav{
        padding-top:10px;font-family:Verdana;font-size:77%;margin:0;}
        #ygrp-vitnav a{
        padding:0 1px;}
        #ygrp-actbar{
        clear:both;margin:25px 0;white-space:nowrap;color:#666;text-align:right;}
        #ygrp-actbar .left{
        float:left;white-space:nowrap;}
        .bld{font-weight:bold;}
        #ygrp-grft{
        font-family:Verdana;font-size:77%;padding:15px 0;}
        #ygrp-ft{
        font-family:verdana;font-size:77%;border-top:1px solid #666;
        padding:5px 0;
        }
        #ygrp-mlmsg #logo{
        padding-bottom:10px;}

        #ygrp-vital{
        background-color:#e0ecee;margin-bottom:20px;padding:2px 0 8px 8px;}
        #ygrp-vital #vithd{
        font-size:77%;font-family:Verdana;font-weight:bold;color:#333;text-transform:uppercase;}
        #ygrp-vital ul{
        padding:0;margin:2px 0;}
        #ygrp-vital ul li{
        list-style-type:none;clear:both;border:1px solid #e0ecee;
        }
        #ygrp-vital ul li .ct{
        font-weight:bold;color:#ff7900;float:right;width:2em;text-align:right;padding-right:.5em;}
        #ygrp-vital ul li .cat{
        font-weight:bold;}
        #ygrp-vital a {
        text-decoration:none;}

        #ygrp-vital a:hover{
        text-decoration:underline;}

        #ygrp-sponsor #hd{
        color:#999;font-size:77%;}
        #ygrp-sponsor #ov{
        padding:6px 13px;background-color:#e0ecee;margin-bottom:20px;}
        #ygrp-sponsor #ov ul{
        padding:0 0 0 8px;margin:0;}
        #ygrp-sponsor #ov li{
        list-style-type:square;padding:6px 0;font-size:77%;}
        #ygrp-sponsor #ov li a{
        text-decoration:none;font-size:130%;}
        #ygrp-sponsor #nc {
        background-color:#eee;margin-bottom:20px;padding:0 8px;}
        #ygrp-sponsor .ad{
        padding:8px 0;}
        #ygrp-sponsor .ad #hd1{
        font-family:Arial;font-weight:bold;color:#628c2a;font-size:100%;line-height:122%;}
        #ygrp-sponsor .ad a{
        text-decoration:none;}
        #ygrp-sponsor .ad a:hover{
        text-decoration:underline;}
        #ygrp-sponsor .ad p{
        margin:0;}
        o {font-size:0;}
        .MsoNormal {
        margin:0 0 0 0;}
        #ygrp-text tt{
        font-size:120%;}
        blockquote{margin:0 0 0 4px;}
        .replbq {margin:4;}
        -->

        Send instant messages to your online friends http://uk.messenger.yahoo.com

        [Non-text portions of this message have been removed]



        [Non-text portions of this message have been removed]
      Your message has been successfully submitted and would be delivered to recipients shortly.