Decided To Set Up Mssql Database Computer Science Essay

Published:

Setting up an MSSQL database was relatively easy. The first step was to download the installer package from Microsofts website. The package I chose is the Microsoft SQL Server 2012 Express with Advanced Services. The reason why I chose this package is because it contains all the components of SQL express, which consists of the database engine, express tools, reporting services, and full text search. With this package I will be well-equipped to proceed with my coursework.

Installing the package was a relatively painless and user-friendly affair. Throughout the installation process, I left all the settings as defaults, and everything installed perfectly without a hitch.

So in order to populate the database, I went to http://www.sqlskills.com/blogs/bobb/post/ mondial-database-for-sql-server-2008.aspx to download the 3 files. The 3 files are:

modial_latlong.sql

mondial-inputs-sqlserver2008.sql

mondial-schema-sqlserver2008.sql

By running and executing these 3 files, I managed to successfully populate the database.

Lady using a tablet
Lady using a tablet

Professional

Essay Writers

Lady Using Tablet

Get your grade
or your money back

using our Essay Writing Service!

Essay Writing Service

A2

Database Engine

What's New (Database Engine)

Availability Enhancements (Database Engine)

Describes enhancements to high availability features.

Cross-cluster migration of AlwaysOn Availability Groups for OS upgrade.

Introduces features of AlwaysOn Availability Groups such as AlwaysOn SQL server failover cluster Instances and AlwaysOn Availability Groups tools.

Manageability Enhancements (Database Engine)

Describes enhancements to tools and monitoring features.

Enhancements include alternative keyboard shortcut schemes, improvements to query editor, changes to startup options, contained databases, and data-tier applications, Windows Powershell and Database Engine Tuning Advisor.

List of new and modified Dynamic Management views and functions.

Programmability Enhancements (Database Engine)

Describes programmability enhancements in the Database Engine.

Echancements include FileTables, statistical semantic search, property-scoped full-text search and customizable proximity search, ad-hoc query paging, circular arc segment support for spatial types, support for sequence objects, and default support for 15,000 partitions.

List of 14 new functions and 1 changed function for Transact-SQL.

Scalability and Performance Enhancements (Database Engine)

Describes scalability and performance enhancements in the Database Engine.

Enhancements include ColumnStore indexes, increased partition support, FILESTREAM Filegroups can contain multiple files, and Online Index Create, Rebuild and Drop.

Security Enhancements (Database Engine)

Describes security enhancements in the SQL Server Database Engine.

Security enhancements in the SQL Server Database Engine include provisioning during setup, new SEARCH PROPERTY LIST permissions, new user-defined server roles, default schema for groups, SQL server audit enchancements, database engine access is allowed though contained databases, hashing algorithms, further depreciation of RC4, certificate key length, service master key (SMK) and database master key (DMK) encryption changes from 3DES to AES, and certificates can be created from binary.

Resource Governor Enhancements (Database Engine)

Describes Resource Governor enhancements in the Database Engine.

Enhancements include support for 64 resource pools, greater CPU usage control, and resource pool affinity for partitioning of physical resources and predictable resource allocation.

Microsoft.SqlServer.Dac and Microsoft.SqlServer.Dac.Extensions

The Microsoft.SqlServer.Dac namespace provides classes to perform operations on DACPAC and BACPAC packages.

The Microsoft.SqlServer.Dac.Extensions namespace provides classes containing extension methods to retrieve information from DACPAC and BACPAC packages or utilize the expanded functionality not currently present in the Microsoft.SqlServer.Dac namespace.

SQL Server Database Engine Backward Compatibility

Deprecated Database Engine Features in SQL Server 2012

List of features that are still available in SQL Server 2012, but are to be removed in future versions of SQL Server

Discontinued Database Engine Functionality in SQL Server 2012

List of Database Engine features that are no longer available in SQL server 2012.

Breaking Changes to Database Engine Features in SQL Server 2012

List of changes in SQL Server 2012 Database Engine that may break applications, scripts, or functionalities that are based on earlier versions of SQL Server.

The list includes changes to Transact-SQL, Dynamic Management views, Catalog views, SQL CLR data types (geometery, geography, and heirarchyid), and XQuery functions.

Behavior Changes to Database Engine Features in SQL Server 2012

Behaviour changes include Metadata discovery, changes in behaviour in scripting an SQL Server Agent task, constant folding for CLR user-defined functions and methods, behavioural changes to STEnvelope() Method with empty spatial types, optional parameter for LOG function, changes to statistics computation during partitioned index operations, changes to data type conversion by the XML value method, sqlcmd.exe behaviour change in XML mode, and change in behaviour of exist() function on XML datatype.

Lady using a tablet
Lady using a tablet

Comprehensive

Writing Services

Lady Using Tablet

Plagiarism-free
Always on Time

Marked to Standard

Order Now

SQL Server Management Tools Backward Compatibility

Deprecated Management Tools Features in SQL Server 2012

List of depreciated Management Tools features that are still available in SQL 2012 but are scheduled to be removed in future releases of SQL Server.

Discontinued Management Tools Features in SQL Server 2012

Lists the SQL Server Management Tools features that are no longer available in SQL Server 2012. These features/components include SQL Server Compact edition, ActiveX subsystem for SQL Server Agent, Net Send and Pager Notification, sp_addtask, sp_deletetask, sp_updatetask, and some data-tier applications.

Breaking Changes to Management Tools Features in SQL Server 2012

SQL Server 2012 management tools cannot be used to create a utility control point on SQL Server 2008 R2.

