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 ...
No, not for any significant and practical amount of code anyway. You can try to hew to standards (e.g., use COALESCE rather than ISNULL) but there are too many differences, big and small. Off the top of my head:
SQL Server supports double quotes and square brackets for identifiers; MySQL uses backticks.
SQL Server supports TOP, most other databases use ...
You can follow any method below :
Note: If you are using any new features like new data types, etc then you have to test out as it will throw errors.
METHOD 1: Using Native Tools
Script out the database SCHEMA_ONLY and recreate an empty database on the destination server. Below are the screenshots :
Use BCP OUT and BULK INSERT to insert data.
Below is ...
There are ANSI SQL standards, see for example the part on Interoperability and standardization in the Wikipedia article. Problem is, few actually follow these standards, which are often written and created post fact, when years of history has already tied the hands of various database products to do things differently.
Not all is lost, however. With modest ...
The foremost step to do is to run the Upgrade Advisor on SQL Server 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 ...
Should I be concerned?
Well, there are a couple of things here that are a little concerning.
First: while it is true that a UNIQUEIDENTIFIER (i.e. Guid) is a 16-byte binary value, it is also true that:
All data can be stored in binary form (e.g. INT could be stored in BINARY(4), DATETIME can be stored in BINARY(8), etc), hence #2 ↴
There is probably a ...
Straight backup and restore is obviously out. I also wouldn't consider replication of any kind.
Database mirroring is relatively simple to set up, but requires real-time connectivity between the two servers, setting up of partners and endpoints, etc. Availability Groups could be an option, but on top of the networking complications you also have to have ...
No, there is no workaround to upgrading a database directly from 2000 to 2012.
Since you don't have a ton of data, you can do all kinds of things to move the data (but not the database as a whole), including:
Manual queries using a linked server from 2012 or an application
However these will not necessarily bring over other ...
Create a sequence and use it as the default value for the column:
create sequence measures_measure_id_seq
owned by measures.measure_id;
alter table measures
alter column measure_id set default nextval('measures_measure_id_seq');
That essentially what serial does.
See the manual for details:
I recently migrated 15tb across 6 databases using mirroring. Very simple and worked perfectly with just a couple seconds of failover time.
I had two new virtualized SQL Servers. The databases were coming from 3 servers that they had just plain outgrown, and were impacting performance on the smaller databases hosted on them.
The process was very ...
SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find references and intelli-sense as well as the ability to compile a project into a dacpac and then deploy that dacpac to a database.
The SSDT way (and redgate sql compare ...
The 'mysql' database contains users/privileges/passwords. So take the dump of mysql database along with other databases
mysqldump [options] --all-databases > all_databases_dump.sql
mysqldump -u root -p mysql user > user_table_dump.sql
These mysql database tables contain grant information
user: User accounts, global privileges, and other non-...
User Aaron Bertrand made some comments that align well with my thoughts on your question. This is more of a frame challenge than an answer to your specific question, but I think it's valuable to consider in this context.
Portability is a nice textbook goal, but it rarely happens in practice.
If you have to change platforms at some point, there will be ...
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' ...
Pay attention that the correct time zone (UTC in your case) is applied during the conversion. If you are not explicit about this, the time zone of the current session is assumed - typically not UTC.
ALTER TABLE tbl ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC';
Check a possible column default for sanity, too. Any expression working ...
This is the solution I came up with:
Enable xp_cmdshell with
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1
If needed create a directory with xp_cmdshell to get the needed permissions.
EXEC master..xp_cmdshell 'mkdir C:\exportdir'
Use BCP with queryout
EXEC master..xp_cmdshell 'BCP "...
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
You can increase the length of an existing column, or decrease it, if
all existing data satisfies ...
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 ...
This isn't my query, I'm translating a system prototyped in MySQL to PostgreSQL. This query does work in MySql.
Does that constitute a bug in MySql, or a shortcoming of PostgreSQL, or just a difference of interpretation?
More like all of the above ;)
Does that constitute a bug in MySql?
It's a "feature" that many consider a bug. Mainly because it can ...
Database Mirroring (SQL Server 2005+) would allow as little as 20 seconds of downtime. This is likely the fastest way of moving a database from machine A to machine B.
You can setup mirroring during the day with no downtime, then cutover at the time of your choosing. As long as the clients are programmed to retry whatever operation they are doing at the ...
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 ...
since you are using SQL Server 2008 Web edition, your best bet is to use LOG SHIPPING.
Your database size is 100GB, so initializing the secondary from a backup should be considered. Dont use GUI to set up initial logshipping.
SO your steps should be :
Backup 2008 Web edition database and restore it on 2012 enterprise WITH NORECOVERY.
Launch Logshipping ...
Have you looked under Advanced Security Settings?
The arrows point to the modifications I've often had to specifically apply when installing SQL Server on Windows 2012 R2.
For the SQL Server Root Directory, I'll give full control to the SQL Server Process account.
Related official documentation:
Configure File System Permissions for Database Engine ...
Yes this will work. You can always go forward, you can't go backwards, at least not with backup and restore. With older version eg SQL 2000, SQL 2005 sometimes you have to do a stepped upgrade, ie go from 2000 to 2008 to 2014, but that shouldn't be a problem for you.
Be aware when you restore your databases they will have the older database compatibility ...
I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp will be prompted with the following questions:
Enter the file storage type of field FileData [image]:
Enter prefix-length of field FileData :
Enter length of ...
There is SET FIPS_FLAGGER 'FULL'.
This prints out a warning for non standard SQL - but some caveats are
I am unsure what specific standard this uses (and suspect it may be SQL 92)
From a quick test this doesn't complain about use of the + operator for string concatenation or proprietary functions such as GETDATE() so it doesn't seem very ...
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 ...
RDS for MySQL 5.6 supports "in" and "out" migration strategies of live workloads using MySQL's native replication, but RDS for MySQL 5.5, they only support "in" migration... which is unfortunate, because essentially every possible combination except the one that you need is available. That would have been nice. :(
If your dump download took 3 hours and ...