Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computing > NZ Computing > MS SQL Server upgrade?

Reply
Thread Tools

MS SQL Server upgrade?

 
 
Chris Hope
Guest
Posts: n/a
 
      07-19-2005
Lawrence DčOliveiro wrote:

> In article <TYLCe.1445$(E-Mail Removed)>,
> "Ron McNulty" <(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
 
Lawrence DčOliveiro
Guest
Posts: n/a
 
      07-19-2005
In article <dbicog$mq8$(E-Mail Removed)>,
Chris Hope <(E-Mail Removed)> wrote:

>Lawrence DčOliveiro wrote:
>
>> In article <TYLCe.1445$(E-Mail Removed)>,
>> "Ron McNulty" <(E-Mail Removed)> 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.
 
Reply With Quote
 
 
 
 
Chris Hope
Guest
Posts: n/a
 
      07-19-2005
Lawrence DčOliveiro wrote:

> In article <dbicog$mq8$(E-Mail Removed)>,
> Chris Hope <(E-Mail Removed)> wrote:
>
>>Lawrence DčOliveiro wrote:
>>
>>> In article <TYLCe.1445$(E-Mail Removed)>,
>>> "Ron McNulty" <(E-Mail Removed)> 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
 
Reply With Quote
 
Lawrence DčOliveiro
Guest
Posts: n/a
 
      07-19-2005
In article <dbids8$pc7$(E-Mail Removed)>,
Chris Hope <(E-Mail Removed)> wrote:

>Lawrence DčOliveiro wrote:
>
>> In article <dbicog$mq8$(E-Mail Removed)>,
>> Chris Hope <(E-Mail Removed)> wrote:
>>
>>>Lawrence DčOliveiro wrote:
>>>
>>>> In article <TYLCe.1445$(E-Mail Removed)>,
>>>> "Ron McNulty" <(E-Mail Removed)> 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...
 
Reply With Quote
 
Chris Hope
Guest
Posts: n/a
 
      07-19-2005
Lawrence DčOliveiro wrote:

> In article <dbids8$pc7$(E-Mail Removed)>,
> Chris Hope <(E-Mail Removed)> wrote:
>
>>Lawrence DčOliveiro wrote:
>>
>>> In article <dbicog$mq8$(E-Mail Removed)>,
>>> Chris Hope <(E-Mail Removed)> wrote:
>>>
>>>>Lawrence DčOliveiro wrote:
>>>>
>>>>> In article <TYLCe.1445$(E-Mail Removed)>,
>>>>> "Ron McNulty" <(E-Mail Removed)> 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
 
Reply With Quote
 
David Preece
Guest
Posts: n/a
 
      07-20-2005
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
 
Reply With Quote
 
Chris Hope
Guest
Posts: n/a
 
      07-20-2005
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
 
Reply With Quote
 
Lawrence D'Oliveiro
Guest
Posts: n/a
 
      07-20-2005
In article <dbku7i$fad$(E-Mail Removed)>,
Chris Hope <(E-Mail Removed)> 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.
 
Reply With Quote
 
Chris Hope
Guest
Posts: n/a
 
      07-20-2005
Lawrence D'Oliveiro wrote:

> In article <dbku7i$fad$(E-Mail Removed)>,
> Chris Hope <(E-Mail Removed)> 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
 
Reply With Quote
 
Mr Scebe
Guest
Posts: n/a
 
      07-20-2005

"disco_stu" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> 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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help. Getting a An error has occurred while establishing a connectionto the server. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allow remote aboutjav.com@gmail.com ASP .Net 0 05-03-2008 12:43 PM
SQL Server does not exist after moving to new Sql Server Geoff ASP .Net 3 01-16-2006 05:54 AM
Do the Self-Paced Training Kits: Microsoft SQL Server 2000 include Eval copy of SQL Server? Brian Whiting Microsoft Certification 2 12-29-2005 04:24 AM
sql server express vs sql server 2000 code Daves ASP .Net 1 06-13-2005 12:24 PM
Can't connect to SQL Server, using Windows Authentication users of SQL server? help =?Utf-8?B?UmV6YQ==?= ASP .Net 3 06-07-2004 06:42 PM



Advertisments