As mentioned above, the promotion from Access to MSSQL

Run environment overview of the autumn Garden: the database is Access in a subdirectory of a foreign godaddy VM. Access paging: 1: topmax (id) is used as the paging mode when the CYQ. Data framework supports Access. In the absence of many articles in the autumn Garden, the general order is basically maintained.

Running environment overview of the autumn Garden: the database is Access in a sub-directory of a foreign godaddy VM. Access paging: 1: When top max (id) supports Access on the CYQ. Data framework, top max (id) is used as the paging mode. When there are not many articles in the autumn Garden, the normal order is basically maintained.

AutumnOperation Environment overview of the park:

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.

InAutumnIn the absence of many articles, the park maintains a normal order.

UntilAutumnDuring the evolution of the park, multi-field sorting may occur, for example, order by field 1 and 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.

Post-meridianAutumnWhen the article in the park 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 immeasurable error in writing data concurrently.AutumnThe most common errors in the background exception log of the park:

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

This exception occurs many times early in the morning.ProcessAfter the order is optimized, the database method is updated only after the random probability is counted in the memory. The average value is 2-3 times per 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.AutumnThe garden 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 crowded.

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 planAutumnThe school upgraded 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?

AutumnBecause the park has a custom life cycle, there are a lot of unified barriers. 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:

RunAutumnSite, found that the home page was unavailable, scheduling found, missing a field, re-import trouble, had to write SQL updates.

Discover againAutumnThe two systems in the park were missing, and it took a lot of time to find the cause, so I 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:

ProcessWhen auto-increment ID is enabled, the ID cannot be imported,

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 !!!

CurrentlyAutumnParkOfficially resume Data Writing. Welcome to continue.