SQL Server Performance

which replication?

Discussion in 'General DBA Questions' started by Reddy, Apr 24, 2007.

  1. Reddy New Member

    HI
    I want to update my database everyday for just reporting purpose from the original database. I was thinking of replication to use for this but not sure wht type of replication best fits into. My real purpose is to duplicate the original database at my end for reporting purpose and am not going change anything on my database.

    Pls let me know wht is the best i can do for this or can i just perform this through a DTS?

    Thanks!
    "He laughs best who laughs last"

  2. satya Moderator

    How many times in a day the secondary database will be used for reporting?
    How long the report will run on that database>?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. patel_mayur New Member

    My real purpose is to duplicate the original database at my end for reporting purpose and am not going change anything on my database.

    Its not good idea to use DTS for just dumping the database. You can use Snapshot Replication if you are not going to modify anything back.
  4. Reddy New Member

    just once in a day i need to update it, as of now we r updating it once in a month( it means its not tht important) and tht physically we r getting a cd which has updated data in it to dump the latest data.

    Thanks!
    "He laughs best who laughs last"

  5. satya Moderator

    If just once in a day then why not take help of log shipping and use the report process to perform at the end of the day on the secondary server. DUring this duration you can stop the log shipping and continue when its finished. This way you can give complete database based solution than selecting few tables with replication.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. Reddy New Member

    yeah i think tht wud be better. but in my envoronment we have limited resources, am not sure how much disk space i need to implement. I just have a source and stand by server, can i use my standby server as monitor server also.
    I want to have just 1 trans bkup per day and restore it on standby every night so tht it is synch.
    Few questions..

    1)while doing log shipping every night, before restoring the trans bakup do i need to manually write a script to kill the users connected to stand by server.
    2)what option I have to use when restoring the trans bakup.
    3)How abt the full bakups , do i still need them every day?
    4)when compared to using DTS for this job how much more resources am going to use for log shipping?


    Pls help me with patience..thanks a lot.

    Thanks!
    "He laughs best who laughs last"

  7. ndinakar Member

    Why not just back up the source DB and restore it on the reporting DB? That is what we do, on a daily basis for a 2.5 TB DB.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  8. satya Moderator

    1. Ensure no users are connected in Reporting servcer by running ALTER DATABASE statement, refer to BOL.
    2. Use STANDBY option, again BOL is your friend.
    3. Once you have restored full database backup before the log shipping then you are good to go and in any case if there is a mismatch of logs then using the full database backup restore is good to go again.
    4. DTS is limited to number of tables, how about stored procedures that are updated on the main database and having them on reporting server too. So using Log shipping (custom based) will also do.

    Recently I have completed such setup for a client (constraints such as server & cost) successfully with no issues, drop me an email for more information for such installation (if you wish to).

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  9. MohammedU New Member

    I believe you have to have EE of sql server to use built in log shipping in sql server 2000.
    If you want you can use custom log shipping..
    For smaller database daily full backup/restore should be fine as along as you don't need upto the minute data for reporting.

    As Dinakar mentioned restoring 2.5 TB for reporting might be painful using native sql backup and it is good to use HW solution.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  10. Reddy New Member

    Dinakar
    As u said I can do a completed DB bakup-restore daily thts vesy simple but the thing is I have network bandwidth issue in my environment where I have to do the restores from 9 different location every day which may nearly 2Gb each.

    Thanks!
    "He laughs best who laughs last"

  11. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] thats the limitation due to the geographical & infrastructure related issues, anyway as referred you can take help of log shipping where your traffic will be in limited as per the available resources of bandwidth. I hope you are aware about log shipping articles on this website, if not search through Log shipping forum section for more information.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>
  12. Reddy New Member

    If I need a backup file or transaction file from the source( remote area), what is the best/secure way to get the file into my network so that I can work on restore.

    Thanks!
    "He laughs best who laughs last"

  13. satya Moderator

    You should have some sort of connection (WAN) from a remote location to get from remote server, without any sort of connection there is no pure way to accomplish the standby server.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  14. Reddy New Member

    i dont think we have WAN or anything to some of the locations where we need to synch data but I hope we do have some software where we can login to their systems and wotk on them. what are the other ways of connections we can have to get the bakup files

    Thanks!
    "He laughs best who laughs last"

  15. satya Moderator

    Do you have any sort network connection between those 2 locations?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  16. Reddy New Member

    I think we dont have any type leased connection with them, may be its just DSL/Internet. am new to the environment so not much aware....

    Thanks!
    "He laughs best who laughs last"

  17. satya Moderator

    Without any dedicated connection between the servers its hard to achieve such inline operation of Log shipping or replication. Even any sort of standby servers approach is hard in this case.
    Only you can have an option of transferring data with the help of media (tapes or dvds) which is not secure in terms of data security. Until unless you sort this important bit it will be on paper to discuss.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  18. Reddy New Member

    does VPN/FTP any of them can work?

    Thanks!
    "He laughs best who laughs last"

  19. satya Moderator

    No, i don't think so and you have to test it by deploying the VPN access on SQL server.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  20. MohammedU New Member

    FTP should work with the help of custom log shipping but not built in log shipping...

    You can use tsql ftp code to upload the file to ftp site and down load them once they are available... You are concern about security you may need to use secure ftp software...

    http://www.sqlteam.com/item.asp?ItemID=6002

    Using FTP is not reliable log shipping but it works...
    I did this type of log shipping few years back...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  21. satya Moderator

    Even FTP need to have dedicated connection between the servers, even though it is log shipping or any other sort of connections.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  22. MohammedU New Member

    can be used FTP server where both sql servers able connect using internet without dedicated connection.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  23. Reddy New Member

    Finally I have decided to do log shipping thorugh FTP because that is the only way we have. We are just connected with public internet, and thats all we have. so here is the process....

    1)Initially am going to take a full back up from all the different places through Remote Desktop and apply them in my server.
    2) Setup all the servers to do Trans Backup for every 12hrs by tht way we'll have just 2 files
    3) These 2 files will be uploaded to ftp site every day which will be automated
    4) Same 2 files will downloaded through ftp into my server and then apply them to the databases.

    I want to automate this complete setup and have some notifications if any failure. I myself have to work on the source servers through Remote Desktop.

    DOUBT: Wht if my TRANS backup-restore fails, do I need to have Full Backup file at that point or Can I just start working with the next available TRANS-backup file.

    Please let me know the possiblities and Issues that can cause with this kind of setup

    Thanks!
    "He laughs best who laughs last"

  24. MohammedU New Member

    Read my comments....


    quote:Originally posted by Reddy

    Finally I have decided to do log shipping thorugh FTP because that is the only way we have. We are just connected with public internet, and thats all we have. so here is the process....

    1)Initially am going to take a full back up from all the different places through Remote Desktop and apply them in my server.
    2) Setup all the servers to do Trans Backup for every 12hrs by tht way we'll have just 2 files
    Transaction log backups will be taken more frequently to reduce the size of the tlog...and if your tlog files are bigger you can run the backup to multiple smaller files to copy easily over ftp
    3) These 2 files will be uploaded to ftp site every day which will be automated

    4) Same 2 files will downloaded through ftp into my server and then apply them to the databases.

    I want to automate this complete setup and have some notifications if any failure. I myself have to work on the source servers through Remote Desktop.

    DOUBT: Wht if my TRANS backup-restore fails, do I need to have Full Backup file at that point or Can I just start working with the next available TRANS-backup file.

    For any reason tlog backup fails due to LSN mismatch, you can try differential backup instead of again full backup.

    Please let me know the possiblities and Issues that can cause with this kind of setup

    Thanks!
    "He laughs best who laughs last"



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  25. Reddy New Member

    The Database will be updating not more than 1000 records every day so thought of having only 1 Trasnsaction bachkup per day. when you say to multiple files of tlog How can we backup to multiple file, pls guide.. I think we just need to add multiple locations for a backup to do so, am i right?


    Is there any third party software to do this efficeintly.

    Thanks!
    "He laughs best who laughs last"

  26. satya Moderator

    Multiple log files is to have a flexibility of breaking up the log backup, say if you can perform 3 log backups in a days that can divide 330 per backup file to reduce the contention of network.

    Either you can have 1 backup file WITH INIT or create backup with date-time values during the Tlog backup.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  27. MohammedU New Member

    quote:Originally posted by Reddy

    The Database will be updating not more than 1000 records every day so thought of having only 1 Trasnsaction bachkup per day. when you say to multiple files of tlog How can we backup to multiple file, pls guide.. I think we just need to add multiple locations for a backup to do so, am i right?


    Is there any third party software to do this efficeintly.

    Thanks!
    "He laughs best who laughs last"



    If your db is doing only 1000 row modifications per day then nothing to worry but for knowledge point you can run the full/diff/tlog backups to multiple backup files...See bol...

    command...
    Backup database dbname to
    disk = 'c:dbname1.bak',
    disk = 'c:dbname2.bak', disk = 'c:dbname2.bak'

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  28. satya Moderator

    Differential backups and log shipping will have issues (in my exp.) its better to choose either full and trans.log with log shipping or full and diff. backups periodically.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  29. Reddy New Member

    When working with transaction log backups, do we need to have the source and destination databases to be in either FULL/BULK-LOGGED recovery models or just the source should be in those models?

    Thanks!
    "He laughs best who laughs last"

  30. satya Moderator

    FULL is better suggested and within SQL 2000 using BULK LOgged is not suggested for point intime recovery. Check books online for 2000 in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  31. Reddy New Member

    Do u mean both source and destination DB's which are designed for Synch shud be in the same recovery model

    Thanks!
    "He laughs best who laughs last"

  32. MohammedU New Member

    You can't change distination database recovery model without recovering the db...
    When you are logshipping your destination db recovery will be maintened as the same as source db recovery model...

    Satya: I don't advise to use Differential backup for log shipping but it is very useful when the tlog LSN sequence breaks... because you can restore differential manually and continue your log restores instead of restoring big full backup file.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  33. satya Moderator

    Yes in the terms of log shipping you need to have both the databases with similar recovery model, which in this case FULL.
    I believe you need a thorough understanding of what is a recovery model and how you can achieve point in time recovery, please look into the books online(updated) for more information.

    Mohammed: I agree for the advantage of Diff. backups but when using log shipping do not mixup with diff.backups you will have mismatch of LSN, I have had similar issues in the past and found that mixup is the root cause. Anyway it is purely depends upon how quickly you want to recover the database.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  34. Reddy New Member

    Can i just have a full buck ONCE when i initially setup the job and just have 2 TRANS backups every day and restore it on destination to make it Synch. is this possible?

    Thanks!
    "He laughs best who laughs last"

  35. MohammedU New Member

    Yes, do manual restore full backup restore then schedule your tlog restore based on your backup and copy to the destination servers...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page