SMO has ben reversioned in SQL Server 2012.

Behavior Changes to Management Tools Features in SQL Server 2012

Other changes to Management Tools features in this release.

Database Engine Features and Tasks

Database Engine Instances (SQL Server)

Describes what an instance of the Database Engine is.

Explains related tasks such as configuring Database Engine instances, collation and unicode Support, linked servers, managing the Database Engine Services, server network configuration, Database Engine scripting, maintenance plans, resource governor, database mail, extended events, SQL trace, SQL Server profiler, tracking data changes, log file viewer, Database Engine Tuning Advisor, diagnostic connection for Database Administrators, remote servers, and the Server Broker.

Database Features

Describes features and tasks associated with databases, database objects, data types, and the mechanisms used to work with or manage data.

Topics included in this section include databases, tables, indexes, partitioned tables and indexes, views, stored procedures, search, user-defined functions, statistics, plan guides, cursors, sequence numbers, DDL triggers, DML triggers, synonyms, XML data, spatial data, Binary Large Object Data, data-tier applications, the Transaction Log, database checkpoints, back up and restore of SQL Server databases, bulk import and export of data, data compression, OLE automation, event notification, and monitor and tune for performance.

Database Engine Cross-Instance Features

Describes the tools and tasks associated with managing and monitoring servers and database instances.

The tools and tasks described include the SQL Server Management Studio, SQL Server Utility features and tasks, administer servers by using policy-based management, register servers, SQL Server Distributed Replay, data collection, monitor resource usage, administer multiple servers using central management servers, SQL server configuration manager, and Activity Monitor.

High Availability Solutions (SQL Server)

The high-availability options include AlwaysOn Failover Cluster Instances, AlwaysOn Availability Groups, database mirroring, and log shipping.

Security and Protection (Database Engine).

The topics discusses under security and protection include securing SQL Server, Principals, server-level roles, database-level roles, credentials, securables, choosing an authentication mode, surface area configuration, TRUSTWORTHY database property, password policy, strong passwords, SQL Server encryption, SQL Server certificates and asymmetric keys, and SQL Server audit.

Technical Reference (Database Engine)

Feature Reference

Topics include registered servers F1 help, SQL Server Management Studio help, and Visual Database Tools F1 help.

Command Prompt Utility Reference

Topics include bcp Utility, dta Utility, and SqlLocalDB Utility.

Database Engine PowerShell Cmdlets

Discusses Invoke-PolicyEvaluation cmdlet and Invoke-Sqlcmd cmdlet.

Errors and Events Reference

List of Database Engine events and errors.

Showplan Logical and Physical Operators Reference

Describes the use and implementation of logical and physical operators.

Transact-SQL Reference (Database Engine)

Lists the kinds of applications that can generate Transact-SQL.

XQuery Language Reference (SQL Server)3

Topics related to XQuery discussed in this section include XQuery basics, XQuery expressions, modules and prolongs, XQuery functions against the xml data type, XQuery operators against the xml data type, and additional sample XQueries against the xml Data Type.

Data Quality Services (DQS)

Introducing Data Quality Services

The Business Need for DQS

Explains why businesses have a need for DQS.

Answering the Need with DQS

To resolve data quality issues, the DQS provides features such as data cleansing, matching, reference data services, profiling, monitoring, and knowledge base.

A Knowledge-Driven Solution

DQS knowledge-driven solution uses knowledge management processes and data quality projects to cleanse data.

DQS Components

DQS comprises of Data Quality Server and Data Quality Client.

Lady using a tablet
Lady using a tablet

This Essay is

a Student's Work

Lady Using Tablet

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Examples of our work

Data Quality Functionality in Integration Services and Master Data Services

Describes the DQS Cleansing component in Integration Services and the Data Quality Processes in Master Data Services.

Data Quality Services Concepts

Knowledge Management Concepts

Describes the processes used to create and manage the knowledge base.

These processes include knowledge discovery, domain management, matching policy, and reference data services.

Data Quality Project Concepts

Describes the various concepts of data cleansing including data cleansing, data matching, and profiling and notifications.

Data Quality Administration Concepts

Administrative tasks using the Data Quality Client application include activity monitoring, configuration, and DQS security.

Data Quality Services Features and Tasks

Data Quality Client Application

The Data Quality Client application is used to perform data quality operations using a standalone tool.

The client application can be used as a DQS KB Operator, a DQS KB Editor, and a DQS Administrator.

DQS Knowledge Bases and Domains

Describes how to create and build a DQS knowledge base.

Discusses about knowledge discovery, domain management, and data matching.

Data Quality Projects (DQS)

Explains the various Data Quality projects including cleansing activity, matching activity, and data profiling and notifications.

Data Cleansing

Describes the various aspects of data cleansing such as computer-assisted cleansing, interactive cleansing, leading value correction and standardizing cleansed data.

Data Matching

Explains how to perform data matching, building a matching policy and running a matching project.

Reference Data Services in DQS

Discusses the use of reference data from Windows Azure Marketplace, using reference data directly from the Third Party Reference Data Providers, and how to cleanse data by using the reference data

Data Profiling and Notifications in DQS

Explains how profiling works, profiling data by activity, profiling data in activity monitoring, and notifications.

DQS Administration

Describes administration activities by using Data Quality Client and administration activities outside of Data Quality Client.

DQS Security

The DQS security infrastructure is based on the SQL server infrastructure.

Discusses the DQS roles and user management.

Analysis Services

What's New (Analysis Services)

SQL Server 2012 Service Pack 1 (SP1)

