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.
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.
You'd want to consider migrating the system databases (master, msdb, and perhaps model), if you need access to the metadata stored in those databases.
Master stores items such as logins, security certificates, etc.
msdb contains details about backups, and stores SQL Server Agent job configurations, among other details.
Model may have been customized by you or your team to allow newly created blank databases to contain a set of predefined objects you use in every database.
Migrating system databases can be accomplished fairly easily; detailed instructions are available from MSDN at Back Up and Restore of System Databases (SQL Server).
Depending on your requirements, you could perform a BACKUP DATABASE operation against the user databases on the 2005 instance, and then RESTORE DATABASE on the 2016 instance to bring the entire database, including all data, indexes, and other objects.
This will, at least initially, require the same amount of space consumed by the database on the 2005 instance. However, once you've restored the database, you could take advantage of data compression to substantially reduce the required footprint.
See About Working with SQL Server Backups for details on performing backups, and About Restore Scenarios for details about restores.
Is it to 2012 or 2016? It makes a difference in that, IIRC 2012 is a tested migration path while 2016 is not. As such, known issues are documented and/or will be caught by Upgrade Advisor for 2012. A non-tested path might still work without any issues, it's just unknown. That said, strongly recommend you go to 2016. I suspect the effort will be almost the same.
See Are you upgrading from SQL Server 2005? in the documentation for 2005 upgrade options and links to a very detailed upgrade process. The upgrade process was written for 2014 but is still applicable for 2016.
