I have had lots of problems with installing SQL Server Business Intelligence Data Tools and decided to write instructions on it also at one point. But this isn't finished as I need help first: I am not able to install 64 bit version and perhaps because of that I am getting into problems.
Should I be able to install 64 bit version as MS/you have claimed or not?
Installing SSAS/SSDT/SSDTBI and its prerequisites Visual Studio 2013 and Microsoft SQL Server 2014
MS SSAS = Microsoft SQL Server Analysis Services, so this is the component in MS SQL server that does BI analysis work. If you have SQL Server 2012, you need to install this separately. With server 2014 this comes in the same package as everything else.
SSDT, SSDTBI = SQL Server Business Intelligence Data Tools, so this is the component in Visual Studio that you need to develop for SSAS.
Especially on free versions there are many places where you can get wrong, so I documented on how I should have done it.
Find out what versions you need
- Visual Studio 2013 does not work with MS SQL SSIS 2012. So if you have that server version, you need to install older version of Visual Studio.
- Reading Microsoft texts, it appeared to me that it is possible to mix Visual Studio 2012, MS SQL 2014 with SSAS all together for free. I was going to read later on as to if it is for evaluation only or for production development as well. But since my work was evaluation I haven’t checked that yet. And since they don’t seem to work together, you don’t need to either.
- Problem with the free setup was that SSAS module did not install to free SQL server (free = “Express Advanced” version), so if you are interested of analytics this won’t work. If not, then it does work.
- Next step on price ladder is to use MS SQL virtual instance, which are very cheap especially for development purposes as may save a lot of deployment time and will help for team co-operation or even just as a backup in case the employee gets sick or leaves the company. I didn’t test this though, so this is what it should do.
- If it is not clear if your project will continue or not, then the other option for MS SQL is to 180 days Evaluation version. At one point I got 30 day version, but when I updated the license it went to 180 days. At least one document says that it would be 90 days. But my end result was 180 days.
- For Visual Studio the cheapest option is Express version, which is free. It is available only as 32 bit instance, but you need to download Desktop version as the default version is for mobile phone (and Windows 8 desktop) development. Except for reinstallation issues, this worked well for me.
- I recommend you to download 64 bit versions on everything that you can: they are faster and they have fewer issues to solve.
- If you will utilize Excel BI / Power Pivot with direct database connection for visualization, you will have fewer issues if you have those components as 64 bit also: Office, B/Power Pivot, ODBC & DB driver.
- Decide if you will utilize virtual machine or not. Virtual machine has couple of benefits: it is easy to scratch and to start all over and it is easy to copy and distribute to all of the team members. Make sure that you have permission to update your BIOS settings as typically virtual machines are disabled on BIOS level on enterprise computers.
- Ensure that your computer has enough of memory. Both standalone installation and virtual machines require 6 GB memory on minimum. With 4 GB you can’t run Word, Excel, PowerPoint, MS SQL, Visual Studio and browser at the same time.
- I used the following installation order:
- Operating system service pack, reboot
- MS SQL server, reboot before that if required, reboot
- MS SQL server service pack
10) Visual Studio for Windows Desktop, reboot
11) If you need analysis services, then MS SQL Server 2008R2 does not support it, 2012 has it is a separate download and on 2014 it comes in the normal setup.
12) SSDTBI – MS SQL Analysis additions to Visual Studio.
Now this will look strange: You have just installed SQL server 2014 instance and now the only good option is to reinstall SQL server instance again!
- Go to options and change your installation to 64 bit if you are able (I was not).
- Start the installation and on step Installation Type, you can try to define that you will make an upgrade instead of fresh installation.
- If your 2014 à 2014 with BI upgrade failed as mine did, then install new 32 bit instances instead as that seems to be the only functional option.
- If you get error “Cannot find registry key …”, error code 0x84BB0001, then you need to google for what to do: there are several options on it. You can try also to do nothing, but for me it didn’t work well enough.
Uninstallation and reinstall
Hardest part, if you end up doing it, is uninstallation. To do that, you should first filter in Control Panel uninstall view according the date so that it shows all components you have installed on the same day as when you installed Visual Studio, MS SQL or SSAS/BI. Two first ones have a lot of components and without this you are likely to miss uninstalling some of those.
Naturally you need to have a backup of your databases. Couple of things you need to know when you revert the backup to new empty database:
- By default you don’t have permissions to read your backup files from MS SQL tools. I moved them in Windows Explorer from old instance to the new instance.
- There is a flag which you need to enable in order to create new database on restore operation.
- Remove C:\Users\<user id>\AppData\Local\Temp\*
- If you do the uninstallation using the setup program, Microsoft recommends you to start it with Administrator privileges even if you are on administrator. Although on my case (Win 7) this happened automatically, so it depends on which Windows you use. This might have worked better, but I used Control Panel.
- Go to Control Panel\Programs\Programs and Features
- If you installed these three on the same day, you can identify MS SQL components from the fact that components name includes either “SQL” or “SSDT” word. It is best to start from latest versions and finish on earliest versions as there are often dependencies.
- Although as SSDT BI component ties up Visual Studio and MS SQL SSAS together and this component is the weakest link, I always did uninstall on all of these at the same time as they were all installed locally and I wasn’t optimistic enough to do otherwise.
- Start your uninstallation from those files with a proper icon. And then go to those that have “setup” in the name or have a setup icon. I ignored over 90% of the warnings I got, but this depends on your setup what you can ignore and what you can’t.
- If you have major issues with reinstallation, then take a zip file backup from from C:\Users\<your id>\AppData\Local\Microsoft and C:\Users\<your id>\AppData\Roaming\Microsoft directories. It is better not to take backups from higher directory level as these folders can be very large and you may have issues with sufficient permissions. If you have permission issues on this level also, you can use copying or a zipping software or take the backup of only the directories you will remove.
- If you have major issues with reinstallation, then remove manually following directories from them: VisualStudio, VisualStudio Services, VSCommon, WD*, SQL Server *, Microsoft SQL Server, MMC (Thanks to Samuel Jones for this info).
10) Now you can reinstall what you needed.
11) MS SQL reinstallation:
- I suggest that you will use different instance name for MS SQL.
- If the installation says as a sub note in Instance Configuration that you already have SSDTBI, then cancel installation, remove registry data on it and then try again. Or MS SQL 2008, 2012 and 2014 registry folders fully as I did.
12) Visual Studio reinstallation:
- If the Studio has upgrade option available, you need to remove “Visual Studio”, WDExpress”, “VS”, “VSCommon”, “VSTA*” and “Web Tools” folders from registry. Note that you take them from sufficiently high level.
- After that do uninstall using setup (naturally you should try this in the first time, but for me it didn’t work).
- If your license that doesn’t have an expiration day has expired, log in to MSDN and refresh the license from there.
- See the same instructions as for clean installation.
- Find out what versions you need
Thank you for sharing such useful information. It will help for the users who want to install Microsoft SQL Server Data Tools_Business Intelligence for Visual Studio 2013. Are there any problems in your installation? If there are some problems, please post the detail error message, so that you can get more help. Please change the type to discussion if there are no any problems.
TechNet Community Support