New features include PowerPivot in Excel, PowerPivot for SharePoint, spPowerpivot.msi, version compatibility for tabular models, and importing from PowerPivot in Excel 2013.

SQL Server 2012 What's New by Feature

New features are added to the following areas: server instance and server monitoring, tabular modeling, multidimensional modeling, PowerPivot for Excel, PowerPivot for SharePoint, programmability, and design tools.

Analysis Services Backward Compatibility

Discontinued Analysis Services Functionality in SQL Server 2012

Migration Wizard, used to migrate SQL Server 2000 Analysis Services databases to newer versions, is discontinued because SQL Server 2000 is no longer supported.

Decision Support Objects (DSO) library that provided compatibility with SQL Server 2000 Analysis Services databases is also discontinued and no longer part of SQL Server.

Deprecated Analysis Services Functionality in SQL Server 2012

Features not supported in next version of SQL server include InsertInto connection string property, CreateCube connection string property, SQL Server 2000 PMML, and Create Action statement.

Features not supported in future versions of SQL Server include CalculationPassValue function, CalculationCurrentPass function, NON_EMPTY_BEHAVIOR query optimizer hint was turned on by default, CELL_EVALUATION_LIST intrinsic cell property, and COM assemblies.

Behavior Changes to Analysis Services Features in SQL Server 2012

Cube browser in Management Studio and Cube Designer has been removed

Higher permission requirements for using a PowerPivot workbook as an external data source.

PowerPivot Gallery: New rules for snapshot generation for some PowerPivot workbooks.

New default setting for load balancing requests changed from Round-Robin to Health-Based

Breaking Changes to Analysis Services Features in SQL Server 2012

Setup commands removed for a PowerPivot for SharePoint installation.

Analysis Services Features and Tasks

Comparing Tabular and Multidimensional Solutions (SSAS)

Topics discussed in this section include data source support by solution type, model features, model size, programmability and extensibility support, query and scripting language support, security feature support, design tools, client application support, SharePoint requirements, and server deployment modes for multidimensional and tabular solutions.

Analysis Services Instance Management

Topics discussed in this section include determining the server mode of an analysis services instance, configure service accounts (Analysis Services), configure server properties in Analysis Services, configure HTTP access to Analysis Services on Internet Information Services (IIS) 7.0, connect to an Analysis Services instance, register an Analysis Services instance in a server group, disconnect users and sessions on Analysis Services server, rename an Analysis Services instance, monitor an Analysis Services instance, and script administrative tasks in Analysis Services.

Tabular Modeling (SSAS Tabular)

Discusses tabular model solutions, tabular model databases, and tabular model data access.

Multidimensional Modeling (SSAS)

Discusses multidimensional model solutions (SSAS), multidimensional model databases (SSAS), multidimensional model object processing, multidimensional model partition management, multidimensional model roles and permissions, and multidimensional model assemblies management.

Data Mining (SSAS)

Discusses data mining concepts, data mining algorithms, mining structures , mining models , testing and validation, data mining queries, data mining solutions, data mining tools, data mining architecture, and security overview (Data Mining).

PowerPivot for SharePoint (SSAS)

Explains the benefits of PowerPivot for SharePoint.

Topics discussed include PowerPivot for Excel, PowerPivot configuration in central administration, PowerPivot Configuration using PowerShell, PowerPivot configuration tool, PowerPivot authentication and authorization, PowerPivot server administration, PowerPivot server health, PowerPivot management dashboard, PowerPivot dsage data collection, PowerPivot gallery, PowerPivot data access, PowerPivot data refresh, PowerPivot data feeds, and PowerPivot BI Semantic Model Connection (.bism).

Technical Reference (SSAS)

Data Mining Stored Procedures (Analysis Services - Data Mining)

The stored procedures include SystemGetCrossValidationResults, SystemGetClusterCrossValidationResults , SystemGetAccuracyResults, and SystemGetClusterAccuracyResults .

Errors and Events Reference (PowerPivot for SharePoint)

Provides information about errors and events for PowerPivot for SharePoint. Errors in this section are identified by the error message text that appears in a log or error window.

Analysis Services PowerShell

Describes the prerequisites, supported versions and modes of Analysis Services, authentication requirements and security considerations, and Analysis Services PowerShell tasks.

PowerPivot Reference for SharePoint PowerShell

List of PowerShell cmdlets used to configure or administer a PowerPivot for SharePoint installation.

Multidimensional Expressions (MDX) Reference

Description of of MDX syntax elements and MDX language reference.

Data Analysis Expressions (DAX) Reference

Describes the DAX syntax specification for PowerPivot, DAX operator reference for PowerPivot, and DAX function reference.

Data Mining Extensions (DMX) Reference

Provides information regarding DMX statements.

User Interface Reference (Analysis Services)

Help topics for Analysis Services Wizards (Multidimensional Data), data mining designers and dialog boxes, Analysis Services designers and dialog boxes (Multidimensional Data), Analysis Services designers and dialog boxes (Tabular), and Data Mining wizards.

Integration Services

What's New (Integration Services)

Deployment

Discusses the deployment of projects and packages, deployment of projects to Integration Services Server, and Integration Services Server.

Management and Troubleshooting

Describes the server environments, SSISDB catalog, troubleshooting performance and data issues, and reports for troubleshooting package operations.

Development Enhancements

Describes the project connection managers, offline connection managers, flat file connection manager changes, parameters, execute package task and parameters, comparing and merging packages, undo/redo in SSIS Designer, column mapping, and script task and script component.

Performance

Reduced Memory Usage by the Merge and Merge Join Transformations.

Data Quality

Explains the DQS Cleansing transformation.

Access to Samples and Tutorials

The Getting Started window in the SSIS Designer provides links to samples, tutorials and videos.

The SSIS Toolbox in SQL Server Data Tools (SSDT) provides links to samples and Help content for Control Flow and Data Flow items.

Integration Services Backward Compatibility

Deprecated Integration Services Features in SQL Server 2012

There are no deprecated Integration Services features in SQL Server 2012.

Discontinued Integration Services Functionality in SQL Server 2012

The features that are discontinued in the current release of SQL Server Integration Services are data source views, data viewer, Data Transformation Services (DTS), Execute DTS 2000 Package Task, and ActiveX Script task.

Breaking Changes to Integration Services Features in SQL Server 2012

There are no breaking changes in SQL Server 2012 Integration Services (SSIS) features.

Behavior Changes to Integration Services Features in SQL Server 2012

There are no behavior changes in SQL Server 2012 Integration Services (SSIS) features.

Integration Services Features and Tasks

Integration Services (SSIS) and Studio Environments

Describes how to utilize the SQL Server Data Tools and the SQL Server Management Studio.

Integration Services (SSIS) Packages

Discusses the package contents, package templates, objects that extend package functionality, package properties that support extended features, custom log entries available on the package, and configuration of packages.

Integration Services (SSIS) Connections

Describes the different connections used to perform different tasks.

Lists out the connection manager types that are provided.

Integration Services (SSIS) Projects

Explains what is a project, and what is a solution, for Integration Services Projects.

Integration Services (SSIS) Parameters

Explains the parameters used with the package and project deployment models.

Integration Services (SSIS) Queries

Explains the implementation of SQL in files and the SQL in variables.

Integration Services (SSIS) Expressions

Describes the components that use expressions, icon markers for expressions, and expression builder.

Integration Services (SSIS) Variables

List of variables that are used in Integration Services, as well as their purposes.

Integration Services (SSIS) Event Handlers

Describes the tasks the event handlers can perform, event handler content, run-time events, and configuring an event handler.

Integration Services Service (SSIS Service)

List of management capabilities provided by the Integration Services service.

Integration Services (SSIS) Server

Explains the purpose for Integration Services (SSIS) Server, high availability, and the use of Integration Services Server in SQL Server Management Studio.

Deployment of Projects and Packages

Compares the differences between project deployment and package deployment.

List of features of project deployment model.

Description of project deployment and required tasks.

Execution of Projects and Packages

List of tools to run an Integration Services package.

Describes the execution and logging for Integration Services packages.

Monitoring for Package Executions and Other Operations

Describes how logs, reports, views, performance counters, and data taps are used to monitor Integration Services operation.

List of operation types monitored.

Security Overview (Integration Services)

Explains how implementing identify features will ensure that packages only open from trusted sources, and how access control features allow only authorized users to open and run packages.

Technical Reference (Integration Services)

Integration Services Error and Message Reference

List of error messages, warning messages, informational messages, general and event messages, success messages, and data flow component error messages.

Views (Integration Services Catalog)

List of Transact-SQL views that are used in the administration of Integration Services.

Stored Procedures (Integration Services Catalog)

List of Transact-SQL stored procedures that are used in the administration of Integration Services projects.

Functions

List of Transact-SQL functions that are used in the administration of Integration Services projects.

Master Data Services

What's New (Master Data Services)

The new features in SQL Server 2012 Master Data Services include using Excel to manage Master Data, matching data before loading, loading data into MDS using entity-based staging, new model deployment tools, re-designed and higher-performance web user interface, SharePoint integration introduced, multi-level recursive hierarchies support, many to many mapping improved, codes automatically generated, security is simplified, and the installation is part of SQL Server.

Backward Compatibility (Master Data Services)

Deprecated Master Data Services Features in SQL Server 2012

Staging process from SQL Server 2008 R2 and metadata will be removed in future editions of SQL Server 2012.

Discontinued Master Data Services Features in SQL Server 2012

Model object permissions can no longer be assigned to the Derived Hierarchy, Explicit Hierarchy, and Attribute Group objects.

New staging process cannot be used to create or delete collections, add members to or remove members from collections, and reactivate members and collections.

Other discontinued features include the model deployment wizard, code generation business rules, bulk updates and exporting, and PowerShell cmdlets.

Master Data Services Features and Tasks

Models (Master Data Services)

Explains how models relate to other objects.

Describes an example of a model.

Entities (Master Data Services)

Explains how entities relate to other model objects.

Description of using entities as constrained lists, base entities, entity security, and entity examples.

Attributes (Master Data Services)

Explains how attributes relate to other model objects.

Description of required attributes, attribute types, attribute examples, and related tasks.

Domain-Based Attributes (Master Data Services)

Explains the use of same entity for multiple domain-based attributes, how domain-based attributes form derived hierarchies, and an example of a domain-based attribute.

Attribute Groups (Master Data Services)

Explains how attribute groups change the display, and how to show or hide attribute groups, as well as related tasks.

Master Data Services Add-in for Microsoft Excel

List of related tasks for MDS add-in for Excel.

Members (Master Data Services)

Explains how members relate to other model objects and using hierarchies and collections to organizer members.

Description of member types, examples and related tasks.

Transactions (Master Data Services)

Explains when transactions are recorded, and how to view and manage transactions.

Description of system settings and concurrency.

Annotations (Master Data Services)

Explains how to annotate a transaction.

Hierarchies (Master Data Services)

