locked
Sql server migration from 2008 to 2017 (OS windows 2008 to Windows 2016) RRS feed

  • Question

  • Hello ,

    Myself  supersent,  Oracle DBA.  I am new to this forum and also to sql server.  :)

    I am given task of migrating  sql server 2008 to sql server 2017.  

    I have confident that I can achieve this  as basically I am in Database background.

    Please help me

    1) Sql server migration from 2008  to 2017 (OS windows 2008  to Windows  2016) both are different machines.

    can someone guide me the steps  of process for 2008  to 2017 sql server. Or share some usefull document  or link  so that I can enter the sql server world.

    Thanks

    Supersent


    Wednesday, May 8, 2019 1:06 PM

Answers

  • Finally is there any tool to compare the two sql servers? 2008 version an 2017 version.

    One method is using the schema compare feature of SQL Server Data Tools (SSDT). If you don't already have Visual Studio installed, download the free VS 2019 community edition. See https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt.

    SSDT schema compare (from the Tools-->SQL Server menu), allows one to compare both application-scoped (database tables, views, etc.) and/or non application-scoped (server logins, linked servers, etc.) objects. Use the schema compare options to specify the objects to be compared.

    I would expect database objects to be identical due to the restore so you'll probably find comparing only non application scoped objects most useful for your need. You'll need to address SQL Server Agent jobs separately as those are not compared by SSDT.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by supersent Friday, May 17, 2019 9:59 AM
    Friday, May 17, 2019 9:23 AM

