Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Ohio, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an application where I need to have two databases with the same information but one needs to keep the data for archiving and the main database needs to have a much shorter timeframe of data. I have looked into mirroring and replication but it seems to me that any changes (purging of data) done in the master database would then be carried over to the archive database. Is this correct? Is there a better way?

    Main database is SQL2000
    Archive database is SQL2008 running on Win2k8R2

    Thank you for any help,
    Pat Russell
    Process Automation Engineer

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Does the Archive database need to have just Table data in it, or do you need
    all the Views, Procs etc.
    If just table data, you could possible script a transfer of data from one database to the other,
    or maybe export the data from one as XML and then upload into the other,
    from a centrally accessible location.
    This way you could script Appends or Updates of data into the archive.

    Cannot really offer a solution because I have no experience with SQL 2000 or 2008 only 2005.

    There might be a far superior solution out there, (and there probably is), I'm just throwing ideas into the pot.
    Andrew

  3. #3
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,579
    Thanks
    5
    Thanked 1,057 Times in 926 Posts
    Quote Originally Posted by Pat Russell View Post
    I have an application where I need to have two databases with the same information but one needs to keep the data for archiving and the main database needs to have a much shorter timeframe of data. I have looked into mirroring and replication but it seems to me that any changes (purging of data) done in the master database would then be carried over to the archive database. Is this correct? Is there a better way?

    Main database is SQL2000
    Archive database is SQL2008 running on Win2k8R2
    The answer to your "is this correct" is YES. The answer to "is there a better way" depends on what your requirements are (i.e. legal, company regulation, discovery).

    I'm reading that you do not want the archive database to be a copy or the main database. Is that correct?

    Are you looking for the archive database to have a transactional history of the main database?

    Joe
    Joe

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Neither replication or mirroring will do what you want, as you surmised. I would probably create a trigger in the SQL Server 2000 database that copies the record before a delete and puts it in the archive database. However running 2000 on the "master" database and 2008 on the "archive" database may present some challenges. In general it works much better to do all of the administrative work from 2008 (or from 2005 if you have that) and connect to the 2000 database from SQL Server 2008. What you are looking at is not trivial however - and the best answer depends to some degree on the number of records in the table and how often they need to be copied across.
    Wendell

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    Ohio, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I do not need the archive to be a copy of the main. I would probably be fine with a daily update of the archive but would need to ensure there are no duplicate records copied across. The main database has been used as a data storage for a production facility and was used to serve up trending data for the operators and to pull historical reports on product that was run. The server I have it on was performing many other tasks so I purchased a new server and new instance of SQL (2008). I still need the old DB for the operators but will only need to keep about 2 weeks of rolling data in it. I plan to use the SQL2008 server as a web reporting and analysis setup to replace the historical aspects of the original. RIght now there are no regulatory requirements driving this but that could happen with some product we make in the future. The main purpose is for reporting and analysis. I know enough about SQL to get it running and tinker with views/spreadsheet reports. Very limited experience with triggers and complex queries.

    Thank you for the replies
    Pat Russell
    Process Automation Engineer

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    If all the tables have Primary Key fields, you ought to be able to prevent duplicates being transferred.
    Andrew

Posting Permissions

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