-
|
Hello, I'm trying to do a schema build(~2 TB) for SQL Server and the build fails whenever the connections to the db fail intermittently. The DB undergoes maintenance or does a failover sometimes and there is a brief period(few seconds) of unavailability and during this time, all connections fail causing the build to fail. What can be done to avoid this? The build usually takes a long time and it's not feasible to start from scratch every time. Are there any retries performed if connections fail? I took a brief look at the code and couldn't find it. Error seen from one of the virtual users:
|
Beta Was this translation helpful? Give feedback.
Replies: 2 comments 1 reply
-
|
So yes, HammerDB is not intended to be run against intermittently failing databases and will report a dropped connection as an error. Error 9001 means the database can't be opened and 3314 that it is corrupted and should be restored, meaning that reconnecting is not likely to solve your particular problem. Instead, it is important to understand a bit more about the build process. By default, HammerDB will create the data and insert it into the database with an insert statement as it goes. (with multiple threads inserting into the same table at the same time). This has the advantage of being simple to run and no staging files are required, however all the inserts are processed like a regular database operation generating transaction logging with the database maintaining consistency in case recovery is required, this is costly in terms of database operations meaning the slowest part is invariably the database itself for building the schema. This is multiplied if you are running it across a network. Instead I would recommend using the Datagen feature which was designed for precisely this scenario. Here you create the data in the form of flat files first and then load them with a bulk load operation. This has the disadvantage that you will need a staging area to store data in the form of flat files, however there is no overhead from the database when creating the files, so your main limitation will be your disk speed where the files are stored, meaning this will be a lot, lot faster than a regular build and will not be interrupted when your database goes away. Also, it means you only have to do the data creation phase once, you can build the flat files and then keep these and reuse them multiple times to refresh the database with a bulk load. So for a smaller amount of time upfront to generate the scripts to create the tables and do the load you will have a more efficient to create your schema to work around your unreliability issues. |
Beta Was this translation helpful? Give feedback.
-
|
Tried the bulk upload way and it worked quitewell. Didn't face any errors. The only issue with this approach is that we were using Azure SQL DB which meant that we couldn't use the bulk copy commands mentioned in the hammerdb documentation. We had to first create an empty DB, then create tables, do a bulk upload using bcp tool and then create the indexes after the upload was done. The whole process was still actually faster than doing a regular schema build but then there was quite a bit of manual work involved. These steps aren't mentioned in the documentation and if you guys are interested, I could help add the documentation for it. |
Beta Was this translation helpful? Give feedback.
-
|
Yes of course the HammerDB documentation is open source as well and contained in this folder. This is in Docbook format and can be updated with any XML compatible editor and a pull request made to add the documentation updates, there are more details described here: |
Beta Was this translation helpful? Give feedback.
So yes, HammerDB is not intended to be run against intermittently failing databases and will report a dropped connection as an error. Error 9001 means the database can't be opened and 3314 that it is corrupted and should be restored, meaning that reconnecting is not likely to solve your particular problem.
Instead, it is important to understand a bit more about the build process. By default, HammerDB will create the data and insert it into the database with an insert statement as it goes. (with multiple threads inserting into the same table at the same time). This has the advantage of being simple to run and no staging files are required, however all the inserts are processed like a regul…