July 18, 2016 by Kenneth Fisher
Let’s say your company relies on the cloud for its servers (or maybe not, on premise networks have more options but they still have their limits). Performance is important so you’ve tuned every query, you’ve tested and adjusted each of the server settings. The cloud gives you limited control over hardware tuning. So what’s next?
The bandwidth between your machines and the cloud is limited and can be quite expensive to increase. There are methods of compressing the data being transmitted and thus increasing the amount of data transmitted using the same bandwidth. There is hardware that can perform the compression but of course since we are working with the cloud that isn’t really an option, and if you are working on premise it tends to be very expensive.
Introducing NitroAccelerator. NitroAccelerator is a software solution that compresses TDS data between a SQL Server instance and its clients. It can easily be installed on the instance’s cloud server and equally easily installed on each of the clients.
The same install and installation process is used for the server and the clients. When I installed NitroAccelerator on my laptop it not only took less than 5 minutes but no system restart was necessary.
The compression feature is turned on by default. There is a very simple console available to turn compression off or turn on a few additional features. (Note: No restart is necessary to start/stop compression but you will need to create new connections. The old ones will continue to use the setting in place when they were created.)
It’s all about performance right? Well I was able to get 65% compression on a table with nothing but random values (a 200mb table transmitted as if it was 70mb) and with a table containing more regular data I got closer to 85% compression (a 35mb table transmitted as if it was only 5.25mb!). This translated into queries running up to 10x faster!
If your system is pulling small repetitive results sets you can turn on the HyperCache feature for an additional performance gain. This feature looks for stable result sets and then caches them. It can then return the results without waiting for the response back from the server resulting in a huge increase in speed. If the results back from the server don’t match then that query entry is invalidated and the system waits for the values to be stable again before re-caching them. Among other things this feature would be perfect for systems that frequently use lookup tables. (Frequently small and almost always stable.)
Are you worried about security? NitroAccelerator has an option to encrypt the data it’s compressing. Turning this feature on is as simple as checking a box in the console of the server and client. And for additional security you can provide your own encryption password.
NitroAccelerator is a powerful yet easy to use tool that can dramatically improve your SQL Server performance by compressing the data transmitted between the server and then client and provide you additional security by encrypting the data being transmitted across the wire.