April 19, 2010 at 1:51 pm
Hello,
I'm just getting into the BI side of SQL Server. I've been asked to develop some reports and since I'm also considered a DBA I want to make sure all the basis are covered. So here's a list of things I wanted to ensure were set up using best practices for SSAS (SSRS as well, but I will post to the SSRS forum):
1. Deployment from DEV/Test to Production
2. Backup/Restore
3. Mirror/Clustering
As far as I can tell here are the best practices. It would be great if someone can confirm that I'm on the right track or if there are better methods.
1. Deployment - use synchronize DB wizard (i'm not going to be able to learn Xml script so fast); the question is when i deploy a cube from BIDS it creates the multidimension database right, so there is not physical file like a .cube??
2. Backup/Restore - backups use some special xml formatting, but if you use SSMS it works pretty much just like regular user database backup and restores.
3. Mirror/Clustering -set up the cluster like normal making the SSAS a clustered resource;
can you mirror an SSAS database? if so if you went to the mirrored instance, you would need what ever jobs that rebuilds the cube/database on that server ?
Does this make sense, am I missing anything big here?
Thanks in Advance - I really appreciate the sqlservercentral community !
April 22, 2010 at 5:49 am
Helo Natascha,
Welcome to the BI-world, hopefully you have a lot of fun and learn a lot. Zbout your questions:
1. When you deploy a cube from BIDS it creates a multidimension database AND a lot of data files in an NT folder. The location of the folder cabn be specified in the properties of the Analysis Server.
2. You're right on backup/restore, it's similar to regular (sql) databases.
3. About the mirroring, why would you do that. Your SSAS database is NOT an operational database and does not require advanced fail over mechansims. If you want to duplicate the cube simply make a (scheduled) back-up on 1 server and a (scheduled) retsore an another.
Hope this helps and good luck in finding all the other Big issues 🙂
Cees
April 28, 2010 at 2:04 pm
cees van diermen (4/22/2010)
Helo Natascha,Welcome to the BI-world, hopefully you have a lot of fun and learn a lot. Zbout your questions:
1. When you deploy a cube from BIDS it creates a multidimension database AND a lot of data files in an NT folder. The location of the folder cabn be specified in the properties of the Analysis Server.
2. You're right on backup/restore, it's similar to regular (sql) databases.
3. About the mirroring, why would you do that. Your SSAS database is NOT an operational database and does not require advanced fail over mechansims. If you want to duplicate the cube simply make a (scheduled) back-up on 1 server and a (scheduled) retsore an another.
Hope this helps and good luck in finding all the other Big issues 🙂
Cees
thannks Cees,
I've been stuck on some cube development, so i am just getting back to this. unfortunately in my case i'm playing test developer and test admin roles.
It seems like points 1 & 3 are related - when i'm ready to deploy the cubes, i can simply backup the database and restore it to another server? What about the sychronization tool? how are these similar or different?
I guess you are right about the whole mirroring scenerio. I was thinkng in a complete DR solution, but if you got the backups you can simply restore them when needed. But you said that the cube creates a bunch of files on the file system. I'm guessing when you do either a sychronization or a backup/restore you don't have to worry about the files, etc...
May 2, 2010 at 3:04 am
Hello natarschia,
Indeed BackUp/Restore and Synchronization are alike, however Synchronization requires that the NT User account used in the Synchronization has (Analysis Services) administrative rights both to source and destination server. If it can't be setup like this (company rules), you'd better use Backup/Restore.
On doing backup/restores (and synchronization) you indeed don't have to worry about the files (in the filesystem), assuming there's enough diskspace.
r,
Cees
January 28, 2011 at 2:58 pm
BTW, I have some scripts I use to automate SSAS-db restores from production to the D/R server as a nightly job. I use powershell with SQL Server 2008 AMO. See this link for more info:
I can post the scripts I am using here if anyone is interested.
With regards to mirroring, would it make sense to mirror the relational data warehouse to the D/R site?
I would think it makes more sense to do backups/restores of that as well, just as in the case of the SSAS databases.
Any thoughts?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply