Sql-server – Can a 2008 SSIS package Connect to a Newer SQL Instance

I would slipstream Service Pack 2 (and perhaps Cumulative Update 2 as well) into the R2 installer prior to running the upgrade, especially if you have enabled the extended partition support. This guide walks you through slipstreaming (it talks about a different service pack and CU, but the process is the same). Slipstreaming will save you a bunch of time and service restarts, since you should be installing the latest service pack at least and not be leaving your new instance at RTM (it is no longer a maintained branch).

You shouldn't have to change SSIS packages.

If you can afford a side-by-side upgrade (e.g. you have another machine or adequate resources for another instance on the current machine, and can re-point your apps later), it will be slightly safer and require less downtime than an in-place upgrade (though it will be more work). You can install another instance, get your logins and jobs set up, then mirror or log ship your user databases to the new instances. When you've ready, you can fail over, take the old databases offline, then the new instance is the primary and you just need to re-point your apps.

Personally, I haven't had any problems with in-place upgrades, but I would probably opt against them if possible in a mission critical environment. The main point is if you do side-by-side and something goes wrong, you can take the new instance down and rebuild from scratch, and it doesn't affect your production system. If something goes wrong in the middle of an in-place upgrade, then you'll be in scramble mode... do we have current backups? Where do we restore them?

In either case, you'll want to make sure you take proper backups before the upgrade.

You will want to update statistics once the upgrade is complete, and if you're on Enterprise Edition and using data compression on tables with nvarchar columns, you may want to rebuild those indexes, since one of 2008 R2's primary advantages over 2008 in the OLTP world is Unicode compression. I wrote a few blog posts about this:

• https://sqlblog.org/2009/08/11/sql-server-2008-r2-a-quick-experiment-in-unicode-compression

• https://sqlblog.org/2009/08/23/sql-server-2008-r2-digging-deeper-into-unicode-compression

• https://sqlblog.org/2009/10/04/more-testing-of-unicode-compression-in-sql-server-2008-r2

I would suggest reading up on SSIS at MSDN: Initial Installation (Integration Services) -this references having a standalone installat of just SSIS.

Quote from above link:

SQL Server Integration Services is installed through the SQL Server 2008 Setup program. You can install Integration Services alongside other SQL Server components or you can install Integration Services as a stand-alone component. Integration Services includes both client and server applications.

To use a dedicated server for extraction, transformation, and loading (ETL) processes, we recommend that you install a local instance of the SQL Server Database Engine when you install Integration Services. Integration Services typically stores packages in an instance of the Database Engine and relies on SQL Server Agent for scheduling those packages. If the ETL server does not have an instance of the Database Engine, you will have to schedule or run packages from a server that does have an instance of the Database Engine. This means that the packages will not be running on the ETL server, but instead on the server from which they were started. As a result, the resources of the dedicated ETL server are not being used as intended. Furthemore, the resources of other servers might be strained by the running ETL processes.

Some additional information could be found here as well: Considerations for Installing Integration Services. You might want to take under consideration the licensing as well and still validate the standalone server is licensed properly.