Describes what hierarchies contain, the kinds of hierarchies, and an example of a hierarchy.

Explains that hierarchies are not taxonomies.

Collections (Master Data Services)

Describes what collections contain, and the subscription views for collections.

Business Rules (Master Data Services)

Describes the creation and publication of business rules, how business rules are applied, the system settings, and related tasks.

Validation (Master Data Services)

Explains when data validation occurs, and related tasks.

Versions (Master Data Services)

Explains when to use versions.

Describes version flags, workflow for version management, sequential or simultaneous versions, and related tasks.

Notifications (Master Data Services)

Explains how notifications are sent, and when notifications are sent.

Description of system settings, and related tasks.

Security (Master Data Services)

Explains how to set security.

Describes the different types of users, security in the add-in for Excel, and related tasks.

Importing Data (Master Data Services)

List of staging tables.

Explains how to initiate the staging process, log transactions, validate data, and related tasks.

Exporting Data (Master Data Services)

List of subscription view formats, and related tasks.

Deploying Models (Master Data Services)

Describes the tools for deploying models, what packages contain, sample packages, and related tasks.

Developer's Guide (Master Data Services)

Describes the use of Master Data Manager web service, custom workflows, and web server namespaces.

Microsoft.MasterDataServices

List of classes that provide the main entry point for Master Data Services.

Technical Reference (Master Data Services)

Master Data Services Configuration Manage

A tool used to create or configure a Master Data Services database.

Master Data Services Database

Describes the leaf member staging table, consolidated member staging table, relationship staging table, and staging process errors.

Master Data Manager Web Application

Describes the explorer functional area, version management functional area, integration management functional area, system administration functional area, and the user and group permissions functional area.

SQL Server Replication

What's New (Replication)

Replication support for AlwaysOn Availability groups.

Replication Extended Events.

Support for 15000 partitions.

Replication Backward Compatibility

Deprecated Features in SQL Server Replication

Deprecated features in SQL Server 2012 include RMO, heterogeneous replication, and Oracle publishing.

Breaking Changes in SQL Server Replication

There are no breaking changes in SQL Server 2012 for replication features.

Replication Features and Tasks

Types of Replication

The types of replication are transactional replication, merge replication, and snapshot replication.

Replication Agents

Describes the distribution agent, log reader agent, merge agent, merge agent, queue reader agent, snapshot agent, and agent administration.

Security and Protection (Replication)

Explains how encryption can be used to reduce threats to a replication topology, and how to carry out identity and access control.

Description of the replication agent security model, security role requirements for replication, and replication security best practices.

Explanation on how to secure the distributor, publisher, subscriber, and the snapshot folder.

Monitoring (Replication)

Description of the Microsoft SQL Server Replication Monitor, Microsoft SQL Server Management Studio, Transact-SQL and Replication Management Objects (RMO), Alerts for replication agent events, and system monitor.

Scripting Replication

Describes example of automating a task with scripts.

Explanation on script replication objects.

Replication over the Internet

Replication can be carried out over the internet via a Virtual Private Network (VPN) or the web synchronization option for merge replication.

Heterogeneous Database Replication

Describes the publishing of data from Oracle and the publishing of data to non-SQL Server functions.

Configure Replication for AlwaysOn Availability Groups (SQL Server)

Lists the required steps for configuring replication and AlwaysOn availability groups.

Maintaining an AlwaysOn Publication Database (SQL Server)

Describes how to maintain/remove a published database in/from an Availability Group.

Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server)

Describes publisher redirection, changes to replication agents to support AlwaysOn Availability Groups, stored procedures supporting AlwaysOn, change data capture, change tracking, as well as the prerequisites, restrictions, and considerations for using replication with AlwaysOn Availability Groups.

Log Shipping and Replication (SQL Server)

Describes the requirements and procedures for replicating from the secondary if the primary is lost.

Database Mirroring and Replication (SQL Server)

List of requirements and considerations for using replication with database mirroring.

Explanation on how to configure replication with database mirroring, and maintaining a mirrored publication database.

Technical Reference (Replication)

Replication Views (Transact-SQL)

List of the system views used by replication.

Replication Tables (Transact-SQL)

List of the system tables used by replication.

Replication Stored Procedures (Transact-SQL)

List of the stored procedures used by replication.

Properties Reference

List of information for the various replication wizards and dialog boxes.

Tools Reference

Tools for implementing, administering, and troubleshooting replication include the SQL Server Management Studio, programming interfaces, and other Microsoft Windows components.

Errors and Events Reference

Lists the cause and resolution information for a number of errors related to replication.

Reporting Services (SSRS)

What's New (Reporting Services)

SQL Server 2012 Service Pack 1 (SP1)

Added support for Power View in Microsoft Excel 2013 and Power View in Microsoft SharePoint 2013.

SQL Server 2012 SP1 Reporting Services report server in SharePoint mode now supports SharePoint 2013.

New version of the Reporting Services add-in for SharePoint that supports SharePoint 2013 and SharePoint 2010.

View and interact with reports on iOS devices.

Power View

Describes the properties of Power View such as basing on tabular models and coexisting with report builder.

SharePoint Mode

Describes the list of changes made to SharePoint integration to improve the SharePoint IT administrator experience, the end user experience, and supportability.

Data Alerts

Data alerting allows the user to define and save data alert definitions, run data alert definitions, and deliver data alert messages to recipients.

Data alerts also provides tools including data alert designer, and data alert managers for users and administrators.

Report Server Projects in SQL Server Data Tools for Visual Studio

Describes the various aspect of report server projects.

