MS SQL Server upgrade?

Discussion in 'NZ Computing' started by disco_stu, Jul 18, 2005.

  1. disco_stu

    disco_stu Guest

    Hi guys,

    Someone (not me) has installed a eval copy of Microsoft SQL Enterprise
    server, and put it into production with live data. This person has now
    left my workplace and I've been tasked with upgrading it to a full
    version. Obviously the data needs to keep intact.

    Does anyone know if I can just run the full install over the top of the
    eval copy?

    Any help greatly appreciated.

    D.S.
    disco_stu, Jul 18, 2005
    #1
    1. Advertising

  2. In article <>,
    "disco_stu" <> wrote:

    >Does anyone know if I can just run the full install over the top of the
    >eval copy?


    To be safe, why not just back up the database and configuration files
    first? Then upgrade, and see what happens.
    Lawrence D¹Oliveiro, Jul 18, 2005
    #2
    1. Advertising

  3. disco_stu

    Chris Hope Guest

    Lawrence D¹Oliveiro wrote:

    > In article <>,
    > "disco_stu" <> wrote:
    >
    >>Does anyone know if I can just run the full install over the top of
    >>the eval copy?

    >
    > To be safe, why not just back up the database and configuration files
    > first? Then upgrade, and see what happens.


    And preferably try it out on a non-production box before you do it on
    the production box :)

    --
    Chris Hope | www.electrictoolbox.com | www.linuxcdmall.co.nz
    Chris Hope, Jul 18, 2005
    #3
  4. In article <dbfec6$b3h$>,
    Chris Hope <> wrote:

    >Lawrence D¹Oliveiro wrote:
    >
    >> In article <>,
    >> "disco stu" <> wrote:
    >>
    >>>Does anyone know if I can just run the full install over the top of
    >>>the eval copy?

    >>
    >> To be safe, why not just back up the database and configuration files
    >> first? Then upgrade, and see what happens.

    >
    >And preferably try it out on a non-production box before you do it on
    >the production box :)


    At some point, you have to bite the bullet and upgrade the production
    box anyway. My preferred approach would be to do a complete rsync backup
    of the current install. Then if anything goes wrong, you can do an rsync
    restore to undo your upgrade.

    For instance, I have started upgrading machines for one client to SuSE
    9.3. The very first machine we decided to do was the main departmental
    server. Why not start with some other less critical machine? Because
    that strategy turned out to be useless for determining how well the
    upgrade would go on the important machine.
    Lawrence D¹Oliveiro, Jul 18, 2005
    #4
  5. disco_stu

    Chris Hope Guest

    Lawrence D¹Oliveiro wrote:

    > In article <dbfec6$b3h$>,
    > Chris Hope <> wrote:
    >
    >>Lawrence D¹Oliveiro wrote:
    >>
    >>> In article <>,
    >>> "disco stu" <> wrote:
    >>>
    >>>>Does anyone know if I can just run the full install over the top of
    >>>>the eval copy?
    >>>
    >>> To be safe, why not just back up the database and configuration
    >>> files first? Then upgrade, and see what happens.

    >>
    >>And preferably try it out on a non-production box before you do it on
    >>the production box :)

    >
    > At some point, you have to bite the bullet and upgrade the production
    > box anyway. My preferred approach would be to do a complete rsync
    > backup of the current install. Then if anything goes wrong, you can do
    > an rsync restore to undo your upgrade.
    >
    > For instance, I have started upgrading machines for one client to SuSE
    > 9.3. The very first machine we decided to do was the main departmental
    > server. Why not start with some other less critical machine? Because
    > that strategy turned out to be useless for determining how well the
    > upgrade would go on the important machine.


    Your strategy may well work on a Linux box but it won't be so easy on a
    Windows box due to settings in the registry etc. And if the SQL server
    box is a web facing machine that needs to be up near 100% of the time
    you don't have the same luxury of messing around with it, especially
    given you need to bring a database service down before making binary
    copies of its data files if you want any sort of data integrity. Of
    course, if it's just an inhouse departmental machine then you can muck
    around with it outside business hours and it's not going to be so bad
    if it's down for a couple of hours.

    --
    Chris Hope | www.electrictoolbox.com | www.linuxcdmall.co.nz
    Chris Hope, Jul 18, 2005
    #5
  6. disco_stu

    Mercury Guest

    Hi,

    try this:
    http://support.microsoft.com/kb/281574/

    As always do a full backup first.

    For SQL Server 2000, it is advised to have Service Pack 3a installed. I
    would ensure the system is disconnected from any internet connection unless
    it is properly firewalled (IE ports 1433 & 1434 are blocked in both
    directions), do the upgrade, review the installation of SP3a (protects
    against the slammer worm) and install it especially if you have already had
    it installed.

    HTH.


    "disco_stu" <> wrote in message
    news:...
    > Hi guys,
    >
    > Someone (not me) has installed a eval copy of Microsoft SQL Enterprise
    > server, and put it into production with live data. This person has now
    > left my workplace and I've been tasked with upgrading it to a full
    > version. Obviously the data needs to keep intact.
    >
    > Does anyone know if I can just run the full install over the top of the
    > eval copy?
    >
    > Any help greatly appreciated.
    >
    > D.S.
    >
    Mercury, Jul 18, 2005
    #6
  7. disco_stu

    Ron McNulty Guest

    Use the Enterprise Manager backup tool to backup your database(s) first.
    Avoid rsync or similar "binary copy" tools. Databases are notorious for
    keeping hard-coded paths inside their binary files.

    The proper EM backup and restore work reliably, and result in a backup that
    can be reliably restored to other directories, and to other servers.

    You need to make sure you backup all the databases running on the server
    (and it is possible that more than one instance of SQL Server may be running
    on the same box).

    If you do a clean install, you will probably lose your SQL Server user and
    role configuration. If you don't have an SQL script to re-create users and
    roles, make one now. Hopefully you are not using replication, as that needs
    a lot of configuration too.

    But in my experience it is likely that the upgrade will go smoothly, so good
    luck.

    Regards

    Ron
    "disco_stu" <> wrote in message
    news:...
    > Hi guys,
    >
    > Someone (not me) has installed a eval copy of Microsoft SQL Enterprise
    > server, and put it into production with live data. This person has now
    > left my workplace and I've been tasked with upgrading it to a full
    > version. Obviously the data needs to keep intact.
    >
    > Does anyone know if I can just run the full install over the top of the
    > eval copy?
    >
    > Any help greatly appreciated.
    >
    > D.S.
    >
    Ron McNulty, Jul 18, 2005
    #7
  8. disco_stu

    disco_stu Guest

    Looks like it should be a simple task.

    Thanks for the help and advice.
    disco_stu, Jul 18, 2005
    #8
  9. disco_stu

    Dave Doe Guest

    In article <>,
    says...
    > Hi guys,
    >
    > Someone (not me) has installed a eval copy of Microsoft SQL Enterprise
    > server, and put it into production with live data. This person has now
    > left my workplace and I've been tasked with upgrading it to a full
    > version. Obviously the data needs to keep intact.
    >
    > Does anyone know if I can just run the full install over the top of the
    > eval copy?
    >
    > Any help greatly appreciated.


    Can't add much to Ron and Mercury's good advice - certainly, SQL S
    backups are the way to go (test a restore though! - you can restore an
    EM created backup to a different filename). And as Ron points out you
    can get EM to script up all sortsa things, including backup jobs too.

    --
    Duncan
    Dave Doe, Jul 19, 2005
    #9
  10. In article <TYLCe.1445$>,
    "Ron McNulty" <> wrote:

    >Avoid rsync or similar "binary copy" tools. Databases are notorious for
    >keeping hard-coded paths inside their binary files.


    I have successfully transferred entire MySQL databases between machines
    and between OS installs using rsync.
    Lawrence D¹Oliveiro, Jul 19, 2005
    #10
  11. disco_stu

    Chris Hope Guest

    Lawrence D¹Oliveiro wrote:

    > In article <TYLCe.1445$>,
    > "Ron McNulty" <> wrote:
    >
    >>Avoid rsync or similar "binary copy" tools. Databases are notorious
    >>for keeping hard-coded paths inside their binary files.

    >
    > I have successfully transferred entire MySQL databases between
    > machines and between OS installs using rsync.


    It's easy to do with MySQL, but other databases aren't so tolerant.
    Remember that the OP was talking about MS SQL Server, /not/ MySQL.
    I /know/ SQL Server isn't as tolerant as MySQL when it comes to
    straight binary copies, and I'm pretty sure I've read somewhere that
    Postgres isn't either.

    --
    Chris Hope | www.electrictoolbox.com | www.linuxcdmall.co.nz
    Chris Hope, Jul 19, 2005
    #11
  12. In article <dbicog$mq8$>,
    Chris Hope <> wrote:

    >Lawrence D¹Oliveiro wrote:
    >
    >> In article <TYLCe.1445$>,
    >> "Ron McNulty" <> wrote:
    >>
    >>>Avoid rsync or similar "binary copy" tools. Databases are notorious
    >>>for keeping hard-coded paths inside their binary files.

    >>
    >> I have successfully transferred entire MySQL databases between
    >> machines and between OS installs using rsync.

    >
    >It's easy to do with MySQL, but other databases aren't so tolerant.
    >Remember that the OP was talking about MS SQL Server, /not/ MySQL.
    >I /know/ SQL Server isn't as tolerant as MySQL when it comes to
    >straight binary copies, and I'm pretty sure I've read somewhere that
    >Postgres isn't either.


    Interesting. MySQL uses a very simple file structure: each database is
    a subdirectory in /var/lib/mysql containing just 3 files per table: the
    ..MYD file contains the table data, the .frm file contains the table
    structure, and the .MYI file contains the table indexes. You really only
    need to back up the first two for each table, since the indexes can
    always be rebuilt. That's all there is to it.
    Lawrence D¹Oliveiro, Jul 19, 2005
    #12
  13. disco_stu

    Chris Hope Guest

    Lawrence D¹Oliveiro wrote:

    > In article <dbicog$mq8$>,
    > Chris Hope <> wrote:
    >
    >>Lawrence D¹Oliveiro wrote:
    >>
    >>> In article <TYLCe.1445$>,
    >>> "Ron McNulty" <> wrote:
    >>>
    >>>>Avoid rsync or similar "binary copy" tools. Databases are notorious
    >>>>for keeping hard-coded paths inside their binary files.
    >>>
    >>> I have successfully transferred entire MySQL databases between
    >>> machines and between OS installs using rsync.

    >>
    >>It's easy to do with MySQL, but other databases aren't so tolerant.
    >>Remember that the OP was talking about MS SQL Server, /not/ MySQL.
    >>I /know/ SQL Server isn't as tolerant as MySQL when it comes to
    >>straight binary copies, and I'm pretty sure I've read somewhere that
    >>Postgres isn't either.

    >
    > Interesting. MySQL uses a very simple file structure: each database is
    > a subdirectory in /var/lib/mysql containing just 3 files per table:
    > the .MYD file contains the table data, the .frm file contains the
    > table structure, and the .MYI file contains the table indexes. You
    > really only need to back up the first two for each table, since the
    > indexes can always be rebuilt. That's all there is to it.


    MySQL is also incredibly easy to backup and restore with the command
    line tools.

    --
    Chris Hope | www.electrictoolbox.com | www.linuxcdmall.co.nz
    Chris Hope, Jul 19, 2005
    #13
  14. In article <dbids8$pc7$>,
    Chris Hope <> wrote:

    >Lawrence D¹Oliveiro wrote:
    >
    >> In article <dbicog$mq8$>,
    >> Chris Hope <> wrote:
    >>
    >>>Lawrence D¹Oliveiro wrote:
    >>>
    >>>> In article <TYLCe.1445$>,
    >>>> "Ron McNulty" <> wrote:
    >>>>
    >>>>>Avoid rsync or similar "binary copy" tools. Databases are notorious
    >>>>>for keeping hard-coded paths inside their binary files.
    >>>>
    >>>> I have successfully transferred entire MySQL databases between
    >>>> machines and between OS installs using rsync.
    >>>
    >>>It's easy to do with MySQL, but other databases aren't so tolerant.
    >>>Remember that the OP was talking about MS SQL Server, /not/ MySQL.
    >>>I /know/ SQL Server isn't as tolerant as MySQL when it comes to
    >>>straight binary copies, and I'm pretty sure I've read somewhere that
    >>>Postgres isn't either.

    >>
    >> Interesting. MySQL uses a very simple file structure: each database is
    >> a subdirectory in /var/lib/mysql containing just 3 files per table:
    >> the .MYD file contains the table data, the .frm file contains the
    >> table structure, and the .MYI file contains the table indexes. You
    >> really only need to back up the first two for each table, since the
    >> indexes can always be rebuilt. That's all there is to it.

    >
    >MySQL is also incredibly easy to backup and restore with the command
    >line tools.


    I must admit I've never used the MySQL command-line tools for
    backup/restore purposes. :)

    There's a lot to be said for simplicity as a foundation for building
    robust, mission-critical applications, isn't there...
    Lawrence D¹Oliveiro, Jul 19, 2005
    #14
  15. disco_stu

    Chris Hope Guest

    Lawrence D¹Oliveiro wrote:

    > In article <dbids8$pc7$>,
    > Chris Hope <> wrote:
    >
    >>Lawrence D¹Oliveiro wrote:
    >>
    >>> In article <dbicog$mq8$>,
    >>> Chris Hope <> wrote:
    >>>
    >>>>Lawrence D¹Oliveiro wrote:
    >>>>
    >>>>> In article <TYLCe.1445$>,
    >>>>> "Ron McNulty" <> wrote:
    >>>>>
    >>>>>>Avoid rsync or similar "binary copy" tools. Databases are
    >>>>>>notorious for keeping hard-coded paths inside their binary files.
    >>>>>
    >>>>> I have successfully transferred entire MySQL databases between
    >>>>> machines and between OS installs using rsync.
    >>>>
    >>>>It's easy to do with MySQL, but other databases aren't so tolerant.
    >>>>Remember that the OP was talking about MS SQL Server, /not/ MySQL.
    >>>>I /know/ SQL Server isn't as tolerant as MySQL when it comes to
    >>>>straight binary copies, and I'm pretty sure I've read somewhere that
    >>>>Postgres isn't either.
    >>>
    >>> Interesting. MySQL uses a very simple file structure: each database
    >>> is a subdirectory in /var/lib/mysql containing just 3 files per
    >>> table: the .MYD file contains the table data, the .frm file contains
    >>> the table structure, and the .MYI file contains the table indexes.
    >>> You really only need to back up the first two for each table, since
    >>> the indexes can always be rebuilt. That's all there is to it.

    >>
    >>MySQL is also incredibly easy to backup and restore with the command
    >>line tools.

    >
    > I must admit I've never used the MySQL command-line tools for
    > backup/restore purposes. :)
    >
    > There's a lot to be said for simplicity as a foundation for building
    > robust, mission-critical applications, isn't there...


    I back the databases up on my servers (mostly mysql, but some postgres)
    daily using the command line tools via cron, and then they're
    transferred to my local machine via rsync over ssh. While I could use
    rsync directly on the binary files it's not really safe as far as data
    integrity is concerned. I dump the structure into one file and then
    each table is saved into a separated tab delimited text file.

    Then all you need to do to restore a mysql database is:
    mysql -u [username] -p [database] < sql.sql
    for filename in `ls -1 *.txt`; do mysqlimport -d -u [username]
    -p[password] [database] `pwd`/$filename; done

    And because each table is saved to a separate tab file you can
    selectively restore what you want, or even open it in a spreadsheet app
    or another dbms if you really wanted to.

    --
    Chris Hope | www.electrictoolbox.com | www.linuxcdmall.co.nz
    Chris Hope, Jul 19, 2005
    #15
  16. disco_stu

    David Preece Guest

    Chris Hope wrote:
    > I'm pretty sure I've read somewhere that
    > Postgres isn't either.


    You're right, it isn't. You're supposed to backup by getting the DB to
    produce a big-arse SQL statement with all the data in.

    It's not, actually, as stupid as it sounds.

    Dave
    David Preece, Jul 20, 2005
    #16
  17. disco_stu

    Chris Hope Guest

    David Preece wrote:

    > Chris Hope wrote:
    >> I'm pretty sure I've read somewhere that
    >> Postgres isn't either.

    >
    > You're right, it isn't. You're supposed to backup by getting the DB to
    > produce a big-arse SQL statement with all the data in.
    >
    > It's not, actually, as stupid as it sounds.


    I agree. Binary backups of data are not a good idea, and between
    versions of a database are an even worse idea.

    Further down this branch of the thread I talk about the way I back up
    MySQL daily into separate sql and tab delimited data files. Very easy
    way to backup and restore, and the tab files are totally database
    independent.

    The other good thing is that rsync'ing over the wire on a big ass text
    file means you only download the changed stuff within those files. I
    have one postgres backup file which is about 2GB but it only needs to
    download a few megs of that each day.

    --
    Chris Hope | www.electrictoolbox.com | www.linuxcdmall.co.nz
    Chris Hope, Jul 20, 2005
    #17
  18. In article <dbku7i$fad$>,
    Chris Hope <> wrote:

    >David Preece wrote:
    >
    >> Chris Hope wrote:
    >>> I'm pretty sure I've read somewhere that
    >>> Postgres isn't either.

    >>
    >> You're right, it isn't. You're supposed to backup by getting the DB to
    >> produce a big-arse SQL statement with all the data in.
    >>
    >> It's not, actually, as stupid as it sounds.

    >
    >I agree. Binary backups of data are not a good idea, and between
    >versions of a database are an even worse idea.
    >
    >Further down this branch of the thread I talk about the way I back up
    >MySQL daily into separate sql and tab delimited data files. Very easy
    >way to backup and restore, and the tab files are totally database
    >independent.


    What do you do with blobs?

    >The other good thing is that rsync'ing over the wire on a big ass text
    >file means you only download the changed stuff within those files.


    rsync is just as smart with big binary files, in case you hadn't noticed.
    Lawrence D'Oliveiro, Jul 20, 2005
    #18
  19. disco_stu

    Chris Hope Guest

    Lawrence D'Oliveiro wrote:

    > In article <dbku7i$fad$>,
    > Chris Hope <> wrote:
    >
    >>David Preece wrote:
    >>
    >>> Chris Hope wrote:
    >>>> I'm pretty sure I've read somewhere that
    >>>> Postgres isn't either.
    >>>
    >>> You're right, it isn't. You're supposed to backup by getting the DB
    >>> to produce a big-arse SQL statement with all the data in.
    >>>
    >>> It's not, actually, as stupid as it sounds.

    >>
    >>I agree. Binary backups of data are not a good idea, and between
    >>versions of a database are an even worse idea.
    >>
    >>Further down this branch of the thread I talk about the way I back up
    >>MySQL daily into separate sql and tab delimited data files. Very easy
    >>way to backup and restore, and the tab files are totally database
    >>independent.

    >
    > What do you do with blobs?


    Apart from the fact I don't have any blobs in my database, I would
    imagine they get represented as ascii data.

    >>The other good thing is that rsync'ing over the wire on a big ass text
    >>file means you only download the changed stuff within those files.

    >
    > rsync is just as smart with big binary files, in case you hadn't
    > noticed.


    Yes, but it's not safe to do this with a binary file which is currently
    in use by a database.

    --
    Chris Hope | www.electrictoolbox.com | www.linuxcdmall.co.nz
    Chris Hope, Jul 20, 2005
    #19
  20. disco_stu

    Mr Scebe Guest

    "disco_stu" <> wrote in message
    news:...
    > Hi guys,
    >
    > Someone (not me) has installed a eval copy of Microsoft SQL Enterprise
    > server, and put it into production with live data. This person has now
    > left my workplace and I've been tasked with upgrading it to a full
    > version. Obviously the data needs to keep intact.
    >
    > Does anyone know if I can just run the full install over the top of the
    > eval copy?


    No i don't.

    > Any help greatly appreciated.


    SQL Server has some gotchas regarding backup and restores that if you don't
    get it right, can cause some real headaches - and may leave your data
    unrecoverable. If you don't know what you're doing, go back to whoever
    tasked you with this and tell them so - and advise them that they should
    fork out the money to get some expertise in to do it. If they are prepared
    to fork out for MSSQL Ent edition, then a few $$ for support is nothing in
    the grand scheme of things.

    --
    Mr Scebe
    Losersh always whine about their 'besht'.
    Winnersh go home and **** the prom queen".
    ~Sean Connery in "The Rock"
    Mr Scebe, Jul 20, 2005
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brian Whiting
    Replies:
    2
    Views:
    691
    Brian Whiting
    Dec 29, 2005
  2. =?Utf-8?B?U3VzaGls?=
    Replies:
    1
    Views:
    704
    Wayne
    Feb 16, 2006
  3. Tommy
    Replies:
    1
    Views:
    1,762
    Mr. Arnold
    Nov 29, 2007
  4. Replies:
    0
    Views:
    1,429
  5. Anand
    Replies:
    3
    Views:
    2,759
Loading...

Share This Page