none
Synchronize SSAS databases on same server?

    Question

  • I am looking for the easiest way to synchronize my work SSAS database with my production database.  I was attempting to use the database sync wizard but it doesn't appear to allow you to sync one database on a server to another on the same server.  Is this possible?  If not, what would be the most optimal solution for time savings?  

    Essentially I want to sync my work db with my production db on the same sever.  The only differences are the names of the databases and the changes I make to the work db that I want to sync with production.

    Thanks!

    Thursday, July 18, 2013 7:43 PM

Answers

  • Confirmed. As we speak, I have written numerous queries unaffected by the fact that this cube db is being restored. I am trying it on one of my smaller dev databases.

    I challenge you to give it a try.


    My Blog: http://ditchiecubeblog.wordpress.com/


    Friday, July 19, 2013 1:08 PM

All replies

  • I have found good old backup and restore to be the best option. It guarantees 100 % up time when releasing changes to production.

    Process your work cube fully, backup and restore over live.

    I haven't found a better option, but I will be interested to see if anyone else has a better suggestion.


    My Blog: http://ditchiecubeblog.wordpress.com/

    Thursday, July 18, 2013 7:53 PM
  • From looking, this seems to be my best option but doesn't a restore of the database prevent user queries?  While this isn't a deal breaker it would certainly make rolling things out a little more tricky.
    Thursday, July 18, 2013 7:58 PM
  • Nope it does not prevent user queries.

    It seems to do the work in a separate area and then commit.

    I use this approach all the time on a very large cube that takes 4 hours to restore.


    My Blog: http://ditchiecubeblog.wordpress.com/

    Thursday, July 18, 2013 7:59 PM
  • Hi Rizzle,

    I recommend you refer to the following article:  
    Analysis Services Synchronization Best Practices: http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/03/16/analysis-services-synchronization-best-practices.aspx

    When administering your enterprise Analysis Services environment, it is generally a best practice to create separate querying, processing, and synchronization windows to ensure that these processes do not interfere with each other.

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Friday, July 19, 2013 7:45 AM
  • Thanks Elvis.  I actually read that article just before posting here :).  That is also why I assumed that a restore would block queries as the article states " This mechanism of locks ensures the integrity of the database as its being restored. At the same time, this also means that the database cannot be queried during the restore. ".

    At any rate, without a separate server to allow for database synchronization I believe the backup/restore method will be the best option for us.  I just think it's odd that SSAS has the ability to sync databases between servers but not databases on the same server.  

    Friday, July 19, 2013 12:30 PM
  • That is a good article, but I can confirm that I have queried the same database that I am restoring over during the restore, and my users have not complained to me either. I will try it again now and confirm.


    My Blog: http://ditchiecubeblog.wordpress.com/

    Friday, July 19, 2013 12:42 PM
  • Confirmed. As we speak, I have written numerous queries unaffected by the fact that this cube db is being restored. I am trying it on one of my smaller dev databases.

    I challenge you to give it a try.


    My Blog: http://ditchiecubeblog.wordpress.com/


    Friday, July 19, 2013 1:08 PM
  • Thanks for confirming Duane, that will make things much smoother!
    Friday, July 19, 2013 1:09 PM
  • No worries, I had to confirm for myself too as this is the approach that I currently use and I need to know for sure that it isn't going to cause problems.

    So far I have been going for a few minutes on different queries each time, with no problems.


    My Blog: http://ditchiecubeblog.wordpress.com/

    Friday, July 19, 2013 1:12 PM