You maintain several databases on a 64-bit Microsoft SQL 2005 instance.
You need to migrate the databases to SQL 2012 on the same server. You also need to ensure functionality in SQL 2012 can be used in the database after migration.
What should you do? (Each correct answer presents part of the solution.)
A . Perform an in-place upgrade to 64-bit in SQL 2012
B . Perform a side by side installation of a 64-bit SQL 2012
C . Perform a side by side installation of a 32-bit SQL 2012
D . Detach the old instance and attach it to the new one
E . Change the compatibility level of the database
Answer: B,D,E
Explanation:
B: In a side-by-side upgrade, SQL Server 2012 installs either along with SQL Server 2008 (or 2005) as a separate instance or on a different server. This process is essentially a new installation followed by a database migration.
D: You can use detach and attach operations to upgrade a SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 database in SQL Server 2012. After being attached to SQL Server 2012, the database is available immediately and is automatically upgraded.
E: One important factor to contemplate during an upgrade and migration effort are your database compatibility levels, which control certain database behaviors to be compatible with a particular version of SQL Server. Unlike the database file version (which get changed when you restore a database backup in a newer version or attach an older database to a newer version of SQL Server), the database compatibility level can be changed back and forth to different values.
References:
https://logicalread.com/sql-server-2012-side-by-side-upgrade-w02/#.WuiWdYhubIU
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms189625(v=sql.110)
https://www.sqlskills.com/blogs/glenn/upgrading-sql-server-database-compatibility-levels/