SQL Migration - Identifying Connections - Best approach

Question

SQL Migration - Identifying Connections - Best approach

ramana3327

SSCoach

Points: 19722
More actions
September 25, 2023 at 11:23 am

#4293574
I have one SQL Database server which is old version and planning to migrate to SQL2017. We have about 10 databases on this server.
I am seeing connections to only one database from sp_who2. Looks the connections to other databases are closing immediately.
Question 1: Other than running SQL Profiler, what are the options available to find the incoming connections to the SQL server?
I want to make sure the source server will still available for next 2 months so planning to take Database offline on old server. I am not fan of Import & export option (It failed in another another server even after creating empty database).
Question 2: Which option is better and are there any other options you can suggest?
1. Copy the mdf & ldf files from source server to destination then attach the database on the destination server
2. Backup the source database then restore the database using that backup on destination server.
- This topic was modified 1 year, 1 month ago by ramana3327.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Q1 - Use extended events and capture the logon event and the statement started events to capture which db's have queries run against them.

Q2 - For me always backup and restore, then take the original DB offline.

Now for moving to SQL2017 why may I ask?

2017 is out of mainstream support already, has 4 years extended (best endeavours support from MSFT) left, so you're not going to get any bug fixes if you see any on 2017.

Should ideally be looking at moving everything now to 2022 or at a push 2019 to ensure you have the most life left in the system.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

You have not mentioned the version of the original SQL Server instance.
For instances that do not support extended events, we used a (random) sampling methode to get a glimpse over time. Since you only have 2 months, let's hope your trace(s) captures all applications that use your databases.
we also urge application developers to provide the connection strings with usable meta data ( application name )
for the needed Extended Events setup have a look at article "Logon monitoring in SQL Server and Azure Managed Instances - Adopting Extended Events" and its predecessor ( mentioned in its intro)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

For finding out whether DBs are being used, there is also the (simple, but somewhat brutal) option of taking them offline and waiting for someone to complain.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ramana3327 SSCoach Points: 19722 More actions · Answer 4

Thank you.

The existing server is SQL2000. Extended events doesn't support. I installed the SQL 2008 to support the migration for SQL 2017. Little worried to run the trace on this server. With out running profiler any other ways exists to find the connections to the SQL Server in older versions?

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Server side trace then, not as resource intensive as profiler.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

Server side trace will have the least impact.

Have look at the included sql file.

It needs a table in your DBA database. ( see script )

It creates 3 SQLAgent job:

DBA_SQLTrace_7Zip : the one that will compress (7zip ) your trace files to save space on the server.
DBA_SQLTrace_Stop_Start_Gen7Zip : will stop and start the trace and generate and launch job 1.
DBA_SQLTrace_7Zip_Cleanup_7z : Performs a powershell command to delete 7z files older than the set number of days ( -5 ) . This job will not work on a SQL2000 instance, since the sqlagent subsystem for Powershell is not available.

DOUBLE CHECK IT !

TEST IT - TEST IT - TEST IT --- before you run it in a production environment !!

Attachments:

You must be logged in to view attached files.