As mentioned above, the promotion from Access to MSSQL

 

Running environment overview of the autumn Garden:

Currently, the database is Access in a sub-directory of a foreign godaddy VM.

 

Access pagination:

 

1: top max (id)

When the CYQ. Data framework supports Access, top max (id) is used as the paging mode.

In the absence of many articles in the autumn Garden, the general order is basically maintained.

The multi-field sorting status still occurs during the evolution of the autumn garden, for example, the order by field 1 and the Field 2.

The original top max (id) cannot display the paging data normally.

 

2: not in

Top max (id) is only applicable to single-field sorting. It cannot be applicable to multi-field sorting and is naturally unavailable.

Therefore, the paging mode of CYQ. Data is changed from top max (id) to normal not in mode.

The data volume was not tested at the beginning, and the page was too fast.

Later, when the article in the autumn garden reaches 3000 or 4000, it was found that the page was too slow to survive, so it was wiped out.

 

Top 3 times

The not in method is eliminated, and the top three times are replaced. If the page is split, the brush is used, and the speed is not high.

Therefore, this method has been stored until now. Although the pages of the current 3-4 million data are not fast, it is barely acceptable.

 

Although I have always wanted to optimize the program so that Access can continue to behave normally by an order of magnitude of 0.1 million,

 

But some things always come too fast, and Access cannot escape the weakness: concurrency.

 

Access has an unpredictable error in writing data concurrently. The most common error occurs in the background exception log of the autumn Garden:

Cocould not update; currently locked. SQL: Update Blog_User Set VisitCount = 64359 + 1 where ID = 111

This exception occurs frequently early in the morning. Later, after the program is optimized, the database method is updated based on the random probability of the memory count. The average number is 2-3 times a day.

 

This is also a good solution. You can lock it Before update,

Somehow, I just couldn't get down. Is it too few times that I always ignored it!

 

There is another human exception:

Unspecified errorSQL: select count (*) from Blog_Content where Year (CreateTime) = 2011 and Month (CreateTime) = 1 and UserID = 67 and TypeID = 0 and IsPub = true

This exception does not occur at ordinary times. Once it appears in the autumn garden, it is basically unable to be opened, and it will take a long time. The good answer for online search is what temporary files are full and cannot be squeezed in.

I can't control the server, and I can't solve it.

 

Today, when dozens of netizens write data at the same time, the situation has come, and it is basically not open..

 

So I plan to upgrade the autumn Garden from Access to MSSQL.

 

Why not consider the SQLite database?

In fact, SQLite was taken into consideration at the beginning. However, due to the tight schedule and the versatility of the Framework for some functions, only Access, MSSQL, and Oracle are processed. What do you mean?

The same function may have different names and usages in different databases. Many other systems that support multiple databases write two or more statements, different database versions submit different DLL overwrites.

When CYQ. Data is processed at the underlying layer, one statement can be automatically parsed into statements of different database types. In this way, the statement is written and compatible with multiple databases.

Since SQLite and MySQL are newly added in the latest version, they are not compatible yet. In addition, it seems that data importing from Access to SQLite is not easy, so it is useless.

 

However, I found that the data import and export function of MSSQL has SQLite, provided that I have installed the SQLite driver and tried again when I have time.

 

Okay, I decided to change the database. The Access database is on a remote server. What should I do?

Because the sub-directories are sent to other users, no FTP permission is assigned.

Therefore, the most common folder compression is gone.

Therefore, ICSharpCode. SharpZipLib. dll is used to write a Zip file for online compression. After the Access file is compressed, it can be several times smaller.

 

What should I do before compression?

The most basic idea at the beginning is to stop Website access so that no read/write operations will be performed on the database.

 

What method did I use when I had a chance?

Because of the custom lifecycle of the autumn garden, there are many unified checkpoints. I just need to easily pass the OnPost event to the operation and change it to the operation output:

Sorry: The system is being upgraded and data cannot be submitted.

In this way, the user's write operations on the database are easily avoided, and the normal access to the site is ensured.

 

After compression, more than 300 M is compressed to more than 60 M, and the network speed of 56 K is reduced. After downloading for more than 10 minutes, the download is complete.

 

Next we will export the data:

Try to export the data to the Local Machine for testing and find that many Memo fields cannot be imported !!

You must change all database fields to nvarchar (max) before importing them.

After the import, you have to change the field back. It takes a lot of time to make a new one.

 

Then perform a local test:

When I run the site of the autumn garden, I found that the homepage was unavailable and scheduling found that I had to write SQL updates because I lost a field and had to re-import it.

Then we found that the two systems in the autumn garden were missing, and it took a lot of time to find the cause, so we made up for it.

 

Export from local machine to remote:

Later, data was exported from the local MSSQL database to the remote MSSQL database. Due to the database connection, data could also be exported,

At first, we found that the Field Types on both sides were a little different and we had to correct them.

Next, I started to export more than 10 thousand documents until now, but there was a gap of 20 thousand. So I wrote an article here.

 

ID auto-increment of imported data:

In the process, I encountered another problem because the auto-increment ID cannot be imported when it is enabled,

Therefore, when creating a table, you have to remove the auto-increment ID and import the data.

However, it is not easy to add SQL statements. The simplest way is to use IDE.

However, if SQL Server Management Studio is connected to a remote database, hundreds of thousands of databases,

Basically, it's enough to get stuck, let alone change it.

 

Originally there was:

Fortunately, the Microsoft Visual Studio 2005 server link can only display a single database, so it is indeed time-saving and worry-free to use it to modify the primary key and auto-increment ID.

 

Now, I have imported 11649 data records and more than 20 thousand data records, so I have to go to bed. I'll get up early tomorrow.

 

Supplement:

At Am, I got up and saw that:

Error 0xc0202009: Data Flow task: ole db error. Error code: 0x80004005.
Ole db records have been obtained. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 note: "cocould not allocate a new page for database 'cyqdata' because of insufficient disk space in filegroup 'primary '. create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. ".
(SQL Server Import and Export wizard)

 

As a result of transaction rollback, it was so sad that one night of data was not migrated at the startup !!

 

Supplement:

I just asked my colleagues about the space of MSSQL. Answer: 200 M!

OH .. My... God !!

 

Now we have recovered to the Access version. We have added two locks in the commit portal and counter portal. I hope everything is safe !!!

 

CurrentlyAutumn GardenOfficially resume Data Writing. Welcome to continue.