Database encryption has been around for a long time but only through third party implementations until the introduction of SQL Server 2005.
SQL Server 2005 offers native database encryption in the form of certificates, asymmetric keys, and symmetric keys. All three options provide cell level encryption which means you can choose to encrypt individual columns. Where these encryption methods differ is in their encryption strength and performance. Certificates and asymmetric keys will provide you with the highest level of encryption strength at the cost of performance. On the other hand, symmetric key encryption offers better encryption performance than the other two options and is the recommended option when performance outweighs security.
Read the full post »
So you want to know how to prepare your current environment for the future? First thing, it is very important to have a baseline of your current performance. A baseline is a performance reference point that should include current activity in your environment along with the performance of your servers. The capture of and analysis of this performance data should be an ongoing process so you can monitor trends in both resource utilization and user activity based on the comparison of your baseline data and current data. With this information in hand you’ll be able to see how your system resources (CPU, memory, disk, network, etc.) scale with your user activity.
Read the full post »
Are you aware that there is a SQL Server trace running right now on your SQL Server? Don’t be alarmed, this is by design and it can provide you with some valuable information in the event that tables were to go missing or new ones mysteriously appear out of thin air.
Read the full post »
There are a lot of businesses that rely on retrieving data from mobile workforces. Freight companies are scanning packages at pickup sites and sales forces are using handheld devices and/or laptops that need to transmit information to and from a central database.
These are just two commonly seen examples of the growing mobile workforce. SQL Server can provide the synchronization needed to ensure that mobile data is available and accurate through the use of Web Synchronization for Merge Replication:
Read the full post »
Many people hold a misconception that Peer-To-Peer replication and active-active clusters can provide write scalability. This article will provide a very brief overview of both technologies and some details as to why these technologies are not capable of scaling write operations.
Peer-To-Peer replication will provide you with the ability to read and write from multiple nodes, which in turn provides read layer scalability and read/write high availability. Although read operations scale very well, write operations are not improved and the write performance would be similar to that of a single node. This is true because all inserts, updates, and deletes are written to all nodes in near real time.
An active-active SQL cluster is a term that is a little misleading as this technically doesn’t exist in SQL Server. It is really a multiple instance cluster since a SQL cluster can only have one instance running on one node at a time. To make the other node perform database operations, you would need another instance running on that node and manage them in such a way that they are always running on a different node from one another. As this scenario does not allow you to run a single database across more than one node, this technology will also fail to provide you with scalability of your database write operations.
There is no out-of-the-box functionality that will provide write scalability in SQL Server, but there are manual processes that can be undertaken to accomplish this goal. For example, you may have heard or seen the word sharding before during your quest for the holy grail of write scalability. Sharding is the technique of splitting up a database into smaller databases. Sharding the database is really the only way to provide scalability of write operations in your Microsoft SQL Server environment.
Ultimately, the limitation of how scalable you want your write operations to be depends on how much work you’re willing to put into splitting up your database and modifying your application so they can deal with the interaction between the multiple databases.
What has worked for you? Let us know!
