Hot answers tagged migration
42
Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012.
A Longer Answer on SQL Server Upgrades/Migrations
So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said ...
22
My favorite way is to pipe a sqldump command to a sql command. You can do all databases or a specific one. So, for instance,
mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserver
You can do all databases with
mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver
The only ...
17
You don't even need mysqldump if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered)
Stop the database (or lock it)
Go to the directory where the mysql data files are.
Transfer over the folder (and its contents) over to the new server's mysql data directory
Start back up the ...
15
I recently moved a 30GB database with the following stragegy:
Old Server
Stop mysql server
Copy contents of datadir to another location on disk (~/mysqldata/*)
Start mysql server again (downtime was 10-15 minutes)
compress the data (tar -czvf mysqldata.tar.gz ~/mysqldata)
copy the compressed file to new server
New Server
install mysql (don't start)
...
12
I have two suggestions:
1) I hate bringing up commercial products but there is a $49.00 tools to Migrate MSSQL to MySQL
2) Try MySQL's MSSQL Migration forums for further suggestions
UPDATE 2011-06-03 18:03 EDT
There is an old product that went EOL back in January 2010 called the MySQL Migration Toolkit. If you can get a hold of it, you can use it.
...
11
Personally, I would avoid the detach/attach mechanisms. Especially in SQL Server 2000, I just don't trust that you will always bring the server back up and be able to attach those files. I've heard plenty of stories where this didn't happen cleanly - just because you have a Plan B doesn't automatically make Plan A sensible.
With backup / restore, you don't ...
10
Every time I've done it, we've gone for two passes ...
take a snapshot, and working on a different server, use that to determine what has to be done for the migration, and script it.
once they have the script in hand, the snapshop is restored on the test system, and it's timed to see if it'll run within the required time, or it's tuned and modified until ...
10
If you cannot take the databases offline, tne you need to do backup/restore. I would suggest the following:
Install SQL 2008 on the new box, using the same file structure as the old box for MDF and LDF files.
Take backups of all databases on the old box.
Restore master from the old box to the new box having the SQL started in single user mode. Restore ...
10
You almost have your answer already:
Create the new structure in parallel
Start writing to both structures
Migrate old data to the new structure
Only write and read new structure
Delete old columns
As for step 3, use something like this (in one transaction):
Insert what is not there yet:
INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM ...
10
In my experience, the same decision making process should be made as prior. AFAIK there hasn't been any 'world changers' with SQL Server installation, within the MS SQL Server product in itself, and the potential problems you have when rolling out software with millions of lines of code. Something bad could happen and now you're stuck with no 'ROLLBACK' ...
9
So someone let our SQL server 2008 R2 enterprise trial expire on a production web server
Violation of the EULA.
Is express or compact capable of running a small website?
Both are capable of running a small website. Express is also capable of running medium or large websites. Personally I would go for Express if possible, Express handles concurrent ...
9
The best answer to this question is a bit of a short one, but go look at the books online articles that talk about what features are due to be removed in future versions and are no longer there in various versions.
For instance.. In SQL Server 2008 the "old-style" left outer join syntax (WHERE Table2.col1 *= Table1.Col1) is listed as deprecated (there but ...
8
According to the MySQL 5.0 Certification Study Guide, Chapter 32 Section 32.3.4, Pages 456,457 describe the Conditions for Binary Portability which bring out the following:
Binary portability is important if you
want to take a binary backup that was
made on one machine and use it on
another machine that has a different
architecture. For example, ...
8
One of the easiest ways to use a full backup to prepare, a differential to migrate.
First off, you do a full backup and restore to prepare the files on the new server disks, but use the RESTORE .. WITH NORECOVERY option to allow more restores.
At the time of migration, you do a differential backup/restore. This is far quicker and you use RESTORE .. WITH ...
7
You mention 24/7 operation, so the best way to do this is to create a database mirror on the new system, then you can simply switch over with minimum - perhaps zero, depending on how your application is structured - downtime using a client-side redirect. The basic steps are:
Make sure your primary database is running in FULL recovery mode.
Backup the ...
7
I think backing up the "data directory" is what you want.
Moving the whole directory to a new server should be what you might look into. This will move everything within the cluster. From there, you can drop, move,... your single databases as you need to.
7
I used to move databases almost constantly, due to SAN reconfiguration and migrations.
Assuming that you are moving a whole server at a time, I would go with something like your path #2. (If you are moving one database at a time, and eventually doing every database on a server, that would be more problematic since you would have to be changing paths to the ...
7
If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one). Then you can get the new rows by
SELECT * FROM staging_table
EXCEPT
SELECT * FROM real_table
;
Rows missing from the new CSV can be get reversing the two sides around EXCEPT. However, ...
6
If you have ssh access you can use mysqldump from the command line
If you don't have ssh access but you have phpMyAdmin access you can use it to export/import
If you don't have phpMyAdmin access there are some handy php scripts that will dump and import ( however speaking from my own experience i never found one that is as reliable as phpMyAdmin ).
There ...
6
I'd consider scripting the table out, or use a compare tools (eg Red Gate) to generate the tables in the target database. Without indexes or constraints yet.
Then I'd consider restoring the database with a different name on the same server and doing
INSERT newdb.dbo.newtable SELECT * FROM olddb.dbo.oldtable
.. for each table, with SET IDENTITY INSERT ON ...
6
I think that simplest way is to use that --hex-blob switch on mysqldump and restore by psql, with decode(string text, type text). However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example:
mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \
--compact ...
6
The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring more than 4000 bytes.
By way of example, this query shows you ...
6
The Oracle Administrators Guide says the following:
Use the ALTER TABLE...MODIFY statement to modify an existing column
definition. You can modify column data type, default value, column
constraint, column expression (for virtual columns) and column
encryption.
You can increase the length of an existing column, or decrease it, if
all ...
5
A while ago I wrote a couple of blog posts on loading logfiles into SQLite for analysis. This is an incredibly easy database to use, zero administration, bindings to any language you can think of (e.g. Python and Tcl/Tk as well as exotic ones like OCaml) supports a decent subset of SQL for complex reporting and can handle surprisingly large datasets ...
5
We actually did it using a lot of manual scripting in conjunction with the Import wizard, but this morning I found a better answer, courtesy of Tibor Karaszi's blog article.
Part of our frustration here was that the SQL 2000 "DTS Import/Export Wizard" actually makes this almost trivially easy by selecting "Copy Objects and Data":
This third option is the ...
5
If you just want to move a specific table try:
mysqldump -u username -ppassword databasename tablename > databasename.tablename.sql
You can specify more table names above, in the same command. Once the command completes, move the databasename.tablename.sql file to the other server and then restore using:
mysql -u username -ppassword databasename < ...
5
To be honest, with a low traffic environment running on 5.1, you shouldn't have any problems with MyISAM. The biggest downfall of MyISAM is table-locking WRITES, IMO. But with limited data in the tables, this won't be noticeable.
MyISAM is probably easier to manage from a DBA standpoint with recovery and repair.
And in 5.1, I think InnoDB's benchmarks ...
5
A query in Access is a view in SQL Server. I am not sure whether the typical migration tools will bring those along and change them to views for you.
Be careful with the code you're using in your queries, as functions like FIRST(), IIF(), ISNULL() etc. either don't exist in SQL Server at all, or work differently.
5
Whichever method you use, you'll need to be aware of any restrictions on SQL Server 2008 R2 Express that might be a show-stopper eg 10GB maximum size of any one database, single CPU, memory restrictions etc
Ideally, you'll also have tested how the application works with the new version of SQL Server. If it's a vendor product, do they support the new SQL ...
5
Have you tried with backup and restore? Make a backup in SQL Server 2005 and use it to restore the db to the R2 instance. At the end of the restore it should be upgraded automatically.
Or is it any different for SBS 2003 and can't make a backup?
To be able to transfer also the logins you could get help from the following KB articles:
How to transfer ...
Only top voted, non community-wiki answers of a minimum length are eligible
