Author Topic: SQL Server to SQL Server really slow transfer speed.  (Read 750 times)

Sunshine

  • EA Practitioner
  • ***
  • Posts: 1268
  • Karma: +114/-10
  • Its the results that count
    • View Profile
SQL Server to SQL Server really slow transfer speed.
« on: April 03, 2023, 03:45:00 pm »
Okay guys, I've changed organisations and found they have SQL Server 2012 with a really old schema V7.0 2004 for Sparx EA with lots of useful models. I've organised an upgrade to SQL 2019 (Thats they latest they have) and created a new database with schema 1558 from the SQL creating Script 1518.  The two scripts to upgrade the schema don't go back as far as the database we have. So far so good, got my new database with new schema set up. Just need to do a project transfer from the old SQL Server to new SQL server. The trouble I've encountered is it's been going for several hours and its still on t_xref table. I tried doing a SQL Server to a qea file the other day and it completed in 20 min. The DB isn't particularly large being around 1.6GB. The numbers of rows in the main tables look like this.
Table Name,Row Count,
t_xref,245754,
t_objectproperties,1261126,
t_object,172449,
t_diagramobjects,166265,
t_diagram,14227,
t_connector,104431,
t_attribute,62213,

Any ideas on how I can transfer from one SQL Server to another quicker, so it completes before I die of old age?
« Last Edit: April 04, 2023, 02:50:08 pm by Sunshine »
Happy to help
:)

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 12783
  • Karma: +532/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL Server to SQL Server really slow transfer speed.
« Reply #1 on: April 03, 2023, 05:39:41 pm »
You could try to use the new file based project transfer.

Geert

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 8508
  • Karma: +250/-129
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: SQL Server to SQL Server really slow transfer speed.
« Reply #2 on: April 04, 2023, 08:04:20 am »
We found the same; look for my posts from at least 6 months ago.


Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

Sunshine

  • EA Practitioner
  • ***
  • Posts: 1268
  • Karma: +114/-10
  • Its the results that count
    • View Profile
Re: SQL Server to SQL Server really slow transfer speed.
« Reply #3 on: April 04, 2023, 02:58:02 pm »
We found the same; look for my posts from at least 6 months ago.

Paolo
Yeah I saw a few posts on the topic from you but didn't see any solutions.
Happy to help
:)

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 8508
  • Karma: +250/-129
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: SQL Server to SQL Server really slow transfer speed.
« Reply #4 on: April 04, 2023, 03:44:54 pm »
We found the same; look for my posts from at least 6 months ago.

Paolo
Yeah, I saw a few posts on the topic from you but didn't see any solutions.
Yes. We've given up on it for the present.


IIRC, there was some desultory attempt by Sparx to duplicate the problem.  My suspicion is that it may have had something to do with the extra indices we placed on the SQL Server version, and building them on the fly was slowing it down.  But since we didn't have an easy mechanism to drop before and restore the indices after the transfer, we've "let it slide"  - we have other, more pressing issues.


Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!