Excel Renderer for Microsoft Excel 2007-2010 and Microsoft Excel 2003

Improvements in rendering extension includes increasing maximum rows per worksheet to 1048576, increasing maximum columns per worksheet to 16384, number of colours allowed in a worksheet is approximately 16 million (24-bit colour), and ZIP compression now provides smaller file sizes.

Word Renderer for Microsoft Word 2007-2010 and Microsoft Word 2003

The Word renderer allows SQL Server 2012 to render a report in Microsoft Word.

Reporting Services Backward Compatibility

Deprecated Features in SQL Server Reporting Services in SQL Server 2012

Depreciated device information settings for the HTML rendering extension include ActionScript, ActiveXControls, GetImage, OnlyVisibleStyles, ReplacementRoot, ResourceStreamRoot, StreamRoot, UsePx, and Zoom.

Other depreciated features include Microsoft Word and Microsoft Excel 1997-2003 rendering, Report Definition Language (RDL) 2005 and earlier, SQL Server 2005 and earlier custom report items, Reporting Services snapshots 2005 and earlier, report models.

Discontinued Functionality to SQL Server Reporting Services in SQL Server 2012

Currently there is no discontinued Reporting Services functionality in SQL Server 2012.

Breaking Changes in SQL Server Reporting Services in SQL Server 2012

Breaking changes include that SharePoint mode server references now require the SharePoint site, the Reporting Services WMI Provider no longer supports configuration of SharePoint mode, and Report Model Designer is no longer available in SQL Server Data Tools.

Describes list of changes to SharePoint mode command-line installation.

Behaviour Changes to SQL Server Reporting Services in SQL Server 2012

Breaking changes include that viewing items permission will not download shared datasets, Report Server trace logs are in a new location for SharePoint mode, GetServerConfigInfo SOAP API is no longer supported, Configuration Manager is not used for SharePoint Mode, and that server modes cannot be changed from one mode to another.

Reporting Services Concepts (SSRS)

Report Server Concepts

Describes the running of report server or report server configured in native mode, reporting Services as a SharePoint server or report server configured in SharePoint mode, report server items, folders, roles and permissions, schedules, subscriptions and delivery, extensions, and report access.

Reports and Related Item Concepts

Describes the reports and report definitions, report data connections and data sources, report datasets, report parameters, report items, data regions and maps, report parts, and data alerts.

Types of Reports

The different types of reports include drilldown reports, subreports, main/detail reports and drillthrough reports, linked reports, history reports, cached reports, snapshots, saved reports, published reports, and upgraded reports

Stages of Reports

The stages of reports are report definition, compiled report and intermediate report format, snapshot or report history, processed report, rendered report, and exported report.

Reporting Services Features and Tasks (SSRS)

Reporting Services Report Server (SSRS)

List of features and modes provided by Reporting Services Report Server include native mode, native mode with SharePoint web parts, SharePoint mode, report processor, report server database, and authentication and rendering.

Reporting Services Reports (SSRS)

Lists the benefits of reporting services reports, and the stages of report processing, creating reports, previewing reports, saving or publishing reports, viewing reports, managing reports, securing reports, creating notifications based on report data, upgrading reports, and troubleshooting reports.

Report Data (SSRS)

List of tips for specifying report data, data connections, data sources, and datasets.

Report Parameters (Report Builder and SSRS)

Describes the common uses for parameters, types of parameters, creating parameters, report parameters, dataset parameters, cascading parameters, displaying parameter values in a report, setting parameters on a drillthrough report, setting parameters on a subreport, managing parameters on a published report, setting parameters on a report URL, setting parameters for a subscription, setting parameters for a snapshot, and parameters and securing Data.

Report Parts in Report Designer (SSRS)

Explains the life cycle of a report part publishing, how to publish/reuse/republish report parts.

Schedules

Topics described include what can one do with schedules, comparing shared and report-specific schedules, configuring the data sources, storing credentials and processing accounts, how scheduling and delivery processing works, server dependencies, and the effects of stopping the SQL Server Agent and Report Server Service.

Subscriptions and Delivery (Reporting Services)

Describes the subscription scenarios, standard and data-driven subscriptions, subscription requirements, delivery extensions, delivery extensions, and the parts of a subscription.

Data Alerts (SSRS)

Describes the data alerts architecture and workflow, permissions for data alerts, diagnostics and logging, performance counters, support for SSL, data alerts user interface, and the globalization of data alerts.

Explains how to install and configure Data Alerts.

Power View (SSRS)

Describes the list of features of Power View including creating data visualizations, filtering and highlighting data, sorting, and creating reports with multiple views.

Security and Protection (SSRS)

Describes the authentication and authorization processes in Reporting Services.

URL Access (SSRS)

Discusses the URL access concepts and related tasks.

Extensions (SSRS)

Extensions include security extensions, data processing extensions, rendering extensions, report processing extensions, and delivery extensions.

Tools (SSRS)

Described the tools for report authoring, tools for report server administration, and tools for report content management.

Technical Reference (SSRS)

Cause and Resolution of Reporting Services Errors

List of errors and resolutions related to Reporting Services.

Report Designer F1 Help

Describes the F1 Help for the SQL Server Reporting Services Report Designer wizards, views, and dialog boxes.

Report Manager F1 Help

List of topics providing page-level help for SQL Server Reporting Services Reporting Manager.

Reporting Services Configuration Manager (SSRS)

Describes the tasks that can be performed by the Reporting Services Configuration Manager.

Description of requirements.

Explanation on how to start the Reporting Services Configuration Manager.

