migrationsql serversql-server-2005sql-server-2016
I am new to administering SQL Server however I am comfortable with the SQL language and making SSIS packages.
I want to migrate data from SQL Server 2005 to 2016.
My question is do I need to worry about the system databases and other objects such as indexes, stored procedures, view, security and permissions. logins or can I just migrate data.
Which would be the recommended procedure in this case.
Generally speaking, if you just reapply of the stored procedures and no errors show up, you should be good to go (based on my experience from migrating 2 databases (2000->2005) (2005->2008). On a side note, this is why we started writing unit tests for our database code to handle problems like these.
For logins, this is better IMHO:
http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror
And for jobs, you don't need SSIS. Just highlight the Jobs node in Management Studio, click on Object Explorer Details (F7), in the right pane select all, right-click and script.
I'd do the logins first, in case any of the jobs involve any of the logins.
Best Answer
Max gave a decent answer which I will upvote once I'm done typing this alternate view up.
I am not a fan of restoring system databases when doing an upgrade migration and I prefer doing migrations over in place upgrades as I discussed in this lengthy answer to another question.
Basically I like to start "fresh" when I do a migration. I find that playing with system database migrations and upgrades through restore to sometimes cause frustrations with the restores and it can carry over potential sins.
You also asked about indexes, stored procedures, views. Those items at the database level should all live inside of a user database. So when you restore database X to the new server, all of the database objects (Tables, Users, Views, Procs, Functions, etc) will be there as well.
What exist in the system databases are jobs, logins, alerts, linked servers, encryption keys, etc. Instance level items.
I like to review those and migrate over what I need using various scripts - lately that is the DBATools.Io powershell scripts. I like using their script to copy sql logins especially, because it handles the SQL authenticated users keeping their passwords and security identifiers the same so the database users from those logins will work. They also have an entire SQL Server migration command which runs their sub commands to copy over the items I typically would copy over.
I do not believe Max to be wrong with that answer hence the upvote. I just have had more success and more luck and feel more comfortable migrating to new instead of trying to restore over system databases between versions. I would say that I've honestly can't remember the last time I did a version upgrade migration and didn't do it this way instead of restoring the system databases.