In conjunction with the emergence of cloud computing, mobile device programming, and BYOD: Bring Your Own Devices there are challenges associated with the database design, performance, data integrity, and administration of MS SQL Server:
The infrastructure in place at Fortune 1000 companies, government municipalities, and healthcare providers is to a significant degree Microsoft centric - MS Windows family desktop operating systems and MS SharePoint software stack. Microsoft SQL Server can be used to automate administrative tasks and then specify the conditions under which each task occurs. The consolidation of databases using virtualization will improve efficiency and reduce cost through a higher utilization of resources, standardization, and improved manageability.
There are facilities and tools for improving productivity and more efficiently managing database operations: automating the administrative function with MS PowerShell and using the business intelligence and reporting features of MS SQL Server 12.
Database design is the process of transforming a logical data model into a physical database. A logical data model is required to design a physical database. Ir defines the business entities which comprise tables, attributes, and the interrelationship between the entities. This process involves normalization where redundancy is removed from the entities along with repeating data. Each entity will have primary key. Normalization will reduce the amount of data in tables and lead to a reduction in I/O: input/output for data retrieval and queries as well as memory savings in the database buffer pool. It also can help prevent issues with data modification anomalies with data updates. Referential integrity and foreign key validation becomes more efficient.
The logical model formalizes the user's view of the database; unstructured data is translated into user-defined requirements. This facilitates mapping to the physical model. An ER: entity relationship modeling documents the entities identified in the user's requirements and the relationships among entities. Business requirement analysis is used for gathering information and specifications from users and develops a user model. This is formalized into an ER model which serves as the logical model.
Physical database design translates the logical data model into SQL statements which define the database. Given the assumption of accurate information, the logical data model is transformed into a physical model. The steps are transform: 1- Entities into tables. 2- Attributes into columns. 3- Transforming domains into datatypes and constraints.
MS SQL Server dynamically manages the amount of memory allocated for index creation operations. If additional memory is needed for creating indexes, and the memory is available based on the server memory configuration settings, the server will allocate additional memory. If additional memory is required, but not available, existing allocated memory will be used to perform the operation.
This default self-tuning memory management in most situations will meet operational and performance requirements. Partitioned tables and indexes which have nonaligned partitioned indexes may require a different approach. Aligning an index with a partitioned table will be important in order to expand by taking on additional partitions or to accommodate frequent changes in partitioning.
When there is a high degree of parallelism with simultaneous index creation operations, there can be problems creating indexes. In order to address this, a specific amount of index creation memory can be set. The sp_configure stored procedure can be used for setting the index creation memory size. The amount of memory allocated to index creation operations should be defined equal to or greater than the minimum memory per query. If this allocation has not been implemented, then MS SQL Server will use the amount of memory specified as the minimum memory per query. A warning message also will be displayed.
There are facilities which can be used to maintain data integrity. The important steps in planning tables are identifying valid values for a column and deciding how to enforce the integrity of the data in the column. There are categories of data integrity.
An entity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties.
A domain is the validity of entries for a given column. Domain integrity can be enforced by restricting data typing, formatting through CHECK constraints and rules, and the range of possible values through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules.
Referential integrity preserves the defined relationships between tables when records are entered or deleted. It ensures that key values are consistent across tables and there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database. The enforcement of referential integrity within MS SQL Server prevents users from: 1- Adding records to a related table if there is no associated record in the primary table. 2- Changing values in a primary table that result in orphaned records in a related table. 3- Deleting records from a primary table if there are matching related records.
User-defined integrity provides for the definition of specific business rules that do not fall into one of the other integrity categories. All integrity categories support user-defined column- and table-level constraints in CREATE TABLE, stored procedures, and triggers.
MS SQL Server PowerShell can be used to programmatically automate and streamline administration.
Convert-UrnToPath converts a SQL Server Management Object URN: Uniform Resource Name to a SQL Server provider path. The URN indicates a management objectís location within the SQL Server object hierarchy. If the URN path has characters not supported by PowerShell, the characters are encoded automatically.
Decode-SQLName returns an unencoded SQL Server identifier when given an identifier that has been encoded. Encode-SQLName encodes special characters in SQL Server identifiers and name paths to formats that are usable in PowerShell paths. The characters encoded by this cmdlet include \:/%<>*?|. If characters are not encoded, then it will be necessary to escape them using the single quote (') character.
Invoke-PolicyEvaluation evaluates management policies applied to SQL Server instances. By default, this command reports but does not enforce compliance. Compliance is enforced by setting - AdHocPolicyEvaluationMode to Configure.
Invoke-SQLCmd runs a Transact-SQL or XQuery script containing commands supported by the sqlcmd utility. By default, this cmdlet doesnít set any sqlcmd variables or return message output.
When a column is too small to accept data, MS SQL Server does not permit a silent truncation of data. However, there are other ways that SQL Server can truncate data that is about to be inserted into a table without generating any form of error or warning.
By default, ANSI_WARNINGS are turned on and required for index creation on computed columns or indexed views. MS SQL Server will truncate the data as needed to make it fit into the column. Data which has been assigned to a variable will be silently truncated regardless as to the status of ANSI_WARNINGS.
A database which appears on servers that are not part of a backup and maintenance plan are problematic for an administrator: backup, maintenance, performance, or remove. A likely source of the problem would be a DEV server where developers have CREATE DATABASE permission or other applications where administrators can create databases using the Admin console, such as SharePoint. The DDL Trigger can be used to capture the event of the CREATE DATABASE command and the sp_send_dbmail for sending an e-mail when this event occurs.
For applications like SAP, which takes thousands of snapshots of data in short periods of time, the duration of time where data is held before it is pushed out to allow for the entry of data can be extended. This facilitates the management of data and helps maintain large sets within filegroups for switching data in and out to meet the needs of the data warehouse.
MS SQL Server 12 compression can reduce the growth of data volumes. Since the data is stored in fewer pages, performance for I/O intensive workloads can be improved through fewer queries for reading pages from disk.
Power View is included with the MS SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition.
Based on MS Silverlight and designed for interacting with a SSAS: SQL Server 2012 Analysis Services tabular model, Power View uses the RDLX file format for interactive data exploration, visualization, and presentation to generate ad-hoc reporting. It can be used to create and interact with views of data from data models based on PowerPivot workbooks published in a PowerPivot Gallery or tabular models deployed to SSAS. SQL Server 2012 Reporting Services uses Data Alerts to notify a list of recipients about data report changes.
Data alerts runs in the background and polls the reports for changes. Three tools are used to create and manage the alerts: Data Alert Designer, Data Alert Manager for users, and Data Alert Manager for administrators.
Azure and SQL Server are registered trademarks of Microsoft Corporation.