Hot answers tagged

23

The foremost step to do is to run the Upgrade Advisor on SQL 2000 database and address all the issues reported by it. As a best practice, use the Upgrade Advisor tool on your SQL Server 2000 legacy database and import a trace file to the Upgrade Advisor tool for analysis. The trace file lets the Upgrade Advisor detect issues that might not show up in a ...


8

I would prefer having a history schema or a second historical database over a linked server any day. It saves license costs is easier to manage and query. You can then also use simpler schema and drop some of the indexes making the database smaller But since you have the enterprise edition you have the third option which is to partition your tables which, ...


8

I think the answer to many of your questions is that it depends. What performance problems are you having? It seems unusual that a database would have performance problems just from growing to 250GB in size. Perhaps your queries are performing table scans on the entire fact table even when only a small portion (e.g., the last year) of the date range is ...


7

Unless the data you are importing is very simplistic using SSMS (Sql Server Management Studio) tends to produce a lot of errors and not a lot of actual data. I find that if my spreadsheet (and/or csv, txt file) is very large or has large text fields, or date/time fields I almost always end up using SQL Server Integration Services (SSIS). The first time you ...


6

In SMSS you can right click a database and select Tasks -> Import data.


6

In order. Some easy options: You could look at the start->run services.msc and see which services are there; you could run powershell and look at run gsv *SQL*; or you could look at the Start menu->Microsoft SQL Server 2008 R2 and see what's there. If you're a database newbie, I would take a look at the excellent database maintenance scripts from Ola ...


6

Disclaimer : SQL Server 2005 is out of mainstream support. - extended support ends for sp4 on 04/12/2016 Migration always have a downtime. So depending on how much your downtime is, you can go with setting up either : Logshipping ( I have used this method and it reduces the downtime considerably) a. Set up logshipping from the source to destination ...


4

Ignoring license for now as that's not where I spend my time. IMHO, archive database is simplest to implement and maintain. They're distinct, loosely coupled entities. Data movement and load/resource controls have clear boundaries. Can easily move to a different instance or server for better performance management and cost is not a major issue. Note that ...


4

In my experience a second database would be the preferred choice for two reasons. You can restore the data from an historic backup then drop the tables and indexes you don't need. You can move this to a different server for reporting purposes, this has the benefits of not using the resources of the primary server You would still need to delete all the ...


4

I want to know the best and the most convenient way to migrate the whole data includes: - Security and permission - Users and memberships - All of the databases and .. Best method to migrate data and all objects related to particular database is by using backup and restore method.Perhaps since you want to migrate whole database to new instance you can ...


4

You might look at PgFutter to help you out. It sounds like it would fit your use case well, because it creates tables for CSV formatted data based on the header line, and then loads the data into the tables.


4

I would choose Option 2 If you use Option 1 and revoke privileges, you have to put them back. The mysql grant tables are MyISAM. Should any crash, human error, or other unexpected event corrupt the tables, you have a mess to clean up. Only those with SUPER privilege can perform writes when read_only is enabled. SUPER is not a database-level grant. With ...


4

The columns in the connection manager that your datasource is using need to be defined as "Unicode String (DT_WSTR)".


4

By reading the official documentation, I'll say that your guess is right, the dump script will override the DB1 database. MySQL Use db syntax The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement ...


3

And to get your columns to DT_WSTR as liam.confrey mentions, you will want to click on your flat file connection manager and redefine each column type from string [DT_STR] to Unicode string [DT_WSTR] Do note that if you misclick like I usually manage to do and select something like 'two-byte unsigned integer [DTUI2]', classic off by one, the ...


3

Things you are likely to need to do include, preferably before you move any data: Normalize Add constraints (keys, domain checking, referential integrity, validation, etc). Both are fairly huge topics :)


3

Sometimes, the lack of an uint type in SQL Server can come in handy. If Order-id is defined as identity(1,1) in DB2, you likely do not have any negative Order-ids. Consider importing all the legacy DB1..Orders.Order-id fields as the same number, but multiplied by -1. If there's a FK to Order-id in DB1, you would use the same multiple-by-negative-one ...


3

The first thing you need to identify in your db are which tables require all their data (the seed data, sometimes called "domain" data), and which tables are suitable for bringing only recent data. You have a couple choices then. 1: Use a tool like SSDT to deploy your SCHEMA to dev & staging, keep the domain data as a bunch of INSERT statements in SSDT ...


3

The question suggests that the tool is being used to migrate data and not schema, which implies that you are referring to SQL Data Compare and not SQL Compare. In which case, yes, indexes on the comparison key columns will speed on SQL Data Compare. I've consulted the SQL Data Compare project manager, who provides the following insight: "Usually ...


3

Since you mentioned you want to copy table data only I guess SSIS would be perfect. I have been using it and it works just fine for me. It also depends on level of competency you hold with SSIS. You can go for backup restore but it would also restore tables whoes data you dont want to change. If you want complete refresh then of course no better option than ...


2

Although the representation of the data differs between the old and new schema the data itself should be the same. This means a series of reconciliation queries will provide certainty that the migration was without error. Say you have an Orders table. The total number of Orders per year, month or day will be the same in the old and new databases. ...


2

Upgrading a major version of PostgreSQL (i.e. 8.3 to 8.4) requires a database dump and restore - simply copying in data from an older version will most likely not work. There are still some copies of 8.3 that you can download from EnterpriseDB to get you going. However it would still be best to upgrade to the later version afterwards if possible.


2

The simplest approach is to simply use pg_dumpall -f cluster.dump, then restore with psql -f cluster.dump on the new host. Slightly more sophisticated is to dump globals, then dump each DB separately: pg_dumpall --globals-only > globals.sql psql -qAt -c 'SELECT datname FROM pg_database WHERE NOT datistemplate;' -0 |\ xargs -0 -i pg_dump -Fc -f ...


2

I would do something like: pg_dump -h localhost -W -U postgres --clean -f dbname.dump DBName The move the file to the new server, create the new database with createdb DBName then restore with: psql -d DBName -f dbname.dump -v ON_ERROR_STOP=1 Remember that if you have tablespaces created on the old DB you will have to manually recreate them in the ...


2

In reality, you could just script it yourself using mysqldump. Here my posts using the --where option for mysqldump: Aug 15, 2011 : Is it possible to mysqldump a subset of a database required to reproduce a query? Mar 14, 2012 : Can I mysqldump an entire database while using where='condition' on individual tables? Jul 15, 2013 : Export just rows ...


2

Z ON DBNAME_NODE0000.out instructs the DB2 command line processor to redirect all output to a file named DBNAME_NODE0000.out in the current directory. Apparently, you don't have permissions to create files in that directory.


2

Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time: 1) Backup and Restore Databases - Is this the best option? It's an option. If you need the downtime to be less, you ...


2

As long as all the changes happen only on your first instance, transactional replication is what you are looking for. It was designed for this purpose and usually operates close to realtime. You can find an introduction to replication here: http://www.sqlservercentral.com/stairway/72401/ If you need to be able to apply changes on both machines, merge ...


2

Aside from using sed to remove the USE db1 statement, if you can take a new dump of the database: mysqldump db1 > newdump.sql should generate the dump without the USE db1 statement.


2

Below steps will help you : Create full backup with compression Ship the compressed backup to the new data center Configure database mirroring in async mode When you are ready for failover, change the mirroring mode to sync mode. Initiate failover to secondary (new data center) alter database db_name set partner failover change the web.configs for your ...



Only top voted, non community-wiki answers of a minimum length are eligible