To download the admin install go to http://msdn.microsoft.com/en-us/data/hh297027, download the ssdtsetup executeable for SQL Server 2014, and also vs2010, vs2012, vs2013. From the command prompt run ssdtsetup /layout <destination> where <destination> is the path of the USB stick / network / local drive you want to install to.
This may take a while to download the full install files.
If that’s all a bit to hard just smell this cool SSDT feature here http://msdn.microsoft.com/en-us/library/dn266029(v=vs.103).aspx.
The entry point is pretty simple, just right click your database and select Database Comparison.
SSAS: Access Denied – The file specified in the restore command is damaged or is not an AS backup file
You have attempted a database restore in Analysis Services from a .abf file and get the error : “The file specified in the restore command is damaged or is not an AS backup file. The following system error occurred : Access is denied”. The key clue in the error is the last part.
Go to the actual file, i.e. not the folder, right click, select properties, the click the Security tab. Edit the file’s security so that the Analysis Services service account has sufficient permissions i.e. full permissions will work. If you don’t know the Analysis Services Services account, go to SQL Server Configuration Manager and find your Analysis Services service where you will see the service account.
Looking forward to the new features in Windows 8 ? Taking a flying leap but make you flinch at first. If you have no Upgrade Apps option chances are you are Installing the Windows 8.1 Enterprise media which doesn’t support upgrade apps – try the Professional edition media instead. If you are chasing the Hyper V host this is available in both Professional and Enterprise but otherwise you will notice Professional doesn’t get a handful of “Enterprisey” things. Get along to the Features v Editions page to see what you are missing between Enterprise and Professional http://www.microsoft.com/en-us/windows/enterprise/products-and-technologies/windows-8-1/compare/default.aspx.
Even if you are happy to punch out a clean install there is a Windows 8.1 Upgrade Assistant, which will give you an assessment on what is compatible and what isn’t.
Note, if you want the upgrade option, there are several options here, summarised down, these are essentially,
1. Keep Nothing (clean install)
2. Keep Personal Files (make sure your files are in your Desktop or Documents folder)
3. Keep Apps (if you don’t see this try the Professional edition media)
If you get along to here you will find more details about the upgrade process http://windows.microsoft.com/en-au/windows-8/upgrade-from-windows-7-tutorial.
The following workaround http://up10it.blogspot.com.au/2013/07/VS2012-projects-not-loading.html was tested successfully for SQL Server Data Tools for Visual Studio 2012. You come across the error “One or more projects in the solution were not loaded correctly” on opening a SSIS solution file in SQL Server Data Tools for Visual Studio 2012 and then encounter a head scratching moment.
Steps taken to work around :
1. Close SQL Server Data Tools for Visual Studio 2012
2. Navigate to the C:\Users\<Your UserName>\AppData\Local\Microsoft\VisualStudio\11.0
3. Rename folder ComponentModelCache to ComponentModelCache.old
4. Re-open SQL Server Data Tools for Visual Studio 2012
5. Open the Solution again
Migrating your Reporting Services instances to SQL Server 2012/2014 has a few key considerations.
First, the oldest version you can upgrade from is Reporting Services 2005 and that must be patched to SP4 (9.00.5000).
Next, any SQL Server 2005 report data sources are still supported in SQL Server 2012/2014. Here are the full list of data sources supported in SQL Server 2014 http://msdn.microsoft.com/en-us/library/ms159219.aspx. However your Report Server databases themselves can’t be hosted on SQL Server 2005 database instances http://support.microsoft.com/kb/2796721/en-nz.
Importantly, those pesky Report Models are depreciated in Reporting Services 2012/2014 http://msdn.microsoft.com/en-us/library/ms143509.aspx. They will migrate but you won’t be able to make changes after the migration – so at some point they will need to have their datasets rewritten. Getting a handle on how many reports hang off Report Models and giving your customers some options is a good way to manage this issue.
Finally, the full migration process is outlined here step by step http://msdn.microsoft.com/en-us/library/ms143724.aspx. There are risks in any major version migration, but running side by side environments and getting a business acceptance sign-off is the best way to mitigate those risks.
The SSISDB contains a wealth of statistical data for your SSIS package executions and messages. In a package failure scenario, trawling through SQL Management Studio to get detailed reports becomes quite tedious. For a very quick and easy dashboard looking at the most recent events try a simple Reporting Services report with two tables and two datasets. The two datasets are outlined in more detail below.
The first dataset takes the last 3 days worth of execution history for your packages as per the top table. Whilst this dataset returns one row per execution as displayed, adjusting the groupings in this query will give you interesting perspectives on Average Duration and Execution Count.
The second table contains the last 15 messages. In a failure scenario the second dataset will typically hold the most recent failure if no other packages have been run subsequent to the failure. However you may need to adjust the dataset to suit, where TOP is specified, or parameterise as required – add a little Dijon mustard :)
Execution Summary – Last 3 Days
SELECT folder_name, COUNT(*) AS tally_executions, SUM(DATEDIFF(ms, start_time, end_time))/1000 AS total_duration_s, package_name, CASE status WHEN 1 THEN 'Created' WHEN 2 THEN 'Running' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'Failed' WHEN 5 THEN 'Pending' WHEN 6 THEN 'Ended Unexpectedly' WHEN 7 THEN 'Succeeded' WHEN 8 THEN 'Stopping' WHEN 9 THEN 'Completed' END AS Status, start_time FROM catalog.executions AS e WHERE start_time > getdate()-3 GROUP BY folder_name, package_name, status, start_time ORDER BY start_time DESC
Message Detail – Last 15 Messages
SELECT TOP (15) event_message_id, message, package_name, event_name, message_source_name FROM (SELECT event_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name, event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID, message_code FROM catalog.event_messages AS em WHERE (operation_id = (SELECT MAX(execution_id) AS Expr1 FROM catalog.executions)) AND (event_name NOT LIKE '%Validate%')) AS q ORDER BY message_time DESC
Here’s a couple of chilly reads that go a little deeper.