Report Wizard Help

Describes a list of F1 help topics for the Report Wizard.

HTML Viewer and the Report Toolbar

Description of the components and functions of the report toolbar.

Explanations of parameters and credentials.

Device Information Settings for Rendering Extensions (Reporting Services)

List of device information settings that are used to pass rendering parameters to a rendering extension.

rs Utility (rs.exe) (SSRS)

Describes the syntax, file location, arguments, permissions, and examples.

rsconfig Utility (SSRS)

Describes the syntax, arguments, permissions, file location, remarks, and examples.

rskeymgmt Utility (SSRS)

Describes the syntax, arguments, permissions, examples, file locations, and remarks.

Reporting Services WMI Provider Library Reference (SSRS)

Describes the MSReportServer_Instance class and MSReportServer_ConfigurationSetting class.

A3

Method 1:

There are 2 ways one can go about completing this part of the coursework. First would be by making SQL queries. To do this, 2 commands are needed:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

SELECT * FROM INFORMATION_SCHEMA.COLUMS WHERE TABLE_NAME = 'table_name';

The first query returns a list of tables which are present in the Mondial database. We can see the list of tables on the next page.

Using the table names listed here, we can use the second query to return the full information schema for each table. An example is shown on the next page.

The full list of information displayed by the command is listed below:

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

ORDINAL_POSITION

COLUMN_DEFAULT

IS_NULLABLE

DATA_TYPE

CHARACTER_MAXIMUM_LENGTH

CHARACTER_OCTET_LENGTH

NUMERIC_PRECISION

NUMERIC_PRECISION_RADIX

NUMERIC_SCALE

DATETIME_PRECISION

CHARACTER_SET_CATALOG

CHARACTER_SET_SCHEMA

CHARACTER_SET_NAME

COLLATION_CATALOG

COLLATION_SCHEMA

COLLATION_NAME

DOMAIN_CATALOG

DOMAIN_SCHEMA

DOMAIN_NAME

While this method works, there is a much straight forward method for completing this task of the coursework. That would be getting the information we need directly from the GUI via the Object Explorer.

Method 2:

From the object explorer, we can very easily see all the tables that are in the Mondial database by simply navigating to Databases\Mondial\Tables as shown in the screenshot below.

From the Object Explorer , we can also fins out the attributes for each table. This can be done by navigating to the Keys folder for each table and clicking on the corresponding key.

This will display a window shown below. From this window, we can see the basic schema the Borders table, as well as which is the primary key(s), which values(s) can accept null, as well as their data type.

While I am sure there might be situations where I would need to use SQL queries the future, but this Object Explorer provides a quick and easy way to check the tables and their attributes within. So here are all the information listed in each table:

Borders

Rows: 305

City

Rows: 3051

Continent

Rows: 5

Country

Rows: 195

Desert

Rows: 30

Economy

Rows: 195

Encompasses

Rows: 198

Ethnic_group

Rows: 520

Geo_desert

Rows: 58

Geo_island

Rows: 212

Geo_lake

Rows: 174

Geo_mountain

Rows: 100

Geo_river

Rows: 448

Geo_sea

Rows: 567

Is_member

Rows: 7766

Island

Rows: 162

Lake

Rows: 90

Language

Rows: 110

Located

Rows: 213

Merges_with

Rows: 34

Mountain

Rows: 87

Organization

Rows: 168

Politics

Rows: 195

Population

Rows: 195

Province

Rows: 1382

Religion

Rows: 406

River

Rows: 132

Sea

Rows: 22

B1

According to the study guide, a database system is one which:

Retrieves data

Insert new data

Delete unnecessary data

Update old data

Also, the study guide defines a database management system (DBMS) as a software used for defining, accessing and maintaining a database.

Therefore, by the above definitions, we cannot consider an online textbook plus search engine as a database or DBMS. The text search function is only capable of information retrieval. There is no data insertion, deletion of data, or updating of old data. Also, in a database, all data is structured, and categorized in proper rows and columns. On the other hand, the information in the online textbook is unstructured. Therefore, based on all this, the textbook search function cannot be considered as a database.

B2

A data entry clerk is an unskilled job.

Errors in computer data entry accounted for 13% of the 235000 medication errors, which is 27711 data entry errors.

Data entry error (the omission of a single letter in the commands issued to the probe) was the cause of failure.

The more skilled and experienced operators might be more complacent and pay less attention to their work as they tend to automate routine actions. Also while users are used to handling typing errors, the delete functions may not be similar across different word processors which could result in transfer errors.

71% to 98%

B3

10 tables x 500,000 rows x 8 columns x 8 characters or digits = 320,000,000 characters or digits entered.

Lowest error rate mentioned in paper: 0.02%

3200000000 x 0.02% = 64000 errors

320000000 x 0.5% = 1600000 errors

B4

4657-4686-6987-0760

Credit Card Number

4

6

5

7

4

6

8

6

6

9

8

7

0

7

6

0

X

Double every other

8

6

10

7

8

6

16

6

12

9

16

7

0

7

12

0

X

Sum of Digits

8

6

1

7

8

6

7

6

3

9

7

7

0

7

3

0

=85

4658-4686-6987-0760

Credit Card Number

4

6

5

8

4

6

8

6

6

9

8

7

0

7

6

0

X

Double every other

8

6

10

8

8

6

16

6

12

9

16

7

0

7

12

0

X

Sum of Digits

8

6

1

8

8

6

7

6

3

9

7

7

0

7

3

0

=86

4659-4998-6988-0760

Credit Card Number

