Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: Replicating design changes

    I am trying to create an auto off-site backup of an entire database. This would include databases and users. It should also include changes made throughout the day.

    Something challenging about this is I want it to also include design changes that may have been done throughout the day.

    I understand log shipping or replication can deal with the data part of my solution. But how can I copy over the logins, users, and design changes?

    Is it possible to have design changes replicated from publishers to subscribers?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Log shipping will get you every change to a database. You'll get users, schema, data, the works...

    The drawback to this is that you don't get changes from OUTSIDE the database like logins. Those changes occur in another database, and although they do have a direct impact on your database, they aren't part of your database. As a general rule, those changes are small and they can be scripted out daily (or more often if necessary).

    -PatP

  3. #3
    Join Date
    Oct 2004
    Posts
    2

    Problem with log shipping

    Thanks for your reply P.

    The problem I have found with log shipping though is that the secondary server has to go into a sleep mode (please correct me if I am wrong). And we need to have that second server available to demonstrate minimum downtime in our recovery model.

    Perhaps what I am trying to create is some sort of SQL mirror.
    Is it possible?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing a LOT more about your application, it is hard to say.

    The environment you are describing (changing data, schema, access, etc) is a development environment. I've never seen a business need for more than daily snapshots of that kind of environment.

    A test environment has much more structured changes... The data changes a lot, but the schema probably only changes once or twice a day. The logins/users rarely change (usually only when personnel change).

    An actual production environment is far more structured. While the data often changes faster in production than in dev or test, the schema usually only changes when there is a software upgrade. This is typically once per week/month/quarter/year depending on the environment, but certainly not the kind of change you first described.

    Replication works well for production environments. Data flows quickly and smoothly. Users/security changes can be accomodated via scripting or similar tools. Schema changes require manual adjustment to the replication process, but those shouldn't happen often enough that they present any real problem.

    Which of the environments I've described is closest to what you are working with? Each of them takes different kinds of handling: their needs are different, their solutions are different.

    -PatP

Posting Permissions

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