• May 30, 2023
    SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com
    SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com
    DBASQL ServerSQL Server Analysis Services

    How to Synchronize Analysis Services Database Using Synchronize Database Wizard in SSMS

    Chetna Bhalla
    Add comment
    104,139 views
    4 min read

    In this article we will take a look at how to Synchronize Analysis Services Database using Synchronize Database Wizard to make two analysis services databases identical by copying the metadata and data between source and the destination SSAS database. This feature is available in SQL Server 2005 and higher versions of SQL Server.

    During the synchronization process, the users can continue to query the Destination SSAS database. Once the synchronization is completed the analysis services will automatically switch the users to the newly synchronized data and the metadata. Finally, analysis services will drop the old data from the destination analysis services database.

    Best Practice for Synchronizing Analysis Services Database

    • If you have a very large Analysis Services Database i.e., greater than 10 GB then synchronization will be faster if you drop the destination SSAS database and then synchronize the SSAS database between source and destination.
    • Generate XMLA script which can be used synchronize the SSAS database between source and destination.
    • If the synchronization process is taking long time to complete then it is recommended to Use Analysis Services Database Backup and Restore Method or Use SSAS Detach and Attach Method (Detach the SSAS DB, Copy the files to the new server and then attach the SSAS Database).

    How to Use Synchronize SSAS Database Using Database Wizard in SSMS

    Step 1: Open SQL Server Management Studio and connect to the Analysis Services Instance where Destination SSAS Database is hosted.

    Step 2: In Object Explorer, right click Databases Folder as shown in the snippet below and then choose Synchronize… from the drop down.

    How to Synchronize Analysis Services Database in SQL Server

    Step 3: The Synchronize Database Wizard Synchronizes two Analysis Services databases located on different servers. The wizard copies changes to data and Meta data from a source analysis server to a destination server. Using this wizard you can also replace the security settings on the destination server with those on the source server. In Synchronize Database Wizard screen click Next to continue with the synchronization.

    Synchronize Database Wizard to Synchronize Analysis Services Database

    Step 4: In Select Database to Synchronize screen you need to choose the Source Server and the Source SSAS Database from where the data and the Meta data will be replaced on the destination server. Click Next to continue with the Synchronization.

    Select Source Database to Synchronize

    Trending Analysis Services Articles and Tips

    Step 5: In Specify Locations for Local Partitions Wizard screen, you can change the measure group partitions to different drives if desired. Click Next to continue with the Synchronization Database Wizard.

    Specify Locations for Local Partitions

    Click the below Number Buttons to continue reading the rest of the article.

    1 2

    Chetna Bhalla

    LESS ME MORE WE

    Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

    View all posts

    You may also like

    Add comment

    Newsletter Signup! Join 15,000+ Professionals




    Be Social! Like & Follow Us

    Follow us

    Don't be shy, get in touch. We love meeting interesting people and making new friends.

    Advertisement