Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
What is the main difference pervasive SQL and MS SQL?
- Thread starter cler
- Start date
That's a very open ended question. Here are the basic differences:
1. PSQL - runs on Windows, NetWare, Linux. MSSQL - only runs on Windows.
2. There are some differences in the SQL syntax. For example, PSQL supports a "SET OWNER" statement that MSSQL doesn't. Also, PSQL has a "USING" clause for the CREATE TABLE statement.
3. Both support multiple interfaces (ADO.NET, OLEDB, ODBC, JDBC) but PSQL also supports another lower level interface (Btrieve) that is faster than SQL.
4. Cost. PSQL has a lower TCO than MS SQL (
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
1. PSQL - runs on Windows, NetWare, Linux. MSSQL - only runs on Windows.
2. There are some differences in the SQL syntax. For example, PSQL supports a "SET OWNER" statement that MSSQL doesn't. Also, PSQL has a "USING" clause for the CREATE TABLE statement.
3. Both support multiple interfaces (ADO.NET, OLEDB, ODBC, JDBC) but PSQL also supports another lower level interface (Btrieve) that is faster than SQL.
4. Cost. PSQL has a lower TCO than MS SQL (
http://www.pervasive.com/download_Check.asp?download=/documentation/whitepapers/pdf/Aberdeen_v8.pdf)
Those are the basic differences. If you have specific questions, please post them.Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Macola, one of the products I sell and support, runs on both platforms.
In the MS SQL case, there are 2 files - a .mdf anda .ldf file, which are constantly in use by the SQL server.
In the Pervasive SQL case, each one of the 500+ "tables" is actually a separate btrieve file. So at 7pm when most everyone has gone home and no one is on the software, you can literally open windows explorer, browse to the customer file, right click it and delete it!
Or you could email it to someone, or send it to a $29 memory stick you got at WalMart, etc.
Again in a MS SQL environment, the files are always in use, so none of this is possible.
This security concern, along with speed - particularly of crystal reports - is among the top reasons why you should steer towards a MS SQL platform.
Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports
"making predictions is tough, especially about the future" - Yogi Berra
In the MS SQL case, there are 2 files - a .mdf anda .ldf file, which are constantly in use by the SQL server.
In the Pervasive SQL case, each one of the 500+ "tables" is actually a separate btrieve file. So at 7pm when most everyone has gone home and no one is on the software, you can literally open windows explorer, browse to the customer file, right click it and delete it!
Or you could email it to someone, or send it to a $29 memory stick you got at WalMart, etc.
Again in a MS SQL environment, the files are always in use, so none of this is possible.
This security concern, along with speed - particularly of crystal reports - is among the top reasons why you should steer towards a MS SQL platform.
Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports
"making predictions is tough, especially about the future" - Yogi Berra
Actually, with PSQL 8.5 (released October 2003) and later, all access through Explorer can be eliminated as well by using Database Security.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
It can be turned on an off through the PCC. Pervasive even provides a login prompt (it's controlled by "Prompt for client credentials"). If you pass a URI, instead of a filename, on the Open call, you can bypass the Pervasive login box (and use your own).
I haven't dealt too much with the security settings as my applications don't need high security.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
I haven't dealt too much with the security settings as my applications don't need high security.
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Dgillz mentioned 500 files vs the mdf (Database) and ldf (Log). We were advised that from a performance stand point that you don't wait to open and close many file (Hi I/O on your server, and moving to SQL would reduce the I/O as the database is always open).
To show now bias - on the favorable side, the transactional engine in Pervasive blows the doors off of MSSQL when configured correctly. If your reports out of your ERP system are transactional in nature vs relational btrieve should be faster. Start joining tables and move to the relational, and MSSQL starts to look better.
As to security, there are many ways to skin that cat, simplest way use to be use a hidden share.
To show now bias - on the favorable side, the transactional engine in Pervasive blows the doors off of MSSQL when configured correctly. If your reports out of your ERP system are transactional in nature vs relational btrieve should be faster. Start joining tables and move to the relational, and MSSQL starts to look better.
As to security, there are many ways to skin that cat, simplest way use to be use a hidden share.
Macola does not support the database security model described by Mirtheil. This doesn't mean it wont work, just that Macola won't support it.
Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports
"making predictions is tough, especially about the future" - Yogi Berra
Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports
"making predictions is tough, especially about the future" - Yogi Berra
Back when I was a certified pervasive developer/technician I looked at the security feature that was just being released. Unless something drastic has changed you do have to code your solution to use a secured database. That being said I will add my two cents to the forum.
Data Entry Speed:
Pervasive is excellent for a closed system that has relatively high volumes of transactional data that requires speed for entering data and the cost of hardware needs to be kept low. Data can be entered through the btrieve API much quicker than can be done on a comparible server running MS SQL. This is not a problem though with a higher end MS SQL server.
Database Size:
Pervasive does not scale to databases greater than 4 gigabytes and for this reason I would not recommend it for anything larger than the small to mid-size business or for storing data in a small section of a larger business. Pervasive is great for use in a cash register system that is disconnected from a main office where cost and speed are an issue.
Reliability:
MS SQL wins hands down when it comes to reliability. I was a great Pervasive Technician because I had to be now I am a mediocre MS SQL administrator because I don't get the chance to fix much. MS SQL does not require much baby sitting for maintenance and data corruption is never an issue. Pervasive is notorious for data corruption. Most often this corruption can be recovered from, but it isn't uncommon to loose a few records.
Backup:
MS SQL provides a method for point in time backup and backup of online database (while the database is in use). Pervasive does provide a method for backing up an online database but last time I worked with this it did not work so well.
Cost:
Pervasive will win in this area. If it was not for Pervasive's cost I think that most developers would avoid Pervasive entirely. A Pervasive based solution requires less hardware and the license costs less. This makes it a good solution for a solution that will be used by a small number of users that need a shared database. Developing programs for Pervasive typically increases development costs by 50-100%. This is because retrieving data from a large data must be done with extreme care and use of the btrieve API is a little tricky. More time must be spent developing the database and testing the solution with large volumes of data.
Personal Comments:
I would say that having extensive experience with both databases has given me a good view of the two products side by side. I would personally do everything in my power to make sure that any solution uses MS SQL rather than Pervasive becuase MS SQL requires less late nights, working on the weekends to clear up data corruption issues, and is much easier to develop solutions for. Perasive still is the right fit for some solutions as noted above but Pervasive based products do not integrate well with other solutions like Crystal Reports (slow), Shipping Software, Microsoft Office, CRM solutions, etc... This is not to say that it can not integrate, it is just more difficult, slower, and often not supported by vendors.
I'm sure that others have very different opinions but these are my personal views and I welcome comments by others with differing views and perhaps some updates on areas of Pervasive where the product has improved since my last experiences.
Scott Travis
Data Entry Speed:
Pervasive is excellent for a closed system that has relatively high volumes of transactional data that requires speed for entering data and the cost of hardware needs to be kept low. Data can be entered through the btrieve API much quicker than can be done on a comparible server running MS SQL. This is not a problem though with a higher end MS SQL server.
Database Size:
Pervasive does not scale to databases greater than 4 gigabytes and for this reason I would not recommend it for anything larger than the small to mid-size business or for storing data in a small section of a larger business. Pervasive is great for use in a cash register system that is disconnected from a main office where cost and speed are an issue.
Reliability:
MS SQL wins hands down when it comes to reliability. I was a great Pervasive Technician because I had to be now I am a mediocre MS SQL administrator because I don't get the chance to fix much. MS SQL does not require much baby sitting for maintenance and data corruption is never an issue. Pervasive is notorious for data corruption. Most often this corruption can be recovered from, but it isn't uncommon to loose a few records.
Backup:
MS SQL provides a method for point in time backup and backup of online database (while the database is in use). Pervasive does provide a method for backing up an online database but last time I worked with this it did not work so well.
Cost:
Pervasive will win in this area. If it was not for Pervasive's cost I think that most developers would avoid Pervasive entirely. A Pervasive based solution requires less hardware and the license costs less. This makes it a good solution for a solution that will be used by a small number of users that need a shared database. Developing programs for Pervasive typically increases development costs by 50-100%. This is because retrieving data from a large data must be done with extreme care and use of the btrieve API is a little tricky. More time must be spent developing the database and testing the solution with large volumes of data.
Personal Comments:
I would say that having extensive experience with both databases has given me a good view of the two products side by side. I would personally do everything in my power to make sure that any solution uses MS SQL rather than Pervasive becuase MS SQL requires less late nights, working on the weekends to clear up data corruption issues, and is much easier to develop solutions for. Perasive still is the right fit for some solutions as noted above but Pervasive based products do not integrate well with other solutions like Crystal Reports (slow), Shipping Software, Microsoft Office, CRM solutions, etc... This is not to say that it can not integrate, it is just more difficult, slower, and often not supported by vendors.
I'm sure that others have very different opinions but these are my personal views and I welcome comments by others with differing views and perhaps some updates on areas of Pervasive where the product has improved since my last experiences.
Scott Travis
- Status
Similar threads
- Locked
- Question
- Locked
- Question