MSSQL Monitoring, Backup, and Scaling
Performance Monitoring and Tuning Tools in MSSQL Server
Microsoft SQL Server provides a comprehensive set of tools for monitoring events in SQL Server and for tuning the physical database design. The choice of tool depends on the type of monitoring or tuning to be done and the particular events to be monitored. At a minimum, statistics should be generated at regular intervals to ensure expected performance.
Microsoft SQL Server Native Backup and Restore Support
A copy of SQL Server data that can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or file groups. Table-level backups cannot be created. In addition to data backups, the full recovery model requires creating backups of the transaction log.
recovery model
A database property that controls transaction log maintenance on a database. Three recovery models exist: simple, full, and bulk-logged. The recovery model of database determines its backup and restore requirements.
A multi-phase process that copies all the data and log pages from a specified SQL Server backup to a specified database, and then rolls forward all the transactions that are logged in the backup by applying logged changes to bring the data forward in time.
For more information, see
Scaling Out SQL Server
Scalability is the ability of an application to efficiently use more resources in order to do more useful work.
Scalable Shared Databases
The easiest scale out solution to implement in SQL Server is Scalable Shared Databases. In this scenario, you create a database on a SAN, and up to eight SQL Server instances running on different servers attach to the database, and start handling queries. This is the classic "shared disk"–style scale out solution, where processing power is scaled out, but only a single disk image of the data is used. At this point, those that are familiar with SQL Server might have questions such as: "But what happens to the locks? I thought each SQL Server instance kept its own locks in its own memory." This is true. Each instance will maintain its own database locks, and none of the instances will know about the other instances' locks. The only way this will work is if there are no locks, and thus Scalable Shared Databases work only if the database is attached as a Read Only database. This means that Scalable Shared Databases are great for data warehouses or reporting databases, but they are not suitable for applications that update data. Going back to our data characteristics, Scalable Shared Databases work only if the Update Frequency is zero. This data is, by definition, historical, and therefore it is all reference data.
Index Size Limitation and Implementation
In MSSQL Server, the maximum number of bytes in any index key cannot exceed 900 bytes. Though a key can be defined using variable-length columns whose maximum sizes add up to more than 900, but in that case no row must not be inserted with more than 900 bytes of data in those columns. (
ThingWorx users should be mindful of creating composite keys and their corresponding length. Users should design their key names to be short yet descriptive as possible.
Was this helpful?