All replies

  • Hi,

    The simpliest and safest way would be backup and resotre. Backup all the databases, objects, jobs, etc. Copy these backups to your new machine and perform the restore. Before the migration, you can use the Data Migration Assistant tool to detect compatibility issues that can impact database functionality in your new version of SQL Server. It can also help you migrate logins.

    You may want to refer to this blog from Tibor Karaszi.
    Moving a database between two SQL Server instances

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Thursday, May 9, 2019 2:51 AM
  • Thanks Puzzle_CHen for your reply.

    I ran Datamigration assistant  and checked the compatibility issues. Source is my database 2008. Target is 2017 sql server. DMA says no impact 100,110,120,130 levels looks fine.

    if compatibility level is 140  there is breaking changes for database as below

    "There is breaking  CLR strict security mode is introduced in 2017 version.  this mode is introduced for breaking changes for databases containing user-defined CLR assemblies marked either SAFE  or external access".

    in Target my  Compatiblity level is 100.  so  I feel it is fine to go for migration.

    I will take export from source (2008) and start restore in 2017  OR  I can migrate from DMA itself using migration option.. please suggest it will be good option .

    Let see if everything goes fine.

    Below link is not working  or connecting.. 

    https://karaszi.com/moving-a-database-between-two-sql-server-instances

    Thanks

    Supersent




    • Edited by supersent Tuesday, May 14, 2019 10:45 AM
    Tuesday, May 14, 2019 9:49 AM
  • Hi,

    >>I will take export from source (2008) and start restore in 2017  OR  I can migrate from DMA itself using migration option.. please suggest it will be good option.

    If they are very large database, you can use backup and restore. If not, you can simply use the DMA tool to do the migration.

    I will wait for your update here:-)

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 15, 2019 1:13 AM
  • Hello ,
    I found almost 150 databases in the sql server 2008(Source).
    Before I start to migrate I need to take source database objects count for each database. So that I can compare it with Target once we migrated.
    What approach I can proceed for DB compare between two source and target?
    using sys.objects  or sys.tables will give rowcount ,object count details of particular database only not all 150 databases. Oracle we have sys.dba_tables,sys.dba_objects for entire database.  but here each tablespace is database.
    How can I approach here to take source object count of all databases?  Please guide
    Supersent

    Wednesday, May 15, 2019 10:49 AM

  • How can I approach here to take source object count of all databases?  

    One method is to run the query against each database using a cursor. Below is an example that omits system databases.

    SET NOCOUNT ON;
    DECLARE @RowCounts TABLE(
    	  database_name sysname NOT NULL
    	, schema_name sysname NOT NULL
    	, table_name sysname NOT NULL
    	, row_count bigint NOT NULL
    	);
    
    DECLARE
    	  @DatabaseName sysname
    	, @SQL nvarchar(MAX);
    DECLARE databases CURSOR LOCAL FAST_FORWARD FOR
    	SELECT name FROM sys.databases WHERE name NOT IN(N'master',N'model',N'tempdb',N'msdb');
    OPEN databases;
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM databases INTO @DatabaseName;
    	IF @@FETCH_STATUS = -1 BREAK;
    	SET @SQL = 'USE ' + QUOTENAME(@DatabaseName) + N';'
    	+ 'SELECT DB_NAME() AS database_name, OBJECT_SCHEMA_NAME(t.object_id), t.name, SUM(p.rows) AS row_count
    	FROM sys.tables AS t
    	JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id IN(0,1)
    	GROUP BY OBJECT_SCHEMA_NAME(t.object_id), t.name;';
    	INSERT INTO @RowCounts (
    		  database_name
    		, schema_name
    		, table_name
    		, row_count
    		)
    		EXEC sp_executesql @SQL;
    END;
    CLOSE databases;
    DEALLOCATE databases;
    SELECT
    	  database_name
    	, schema_name
    	, table_name
    	, row_count
    FROM @RowCounts;
    GO
    


    • Edited by Dan GuzmanMVP Wednesday, May 15, 2019 11:08 AM added curosr close and deallocate
    Wednesday, May 15, 2019 11:07 AM

  • How can I approach here to take source object count of all databases?  

    One method is to run the query against each database using a cursor. Below is an example that omits system databases.

    SET NOCOUNT ON;
    DECLARE @RowCounts TABLE(
    	  database_name sysname NOT NULL
    	, schema_name sysname NOT NULL
    	, table_name sysname NOT NULL
    	, row_count bigint NOT NULL
    	);
    
    DECLARE
    	  @DatabaseName sysname
    	, @SQL nvarchar(MAX);
    DECLARE databases CURSOR LOCAL FAST_FORWARD FOR
    	SELECT name FROM sys.databases WHERE name NOT IN(N'master',N'model',N'tempdb',N'msdb');
    OPEN databases;
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM databases INTO @DatabaseName;
    	IF @@FETCH_STATUS = -1 BREAK;
    	SET @SQL = 'USE ' + QUOTENAME(@DatabaseName) + N';'
    	+ 'SELECT DB_NAME() AS database_name, OBJECT_SCHEMA_NAME(t.object_id), t.name, SUM(p.rows) AS row_count
    	FROM sys.tables AS t
    	JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id IN(0,1)
    	GROUP BY OBJECT_SCHEMA_NAME(t.object_id), t.name;';
    	INSERT INTO @RowCounts (
    		  database_name
    		, schema_name
    		, table_name
    		, row_count
    		)
    		EXEC sp_executesql @SQL;
    END;
    CLOSE databases;
    DEALLOCATE databases;
    SELECT
    	  database_name
    	, schema_name
    	, table_name
    	, row_count
    FROM @RowCounts;
    GO


    Excellent .   Thanks
    Wednesday, May 15, 2019 12:30 PM
  • Thanks to all.

    I have taken the backup of all the databases and restored in target.


    I going to take the objects count taken from source to compare with target.

    Finally is there any tool to compare the two sql servers? 2008 version an 2017 version.

    Supersent

    Friday, May 17, 2019 7:40 AM
  • Finally is there any tool to compare the two sql servers? 2008 version an 2017 version.

    One method is using the schema compare feature of SQL Server Data Tools (SSDT). If you don't already have Visual Studio installed, download the free VS 2019 community edition. See https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt.

    SSDT schema compare (from the Tools-->SQL Server menu), allows one to compare both application-scoped (database tables, views, etc.) and/or non application-scoped (server logins, linked servers, etc.) objects. Use the schema compare options to specify the objects to be compared.

    I would expect database objects to be identical due to the restore so you'll probably find comparing only non application scoped objects most useful for your need. You'll need to address SQL Server Agent jobs separately as those are not compared by SSDT.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by supersent Friday, May 17, 2019 9:59 AM
    Friday, May 17, 2019 9:23 AM