Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2013
    Posts
    1

    Unanswered: management wants a DB mirror on same server.

    My company is a mid sized business and I am the sole of IT. We have MAS, a common accounting software, and just upgraded to the MS SQL version.

    We connect to the SQL DB with ODBC from Access for some reporting.

    Management doesn't like the idea of us connecting directly into the database that is running our accounting software even though the account through which we are accessing is read-only.

    They want me to set up a database mirror and have it on the same server as kind of a redundancy.

    The server that is running the MAS and SQL has more than enough resources, I believe.

    Is this a very unusually request?

    I imagine common practice would be to mirror to an off-site server for failover or a server cluster for redundancy neither of which I need. (I have a backup NAS and there are not too many users.)


    Do I have to create a separate instance of SQL?

    How do I configure the endpoint ports to talk from the first to the mirror on the same server?

    I assume the mirror is 1-way?

    I have no one to discuss these with so any help is appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm nearly certain that you want About Log Shipping (SQL Server) instead of Database Mirroring.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    question - access reporting - do they need real-time data or could they live with previous day data. What I'm suggesting could save money + reduce the technical complexity. If reporting can live on previous day data, just create a scheduled backup/restore job that restores that same database as another name under the same instance. Then you won't need to buy additional sql licences/hardware.

    Thong

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Log shipping replication is the way to go.
    But you should convince your management to host it on a second server, so that it will serve the dual purpose of being a failover server for disaster recovery.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Log shipping or mirroring, the standby database will be in a recovering state. You can get a snapshot of a mirrored database, but I am not sure you can get a snapshot of a log-shipped database (I've not tried it, as yet). The snapshot capability requires enterprise edition, anyway, and I don't know if your management has shelled out for that.

    All that said, I have set up a single server with a database being mirrored on it. This is purely for "research" and demonstrations. It does require a second instance of SQL Server, and I set mine up such that the two endpoints were listening on separate ports.

    What is management's base concern, though? That you will accidentally update the database?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry, I should have said "Transactional Replication", under which both databases would be fully available for read access.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •