cancel
Showing results for 
Search instead for 
Did you mean: 

Move database to new server

bjanker77
Participant
2,247

Hi.

We’re going to move a customer database to a new server. It’s approx. 14 GB. Is it necessary to perform an unload/reload at the same time? It’s been 2 years since the last time that was done.

Br,

Bjarne

Accepted Solutions (0)

Answers (2)

Answers (2)

bjanker77
Participant

The main reason to move the database to a new server is as always performance. The issue is that running certain functions in our PowerBuilder application causes other applications to freeze because of write locks on 5-6 tables in the database. It still looks to be a problem, but only from the workstations. Sometimes this operation takes over 25 minutes to complete, and in the meantime there are lots of locks. This happens when we run commit in the application. However, there is no issues at all when running these applications directly on the database server. The same operation finishes in under 1 minute, and the locks are just present for a short while.

I've measured the network speed using iPerf3, and it averages around 510 Mbit/s. They have gigabit networking, so I've told the IT department to look into this. I've also done a sa_index_density() on one of the tables in use:

https://www.dropbox.com/s/ark5jhlp58sd67l/Capture.PNG?dl=0

I'm not sure how to decipher all this data, but it looks like something could be done here? Perhaps an unload/reload? Or reorganize table?

br,

Bjarne

VolkerBarth
Contributor

Could it be the different machines use different isolation level settings (possibly bound to machine-spcific ODBC settings)? (Minute-long "wait times" are probably more often caused by sub-optimal locking schemes than by hardware performance, methinks...)

bjanker77
Participant
0 Kudos

Hi.

It has crossed my mind, yes. Each workstation has their own ODBC in "System DSN". Just now, I found out that most of the workstation is set up with SQL Anywhere 12 ODBC driver. The database is SQL Anywhere 16, EBF 2193. That might be a potential problem? It's been like this for a long time apparently and these performance issues has escalated the last 2-3 months.

You will have to set the isolation level either in the ODBC or in the applications connectionstring, right?

br,

Bjarne

VolkerBarth
Contributor

...or via SET [TEMPORARY] OPTION isolation_level = X statements within the application or via according API calls...

Before you change those, I recommend to find out whether they are different, such as:

-- lists all current connections with isolation level
select sci.*,
   connection_property('isolation_level', sci.Number)
from sa_conn_info() sci
order by 1;

I would not expect the client version 12 vs. 16 to matter much here.


SQL Anywhere 16, EBF 2193

That is really old, right:)

Breck_Carter
Participant

Foxhound does a good job of showing which connections are blocked and why, in real time.

Figure 1. The Foxhound Monitor Page Showing An Idle Server With One Blocked Connection

MarkCulp
Participant

No need to rebuild. Just shutdown the database and copy the files to the new server.

bjanker77
Participant
0 Kudos

Thanks.

Just as I thought. Will consider an unload/reload if the performance does not increase significantly. But moving from an old 2008 server to a brand new 2016 server with SSD drives should do the trick.

Br,

Bjarne

VolkerBarth
Contributor
0 Kudos

If the hardware is very different, you might think about a re-calibration, aka ALTER DATABASE CALIBRATE...

bjanker77
Participant
0 Kudos

Ok, I'll check this out. Thanks.