SQL Server Performance

A second server

Discussion in 'General DBA Questions' started by CanadaDBA, Feb 1, 2006.

  1. CanadaDBA New Member

    I have got a new server to be used as report server beside my SQL box. I can set a job to copy all tables into new server and users get their reports from that server.

    I have another idea that use the server as a stand by server. I mean using log shipping. This way the users can get their reports using their readonly IDs and if the main server went down, we can switch to the second one and save the business time.

    Is the second idea the best approach?
    What are the Pros and Cons of the second approach?
    Any better idea?

    Thanks,

    CanadaDBA
  2. SQLDBcontrol New Member

    The second idea will work but is only really practical if you don't ship the logs regularly. You can set up log shipping such that the secondary is in standby mode (giving readonly access to the database) but every time the log is shipped (and restored) any users will be kicked off the secondary.

    So if you ship the logs every hour or 15 minutes then your users will be disconnected every hour or 15 minutes.

    By the sounds of it, if you were going to be copying the tables to the new server, I imagine this was not going to be done regularly so log shipping might be a useful option for you.

    However, if you don't ship the logs regularly then you lose much of the benefit that you get from having a standby server.

    You could schedule the copy job of log shipping to copy regularly and then schedule the restore job to restore only between the hours of 6pm and 8am (for example). This means that if your first server goes down at 4pm, you would need to manually run the restore job on the secondary to restore all logs between 8am and 4pm. So you'd still get a standby server but you'd have to factor in the extra time it will take to restore all of those logs before you could bring the standby server online.

    Another alternative is to just copy the full backup to the second server and restore it each night. As long as users don't mind reporting on data that is 1 day old this is fine. But again, you don't get much of a standby server if the first one goes down.

    If you want users to be able to report throughout the day and also keep an up-to-date standby then replication is another consideration. Reporting users won't be affected (like in log shipping) and they have fresh data to report on. Plus you get a standby server. The only problem with replication is that it requires more management than log shipping and you'd have to keep on top of it more.

    I guess much depends on your situation but I would probably go for log shipping with the option of restoring logs outside of core business hours. This means that your users report on slightly old data and you get a standby server (albeit with a slightly slower start-up time).

    Hope that helps,

    Karl

    Karl Grambow

    www.sqldbcontrol.com
  3. CanadaDBA New Member

    Thanks Karl for the time you put to write this complete post.

    Replication looks more closer to my "Report" need but for "stand by" server I think I would have problem to set the secondary server to act as primary in case the primary went down.

    Then the Log Shipping comes up. My users use EM or QA to connect. So, if they lose their connections at the time of restore, the EM/QA will automatically reconnect them. Am I right?



    CanadaDBA
  4. SQLDBcontrol New Member

    With log shipping, after a user has lost their connection, the very next time they try to run a query they will get an error (something like a general network error? I can't remember the exact error and I'm not in front of a SQL Server to test this). After that initial error they will be ok with running subsequent queries, without having to re-connet, if I'm not mistaken.

    You'd have the same problems with log shipping or replication when it comes to setting the secondary server to act as the primary.

    Essentially, you'd need to get your applications to point to the secondary server. I suppose that another alternative would be to rename the server (and SQL Server). You'd have to factor in some time for DNS to catch on to the server name change.

    Hope that helps,


    quote:Originally posted by CanadaDBA

    Thanks Karl for the time you put to write this complete post.

    Replication looks more closer to my "Report" need but for "stand by" server I think I would have problem to set the secondary server to act as primary in case the primary went down.

    Then the Log Shipping comes up. My users use EM or QA to connect. So, if they lose their connections at the time of restore, the EM/QA will automatically reconnect them. Am I right?



    CanadaDBA

    Karl Grambow

    www.sqldbcontrol.com
  5. CanadaDBA New Member

    Well, I will implement Log Shipping. Can any one give me some directions about how I can use the second server in case the primary went down? What's the best practice?

    CanadaDBA
  6. bugster New Member

    Now this may or may not be an option for you because of different site situations. My client, having a validated enviromnent, needed a redundancy which if the primary dropped then the secondary, hopefully would still be running. I used two(2)Clients gathering the same data to send to each server. (Parallel network) The primary and secondary servers would hold the same data, but ODBC would be set for just primary server on other clients. The secondary server would be the reporting server. If primary server went down I would just select the secondary for ODBC on other clients so that they could get data. Keep in mind the site has special software because of pharmaceutical data. So I am not sure on your network config. So after writing all this....yep...you may be better off sticking with log shipping.

    the
    Bugster


    The
    Bugster

  7. CanadaDBA New Member

    Then the way you have implemented is neither log shipping nor replication, isn't it? I am confused about the two clients. Are they only two machines? Or two data entry users? I couldn't undrestand the part that other clients are connected to primary through ODBC; then how their updates in primary affects the secondary?


    quote:Originally posted by bugster

    Now this may or may not be an option for you because of different site situations. My client, having a validated enviromnent, needed a redundancy which if the primary dropped then the secondary, hopefully would still be running. I used two(2)Clients gathering the same data to send to each server. (Parallel network) The primary and secondary servers would hold the same data, but ODBC would be set for just primary server on other clients. The secondary server would be the reporting server. If primary server went down I would just select the secondary for ODBC on other clients so that they could get data. Keep in mind the site has special software because of pharmaceutical data. So I am not sure on your network config. So after writing all this....yep...you may be better off sticking with log shipping.

    the
    Bugster


    The
    Bugster



    CanadaDBA
  8. bugster New Member

    The software is "Metasys for Validated Environments" by Johnson Controls. Controllers gather data which and through software routing, is sent to a client (called a client collector). The client creates the tag and sends the data to server. Using the software routing I also sent the data to a secondary client collector which then sends to a secondary server. The secondary server has the same setup as the primary (with the exceptions of userIDs). Also, "System DSN" is named different on the secondary client collector. The data for workstations come from using OPC and history from the db in primary server. I know it is kind of strange, but it is actually working. lol.... The primary has shut down before and the customer was still able to get trend data from the secondary.


    the
    Bugster

    The
    Bugster

Share This Page