You administer a SQL Server 2000 server.
The SQL Server 2000 server hosts a SQL Server 2000 relational data warehouse and a SQL Server 2000 Analysis Services database (OLAP database).
You plan to migrate to a new SQL Server 2008 server in a new untrusted domain. You need to ensure that both the relational data warehouse and the OLAP database are migrated in the minimum possible time.
What should you do?
A.
Use the Copy Database Wizard to migrate the relational data warehouse.
Use the Migration Wizard to migrate the OLAP database and process the OLAP database.
B.
Use the Copy Database Wizard to migrate the relational data warehouse.
Use the Migration Wizard to migrate the OLAP database and do not process the OLAP database.
C.
Perform a detach and attach of the relational data warehouse files from SQL Server 2000 to SQL Server 2008.
Use the Migration Wizard to migrate the OLAP database and process the OLAP database.
D.
Perform a detach and attach of the relational data warehouse files from SQL Server 2000 to SQL Server 2008.
Use the Migration Wizard to migrate the OLAP database and do not process the OLAP database.
Explanation:
Tip: DW and OLAP migration = "detach/attach … process"http://msdn.microsoft.com/en-us/library/ms190794.aspx
Detaching and Attaching Databases
The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.
Note: The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, attach works across 32-bit and 64-bit environments. A database detached from a server instance running in one environment can be attached on a server instance that runs in another environment.
In SQL Server 2008, you can use detach and attach operations to upgrade a user database from SQL Server 2000 or SQL Server 2005. However, the following restrictions apply:
Copies of the master, model or msdb database created using SQL Server 2000 or SQL Server 2005 cannot be attached.http://technet.microsoft.com/en-us/library/ms143409.aspx
http://technet.microsoft.com/en-us/library/ms174860.aspx
Migrating Existing Analysis Services Databases
You can use the Analysis Services Migration Wizard to upgrade Microsoft SQL Server 2000 Analysis Services databases to Microsoft SQL Server 2008 Analysis Services. During migration, the wizard copies SQL Server 2000 Analysis Services database objects and then re-creates them on an instance of SQL Server 2008 Analysis Services. The source databases are left intact and are not modified. After you verify that the new databases are fully operational, you can manually delete the old databases.
As a best practice, you should migrate your databases one at a time, or in small batches. This will allow you to verify that each database object appears as expected on the destination server, before you migrate additional objects. When you use the Migration Wizard, the MSSQLServerOLAPService service must be running on both the source and the destination server.
Using the Migration Wizard
You can start the Migration Wizard from an Analysis Services server node in the Object Browser in SQL Server Management Studio. You can also start the wizard at the command prompt, by running the program MigrationWizard.exe
After you migrate a database, you must process the database from the original data source before you can query the database.
As an administrator, you keep the Microsoft SQL Server Analysis Services objects in the production databases current by processing them. Processing is the step, or series of steps, that populate Analysis Services objects with data from relational data sources. Processing is different depending on the type of object and the selection of processing options.
While the processing job is working, the affected Analysis Services objects can be accessed for querying. The processing job works inside a transaction and the transaction can be committed or rolled back. If the processing job fails, the transaction is rolled back. If the processing job succeeds, an exclusive lock is put on the object when changes are being committed, which means the object is temporarily unavailable for query or processing. During the commit phase of the transaction, queries can still be sent to the object, but they will be queued until the commit is completed. For more information about locking and unlocking during processing, see Locking and Unlocking Databases (XMLA).