locked
Migrating Databases RRS feed

  • Question

  • Good afternoon, everyone!

    I'm not sure if this is the correct place for this question so, if it's not, please, someone, redirect it to where it needs to be. Thanx!

    I do a lot of business intelligence work with SSMS, so I know T SQL pretty well. But, I'm not a trained DB admin. My experience in creating a database is to R-click on a server, select "New Database..." and just run with the defaults. I also know how to create tables, import data, script a table, etc., etc., etc.

    So, when my manager sent me a task to get details on an existing server for migration purposes, I drew a blank and I came here in hopes someone will be able to help me out.

    His email to me was;

    I need details on SERVER1 server:

    • Server configuration
    • Databases (PROD only)
    • Storage Volumes, split by Data File and Log File for each database (PROD only)
    • Linked Servers – Only TWO Linked Servers are currently operational – please confirm (SQLSVR01 and SQLSVRP01)
    • Any other details

    Purpose is to migrate SERVER1 databases (PROD only) to a different environment. We needs the specs to build the new environment and the above information should help.

    I think I can get the configuration by R-clicking the server name and selecting properties and I can just grab the database names from the Object Explorer to fulfill bullet #2, but the rest is Greek to me.

    Can anyone give me some assistance on how I get the information he's looking for?

    Thanx in advance for any assistance!

    Wednesday, November 7, 2018 7:13 PM

Answers

  • Server Configuration: This, to me, is a broad subject i.e. what server configuration is he referring to? Perhaps, a good starting point would be to look at sys.configurations. Run select * from sys.configurations against the instance and look at "value_in_use" column. If the plan is to mirror the server configuration on the new server, you might need these settings.

    Databases (PROD only): No one, except your team, would know which ones are prod. You may have a mix of prod and non-prod DBs and you'd have to identify which ones are what.

    Storage Volumes: Once you have identified the prod DBs on the instance, right-click each one and select the properties tab-->Files-->Path. There you will see the data and log files path. Don't like the GUI, sure, run the following in SSMS (being connected to the SQL Server in question). Run it under the context of each Prod DB.

    select name "Logical filename", physical_name "file path" from sys.database_files

    Linked Servers: This is a bit tricky. In order to know which linked servers are in use and which ones aren't, you would have to find if there's an object in the database that references the linked server. Here's an example to find linked server dependencies. That's not enough though, as there may be ad hoc queries using a linked server, in which case you can run a trace against the target server (the server linked server points to), to see if there are any connections coming from the "Login" the linked server uses. There are still some gotchas with this approach but that should give you a starting point. Of course, the easiest is to disable the linked server and wait if anyone complains. 

    Any other details: Again, this is a wide question as there can be a lot of details about a SQL instance. Generally, when migrating DBs, you would want to look at the DB users and migrate the corresponding logins to the new server, server-level permissions, jobs that depend on the DBs being moved etc. Also, make sure the resources (CPU, Memory etc.) can accommodate the load of the DBs.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Proposed as answer by Puzzle_Chen Thursday, November 8, 2018 6:30 AM
    • Marked as answer by Adam Quark Thursday, November 8, 2018 1:05 PM
    Thursday, November 8, 2018 12:45 AM
  • You must check SSIS and ssrs are used in current env.

    You have migrate SSRS and SSIS packages.

    For SSRS --> copy all rdl files and report server key with report server database backup

    For ssis-> 

    use msdb;
    
    select * from dbo.sysssispackages


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    • Proposed as answer by Puzzle_Chen Thursday, November 8, 2018 6:30 AM
    • Marked as answer by Adam Quark Thursday, November 8, 2018 1:05 PM
    Thursday, November 8, 2018 12:56 AM

