Posted in SQL, SSAS, SSIS, SSRS, Uncategorized

SQL Server Upgrade Caveats

breaking

Microsoft has some useful information regarding breaking changes, and discontinued functionality for those upgrading versions.

I’ve listed on a high level what to watch out for, plus the link to the Microsoft page if you need more info.

Remember that these issues are accumulative. So any issues in 2012 will affect a 2014/2016 upgrade if you’re still on 2008.

Also, 2016 is still in CTP, so things could still change before RTM.

 

Posted in R, SQL, SSAS, SSIS, SSRS

Reasons to wait for SQL Server 2016

download

Preamble

If you’ve got an old implementation of SQL Server that you use for BI and DWH, say 2008 R2, and you’re planning to upgrade soon, you might be tempted to upgrade to SQL Server 2014.

Many IT departments will wait for SP1 to be released before upgrading to a newer version.

2016 RTM is set to be released in the second quarter of 2016, with SP1 out of sight.

Should you just upgrade to 2014 or wait for SP1 of 2016 to be released?

You might consider going through both, but after doing the first upgrade you might lose the will to do the second upgrade – after all you’ve got a lot vying for your time and attention.

The company that I’m working at is currently having this very dilemma.

Features

Here’s a list of features that only come with 2016, that I thought make it worthwhile to wait:

  • Database
    • PolyBase
    • Stretch database – a nice option for archived, relatively inactive data
    • Column store index performance enhancements for Analytical queries
    • Query Store – a new way to troubleshoot performance issues.
    • Row level security.
  • SSIS 
    • Most changes happened in 2012. The only change in 2014 is rich XML validation in the XML Task.
    • Support for Power Query
    • Deployment enhancements
    • Debugging enhancements
    • Package management enhancements
    • Connectivity enhancements e.g. Hadoop, new Excel, SAP BW, and Azure
    • Better design experience
    • Support for R-Services
  • DQS
    • Nothing for 2014 or 2016
  • SSAS
    • Direct Query enhancements for tabular models
    • Tabular Model Scripting Language (TMSL) supported in Management Studio
    • Calculated tables in SSDT
    • Partition management for Tabular models
    • DBCC for Analysis Services
    • New DAX Functions for Tabular mode
    • Parallel processing for multiple table partitions in Tabular models
    • 2014 : “With exception to added functionality supporting Power View Reports against Multidimensional Models, SQL Server 2014 Analysis Services is unchanged from the previous release.”
  • MDS
    • Custom indexes on entities
    • Extend Business Rules with SQL  (Business Rules Extension)
    • The ability to submit changes for approval through  “Change Sets”
    • Scalability and performance enhancements on the server (compressed data etc).
    • Excel Addin loads data faster and can handle larger entities. (15x faster)
    • Transaction log maintenance
    • Enhanced tracing for debugging and troubleshooting.
    • Improved manageability
    • Entity Sync Relationship  – share entity data between models
    • Many-to-Many Relationships in Derived Hierarchies
    • 2014: No enhancements for 2014
  • SSRS
    • New SSRS Web Portal
    • Mobile Report Publisher
    • SQL Server mobile reports hosted in Reporting Services available in Power BI Mobile app
    • Pin Report Items to a Power BI Dashboard
    • PowerPoint Rendering and Export
    • New charts
  • R Services
    • Run R scripts using TSQL
    • Advanced Analytics Extensions – extend Transact-SQL with supported languages such as R, Python, and JSON.

 

Posted in SSIS

Copying SQL Stored SSIS Packages across instances

The problem

 

At the organization that I work at we run all our SSIS Packages from SQL Server (as opposed to the file system).

 

We had a need to copy all our packages from one server to another (From Production to our Disaster Recovery server). If you’ve ever tried this with Management Studio you’ll know that you painstakingly need to copy them one by one.

 

What packages are on my server?

 

The following script can be used to query which packages are on the server:

 

select top 100 percent FolderName = foldername,
PackageName = [name]
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders fld on pkg.folderid = fld.folderid
order by FolderName, PackageName

 

DTUtil

 

The Dtutil command line utility can be used to manage packages.

It can be used to delete or copy the packages that you’re interested in.

You can find all the parameter options at Books Online (http://msdn.microsoft.com/en-us/library/ms162820%28v=SQL.100%29.aspx)

 

Here are the options and arguments that suited me in copying one package:

dtutil /SQL “SourceFolder\SourcePackage” /SOURCES “MyProdServer” /COPY SQL;”TargetFolder\TargetPackage” /DESTS “MyDRPServer” /Quiet

Now that we’ve got the list of packages, and the DTUtil syntax for what we’re trying to do, we can generate our DTUtil commands and run them on the command line.

 

select ‘dtutil /SQL “‘
+ case
when len(FolderName) = 0 then ”
else FolderName + ‘\’
end
+ PackageName + ‘”‘
+ ‘ /SOURCES “ProdServer” /COPY SQL;”‘
+ case
when len(FolderName) = 0 then ”
else FolderName + ‘\’
end
+ PackageName + ‘”‘
+ ‘ /DESTS “DrpServer”‘
+ ‘ /Quiet’
from (
select top 100 percent FolderName = foldername,
PackageName = [name]
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders fld on pkg.folderid = fld.folderid
order by FolderName, PackageName
) pk

 

I’ve run this on my local instance, here’s the output: