Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: replication help needed

    hi all, i have an issue to ask for ur help

    i am currently using MSSQL Server 2000 and i wish to replicate all the databases from 1 server to another server. The replication will be done daily at a certain time.
    Anyone noes how to do it?
    Thanks

  2. #2
    Join Date
    Sep 2004
    Posts
    22
    i'd say your best bet would be to schedule a dump, then restore to the specified server. Replication would be able to replicate all your data to your new database, but the distribution db would become immense, especially if you have high transactions. Not sure how much time it would take to replicate all those rows to the new server too.

    I'd go with the jobs personally. Schedule dump -> on success, exec job on new server that does a complete restore. No hassle with the subscriptions/publications etc...

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because I'd assume that you need backups anyway, this is a good approach. Bear in mind that you are likely to hit significant security problems using backup/restore, but that they can almost always be cured using a simple script.

    -PatP

  4. #4
    Join Date
    Nov 2004
    Posts
    3

    refer to suggestions

    hi all, thanks for ur suggestions, i will try and update here .thanks

  5. #5
    Join Date
    Nov 2004
    Posts
    3
    Quote Originally Posted by mitchell007
    i'd say your best bet would be to schedule a dump, then restore to the specified server. Replication would be able to replicate all your data to your new database, but the distribution db would become immense, especially if you have high transactions. Not sure how much time it would take to replicate all those rows to the new server too.

    I'd go with the jobs personally. Schedule dump -> on success, exec job on new server that does a complete restore. No hassle with the subscriptions/publications etc...
    Hi again, i saw that there is the Database Copy Wizard, to copy database between servers, however after selecting source and destination server, i cannot procced on with the transaction. there is the error message,

    CDW Error Message
    Your sql server servic is running under a local system account. you need to change your sql server service account to other rights to copy files over network

    anyone noes how to see or update the sql server service account?
    thanks

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many ways, the easiest is to use SQL Enterprise Mangler. Right click on the server of interest, then change the NT login to one that has administrator credentials on both the source and target machines for your copy operation.

    -PatP

  7. #7
    Join Date
    Nov 2004
    Posts
    3
    hihi thanks for ur help,but i haven dared to make the change.

    this is wat i found.
    right click on destination server, (my pc at the office), i chose properties, then under security . there is the Start Up service account.
    2 options are there.
    1st is System account and This account.
    howver, at This Account option, the entries are already entered.
    the entry is Local System and a password alreay there . the thing is that i don remember setting any option for this in the first place.
    is it safe to select 'This Account' option and try the copy db wizard again?
    thanks

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That depends...

    Don't you just love that answer?

    Local System is an NT defined login (or more accurately, the lack of a login). It causes a service to log in as though it were the local system itself, meaning that the service has unlimited authority on this machine (where the service is running), but no authority at all on any other machine.

    What you need to do now is to provide an NT login (such as the one that you use at the keyboard) for the SQL Server service to use. This NT login needs to have Administrator privledges on both the source SQL server and the destination SQL server in order for the Copy Database Wizard to succeed.

    -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
  •