All replies

  • Server Configuration: This, to me, is a broad subject i.e. what server configuration is he referring to? Perhaps, a good starting point would be to look at sys.configurations. Run select * from sys.configurations against the instance and look at "value_in_use" column. If the plan is to mirror the server configuration on the new server, you might need these settings.

    Databases (PROD only): No one, except your team, would know which ones are prod. You may have a mix of prod and non-prod DBs and you'd have to identify which ones are what.

    Storage Volumes: Once you have identified the prod DBs on the instance, right-click each one and select the properties tab-->Files-->Path. There you will see the data and log files path. Don't like the GUI, sure, run the following in SSMS (being connected to the SQL Server in question). Run it under the context of each Prod DB.

    select name "Logical filename", physical_name "file path" from sys.database_files

    Linked Servers: This is a bit tricky. In order to know which linked servers are in use and which ones aren't, you would have to find if there's an object in the database that references the linked server. Here's an example to find linked server dependencies. That's not enough though, as there may be ad hoc queries using a linked server, in which case you can run a trace against the target server (the server linked server points to), to see if there are any connections coming from the "Login" the linked server uses. There are still some gotchas with this approach but that should give you a starting point. Of course, the easiest is to disable the linked server and wait if anyone complains. 

    Any other details: Again, this is a wide question as there can be a lot of details about a SQL instance. Generally, when migrating DBs, you would want to look at the DB users and migrate the corresponding logins to the new server, server-level permissions, jobs that depend on the DBs being moved etc. Also, make sure the resources (CPU, Memory etc.) can accommodate the load of the DBs.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Proposed as answer by Puzzle_Chen Thursday, November 8, 2018 6:30 AM
    • Marked as answer by Adam Quark Thursday, November 8, 2018 1:05 PM
    Thursday, November 8, 2018 12:45 AM
  • You must check SSIS and ssrs are used in current env.

    You have migrate SSRS and SSIS packages.

    For SSRS --> copy all rdl files and report server key with report server database backup

    For ssis-> 

    use msdb;
    
    select * from dbo.sysssispackages


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    • Proposed as answer by Puzzle_Chen Thursday, November 8, 2018 6:30 AM
    • Marked as answer by Adam Quark Thursday, November 8, 2018 1:05 PM
    Thursday, November 8, 2018 12:56 AM
  • Thanx, guys!

    This forum is probably the best I've ever come across for some really great help!

    Thursday, November 8, 2018 1:06 PM
  • Server Configuration: This, to me, is a broad subject i.e. what server configuration is he referring to? Perhaps, a good starting point would be to look at sys.configurations. Run select * from sys.configurations against the instance and look at "value_in_use" column. If the plan is to mirror the server configuration on the new server, you might need these settings.

    Databases (PROD only): No one, except your team, would know which ones are prod. You may have a mix of prod and non-prod DBs and you'd have to identify which ones are what.

    Storage Volumes: Once you have identified the prod DBs on the instance, right-click each one and select the properties tab-->Files-->Path. There you will see the data and log files path. Don't like the GUI, sure, run the following in SSMS (being connected to the SQL Server in question). Run it under the context of each Prod DB.

    select name "Logical filename", physical_name "file path" from sys.database_files

    Linked Servers: This is a bit tricky. In order to know which linked servers are in use and which ones aren't, you would have to find if there's an object in the database that references the linked server. Here's an example to find linked server dependencies. That's not enough though, as there may be ad hoc queries using a linked server, in which case you can run a trace against the target server (the server linked server points to), to see if there are any connections coming from the "Login" the linked server uses. There are still some gotchas with this approach but that should give you a starting point. Of course, the easiest is to disable the linked server and wait if anyone complains. 

    Any other details: Again, this is a wide question as there can be a lot of details about a SQL instance. Generally, when migrating DBs, you would want to look at the DB users and migrate the corresponding logins to the new server, server-level permissions, jobs that depend on the DBs being moved etc. Also, make sure the resources (CPU, Memory etc.) can accommodate the load of the DBs.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    This is a big help, thanx!

    I had figured out the linked server part from some trial and error and discovered sys.configurations from some other searches later in the day.

    But, the biggest help are the suggestions you left for "Any other details" and "Storage Volumes." I didn't think of the logins and permissions, especially..

    I really liked this one, "Of course, the easiest is to disable the linked server and wait if anyone complains."


    • Edited by Adam Quark Thursday, November 8, 2018 1:15 PM
    Thursday, November 8, 2018 1:15 PM