Last updated on September 28th, 2022 at 09:33 am
I’m working on a project to clone a SQL Server database (aka the ConfigMgr database) because a customer wants all querying and reporting off-loaded from the main ConfigMgr database server. Instead of using SQL Server Replication we’re going to use SQL Server Integration Services (SSIS) to move a backup of the database from one server to another server. In order to accomplish this task, I will install SSIS on my workstation. I find it useful to have SQL Server Data Tools (SSDT) (SSIS is included within the SSDT tools) on my workstation as it allows me to manage SQL Server, create SSIS packages without having to RDP to the server to launch the tool, and to perform simple edits of my SSIS packages.
SQL Server Data Tools (SSDT) are NOT installed as part of a SQL install anymore, so where can you get SSDT and how is it installed? This blog post will answer those questions.
In order to locate the most current version browse to this page. That’s where you will be able to find the latest download.
Click on the Download SQL Server Data Tools link.
On the SQL Server Data Tools in Visual Studio 2015 page, click on the Download SQL Server Data Tools link. This will download a small exe that you will need to run.
Deselect everything except for SQL Server Database and SQL Server Integration Services, and then click Next.
Select I agree to the license terms and conditions, and then click Install.
Click Yes.
Wait as this might take 20-30 minutes to complete.
Note: When you click on the Restart button your computer will restart, so this is a good reason why you should install the SSDT tools on your workstation instead of on the SQL Server itself.
Click on the Restart button in order to complete the setup.
Licensing
On the download page for SSDT it says (I capitalized the word, “free”):
“SQL Server Data Tools is a modern development tool that you can download for FREE to build SQL Server relational databases, Azure SQL databases, Integration Services packages, Analysis Services data models, and Reporting Services reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.”
This means that you can install SSDT on your workstation without any licensing cost. That’s good to know!
Notes
I always recommend using the latest version no matter what version of SQL Server you are running. Why? The latest version will support all versions of SQL from 2005 to the current release. In my case, I will be creating SSIS packages for both SQL Server 2012 and SQL Server 2016.
One thing to remember, once you install SSDT it will ask you to upgrade what might seem to be every month. If you don’t upgrade you might have issues connecting to the latest version of SQL Server, but it will still connect to earlier versions without any issues.
If you have questions, please feel free to contact me @GarthMJ.