4

6

5

9

4

9

9

8

6

9

8

8

0

7

6

0

X

Double every other

8

6

10

9

8

9

18

8

12

9

16

8

0

7

12

0

X

Sum of Digits

8

6

1

9

8

9

9

8

3

9

7

8

0

7

3

0

=95

B5

The Entity Integrity Constraint states that no primary key value can be null. Therefore, based on the schema for tables listed in part A of this coursework, the Entity Integrity Constraint is enforced on the Continent table as its primary key does not contain a null value.

The attribute integrity constraint is enforced on the Economy table as it contains a CHECK constraint for the GDP attribute.

As there are no foreign keys present in the Mondial database, it is impossible to tell whether Referential Integrity is enforced.

B6

B7

Groom ->> Horse

Horse -> Groom

Groom ->> Horse

Horse ->> Groom

Groom -> Horse

Horse -> Groom

Groom -> Horse

Horse ->> Groom

B8

Aphra and Betty is wrong. Reason being, there will be database errors should a student fail a subject, and will have to retake an exam the following year. The database will not be able to distinguish the student numbers and subjects from one year to the next.

Gemma is correct.

Bot is incorrect. For example, should a student fail and exam this year, and pass the exam next year, the database might not be updated correctly.

B9

B10

MUMPS

http://thedailywtf.com/Articles/A_Case_of_the_MUMPS.aspx

MUMPS, also known as Massachusetts General Hospital Utility Multi-Programming System, is a programming language developed in the late 1960s by Neil Pappalardo and his colleagues at Massachusetts General Hospital. According to its wiki article, MUMPS has an in-built database which supports and hierarchical structure made of sparse arrays. Currently, healthcare and financial sectors are 2 industries which still use MUMPS which make up their information systems. According to Papadimoulis (2007), similar to languages such as COBOL and FORTRAN, MUMPS is an ageing and obsolete programming language.

MUMPS supports only one universal datatype, meaning that data is coerced and parsed as a string, integer, or other datatypes as and when the context requires. Commands and functions may be abbreviated up to 3 characters in length. This aided multi-programming which resulted in better performance as programs are much more compact. However, as MUMPS programmers generally used their own abbreviations without commenting on code, it is extremely difficult for other programmers to understand and maintain the program without analysing the code line by line.

NoSQL

Unlike what the name implies, NoSQL is actually commonly interpreted as "not only SQL" rather than literally "no SQL". The first time the term NoSQL was used back in 1998 by Carlo Strozzi to describe his own derivative of the traditional relational database system, which does not utilize SQL. According to him, his NoSQL differs from the recent NoSQL movement in that the former is a well-defined software package and is relational, while the latter is a concept of a non-relational database system (which also follows the concept of not using SQL).

According to Finley (2012), the more recent NoSQL movement can be traced back to Google and Amazon. Both companies required new databases which were designed to run across a large number of servers in order to store and process 'big data'. Said 'big data' refers to a massive amount of data (in the scope of petabytes, exabytes, etc) which traditional relational databases could not cope. As a result, Google created BigTable, and Amazon created Dynamo to deal with 'big data' and support their expanding online services. Other companies then sought to replicate the database created by both companies after Google and Amazon published research papers detailing their respective databases. This led to the increase in popularity and implementation of NoSQL databases. Facebook is also another company which utilizes NoSQL databases.

So, what are the features of NoSQL that makes the concept so popular? As mentioned, NoSQL databases are needed when there is a need to manage 'big data' that usual relational databases solutions could not cope. Fowler and Sadalage (2012) reiterates that NoSQL does not adhere to the relational database model, and thus does not utilize SQL. NoSQL does not use any fixed schema, so any data can be stored in any record instead of fixed columns and rows. Also, NoSQL databases are built to stretch across multiple servers instead of being constrained to a single server in the case of a relational database. This is important for companies as it is much more expensive to buy or upgrade to a bigger server, while it is much more cost effective and efficient to scale horizontally by purchasing more machines instead (forming a large cluster called a 'cloud'). This implies that relational databases are much more expensive to maintain and upgrade than NoSQL databases. Furthermore, while the individual machines in the cloud may fail, the overall cluster remains reliable. Therefore, NoSQL servers are much more economic, scalable, and flexible compared to relational databases.

Fowler and Sadalage (2012) further explains that such clusters can help reduce developmental drag. Reason being, in most modern relational databases, much time is spent mapping the various relations. Thus by implementing a non-relational database in the case of NoSQL, in theory, development time can be greatly reduced. However, this can only apply in cases where a non-relational database would be preferred, and a relational database is still preferred for many conventional situations. Fowler and Sadalage (2012) also mentions that the large server clusters which support NoSQL databases are of an obviously much larger scale compared to relational databases, and is thus able to store a much more about of data (in the petabytes) and process and analyse that data much more efficiently.

However, as a relatively recent concept, NoSQL does have some drawbacks as well. Harrison (2010) summarizes the disadvantages of NoSQL into the following points: lack of maturity, inferior support (as compared to relational databases), difficulty of administration, less effective business intelligence, and a general lack of expertise among developers.

In conclusion, while NoSQL is much better than relational databases when handling big data, relational databases are still relevant today due to the maturity of the platform and the great support and expertise available. Furthermore, not all companies need to handle big data, and as such the relational database is still the most viable and conventional solution. For the companies that do need to deal with big data, they can certainly consider implementing NoSQL solutions to meet their business needs. However, as mentioned, the general lack of maturity, expertise, and support could be potential barriers to a company that is considering NoSQL solutions.