How can I deploy a SQL Server 2000 Analysis Services database from my development environment to my test, staging, QA, or production environment? I need to keep my production and development environments synchronized.
SQL Server 2000 Analysis Services has two deployment mechanisms: copy and archive-and-restore. To copy, register your development and your test, staging, QA, or production servers in Analysis Manager, then copy an entire database or selected database objects from one server to another. After copying, process the appropriate objects on the destination server. To archive and restore, create a copy of the database without any data, then archive the database on the development environment and restore it to the QA or production server. This latter approach also lets you version the .cab files in a source-control system such as Visual SourceSafe.
In SQL Server 2005 Analysis Services, you'll develop your analytical application by using Business Intelligence Development Studio, a Microsoft Visual Studio 2005 based development environment. Business Intelligence Development Studio is integrated with source-control systems (such as Visual SourceSafe). Business Intelligence Development Studio lets you version the generated XMLA script in the source-control system. After development, you'll deploy your analytical application by using an easily configurable XMLA script, which can automatically process the deployed objects. Furthermore, Business Intelligence Development Studio supports incremental deployment and processing of changes from the development environment to a QA or production environment without overwriting security or partition settings. You can also use SQL Server Management Studio to create an XMLA script of an existing Analysis Services 2005 database, execute that script against another Analysis Services instance to create a copy of that database, then process the database.
