-
Paging
Paging in SQL Server 2012 allows you to customize how specific query results are returned to help you access relevant results quickly and accurately. In this video; Rafiq Wayani uses paging to limit the number of query results displayed at any given time so that they're displayed in pages that can be viewed more quickly.
-
Data Integrity
In data warehousing; data integrity is what ensures the accuracy and consistency of your data. In this video; Charles Robinson discusses how to ensure data integrity; where to apply it; and its importance.
-
Fact
In data warehousing; a fact table contains the measurable items of a particular business process. In this video; Amanda Jones discusses and examines the sales amount fact of the AdventureWorks Data Warehouse.
-
Factless Fact
In data warehousing; a factless fact is a fact table that does not contain a measure. In this video; Amanda Jones explains the meaning and purpose of a factless fact.
-
Granularity
In data warehousing; granularity is the level of depth of your data; or the level of detail in a particular fact or dimension. In this video; Charles Robinson demonstrates a few examples of granularity using the AdventureWorks Data Warehouse.
-
Joins
In data warehousing; a join is how you create the rules of how data is going to need to match in our tables in order to be included in our output. In this video; Charles Robinson demonstrates the different types of joins and their syntax; using SQL Management Studio.
-
Normalization vs. Denormalization
In data warehousing; data stored in tables can be either normalized or denormalized. In this video; Charles Robinson explains the difference between normalization and denormalization.
-
Schemas
In data warehousing; schemas are the outline or model for database design. The two main schemas used in dimensional modelling are the star and snowflake schemas. In this video; Amanda Jones discusses the star and snowflake schemas.
-
Collation Functions
The collation functions in Microsoft SQL Server 2012 provide sorting rules; cases; and accent-sensitive properties for the data. In this video; Jonathan Summers provides a description of collation in SQL Server before demonstrating how to use the COLLATIONPROPERTY and TERTIARY_WEIGHTS collation functions; as well as sp_helpsort procedure and the SERVERPROPERTY function.
-
Columnstore Indexes
Columnstore Indexes in Microsoft SQL Server 2012 can significantly speed-up the processing time of queries by grouping and storing data for each column and then joining the columns to complete the whole index. In this video; Jonathan Summers uses the Object Explorer component of SQL Server Management Studio to create a columnstore index.
-
Comments
In Microsoft SQL Server 2012; you can add comments to your SQL Server Integration Services (SSIS) packages so you can convey information to help make the source code easier to understand at a later date. In this video; David Dye demonstrates how to add and configure comments and annotations within a Data Flow and Script task.
-
Concatenate Function
In Microsoft SQL Server 2012; you can use the concat function inside of TSQL to join multiple strings together. In this video; Rafiq Wayani demonstrates how the concat function allows you to join two or more values.
-
Configure File Tables
Microsoft SQL Server 2012 allows you to create a SQL Server cluster by installing the Failover Clustering feature on your servers. In this video; Rafiq Wayani uses Server Manager to install Failover Clustering on both his servers before explaining the need for network cards for internal communications between the two failover clusters and for external communication.
-
Configure Firewall for FILESTREAM Access
Microsoft SQL Server 2012 requires that firewalls be configured on the server side and client side when FILESTREAM data is to be accessed from remote clients in a firewall protected environment. In this video; Jonathan Summers demonstrates how to configure Windows firewalls for FILESTREAM access.
-
Connection Managers
In Microsoft SQL Server 2012; SQL Server Integration Services (SSIS) includes a variety of connection managers for connecting data sources that you can create and configure at the package or project level. In this video; David Dye demonstrates how connection managers are defined at the project level; before creating and configuring connection managers for packages and within specific tasks.
-
Contained Database Access
In Microsoft SQL Server 2012; you can use the new contained databases (CDBs) and partially contained databases (PCDBs) features to limit access for certain users. In this video; Rafiq Wayani demonstrates how to create and access CDBs and PCDBs from Management Studio.
-
Contained Databases
Microsoft SQL Server 2012 has a new feature called partially contained databases; or partial-CDBs. In this video; Rafiq Wayani demonstrates how partial-CDBs are used and enabled.
-
Control Flow
Control flow in Microsoft SQL Server 2012 allows you to extract; transform; and load data. In this video; David Dye demonstrates the concept of control flow within an integration services package; the different tasks available within control flow; and the difference between data flow and control flow.
-
Create a Directory
Windows Azure Active Directory is a new Windows Azure service that you can use to create directories for Windows Azure SQL Database administration. In this video; Jonathan Summers leads you through the process of creating a new directory from within the Windows Azure Management Portal.
-
Create a Federation in Azure Database
Windows Azure SQL Database allows you to deliver scale; performance; and additional capacity in SQL Databases through horizontal partitioning when creating a federation. In this video; Jonathan Summers demonstrates how to add a federation to a database using the Windows Azure Platform Management Portal.
-
Create a Microsoft Support Ticket
Windows Azure SQL Database offers multiple support options and enables administrators; depending on their subscription types; to resolve issues beyond their control by submitting support tickets directly to Microsoft. In this video; Jonathan Summers demonstrates how to configure a support ticket; and introduces other forms of support for the Windows Azure SQL Database administrator.
-
Create a New Mobile Service
Windows Azure SQL Database enables administrators to create mobile services for use in the production environment; subject to local and Windows Azure firewalls being configured to allow mobile service connectivity. In this video; Jonathan Summers creates a new mobile service; providing a distinct URL for the back-end service and other required options.
-
Create a New Sync Agent
Windows Azure SQL Database enables you to create a Sync Agent to synchronize on-premise and Cloud-based databases. In this video; Jonathan Summers uses the Add Sync feature to create and configure a new Sync Agent as well as generate an access key; enabling the Sync Agent's registration with online databases.
-
Create a SQL Data Sync Group
Windows Azure SQL Database enables you to synchronize on-premise and Cloud databases by adding them to a Data Sync Group; to which you assign a Sync Agent. In this video; Jonathan Summers uses the Add Sync feature to create a data sync group and then configure the group's sync direction and writable database.
-
Create a Table for Storing FILESTREAM Data
In Microsoft SQL Server 2012; once you have created or modified a database to contain a FILESTREAM filegroup; you can then create a table to store FILESTREAM data. In this video; Jonathan Summers uses the Query Editor in SQL Server Management Studio to create a FILESTREAM-enabled table.
-
Create a Virtual Machine
Windows Azure SQL Database allows you to quickly and easily create a virtual machine; which is a server in the cloud that you can access and manage just like any other server. In this video; Jonathan Summers uses the Quick Create method to create a virtual machine in the Windows Azure Management Portal.
-
Create Access Control Namespaces
Windows Azure SQL Database allows you to create; configure; and activate an Access Control Namespace from your Management Portal. This allows you to manage Access Control Service features; such as the integration of identity federation and single sign-on into your web applications. In this video; Jonathan Summers creates an Access Control Namespace and introduces various Access Control Services.
-
Create and Alter FileTables
Microsoft SQL Server 2012 allows you to create and alter FileTables – a new feature that provides compatibility between Windows applications and the file data stored in SQL Server. In this video; Jonathan Summers demonstrates how to create a FileTable using two different methods; as well as how to change the FileTable directory using the ALTER TABLE statement.
-
Create Data Storage
Windows Azure SQL Database allows you to create a data storage account and the encrypted online storage you need to import and export databases. In this video; Jonathan Summers creates data storage as well as the primary and secondary access keys; allowing access from outside of the Windows Azure subscription.
-
Create Share Drive for Cluster
Microsoft SQL Server 2012 allows you to create a shared drive which; when failover services have been installed on your Windows environment; lets you create a SQL Server cluster. In this video; Rafiq Wayani demonstrates how to configure your machines to communicate with an iSCSI disk; enable Failover Clustering on both machines; and install SQL Server.
-
Creating a FILESTREAM Database
In Microsoft SQL Server 2012; you can create a FILESTREAM enabled database to help store and manage unstructured data. In this video; Jonathan Summers uses the query editor in SQL Server Management Studio to create a FILESTREAM database.
-
Creating a SQL Database Server in Azure
Windows Azure SQL Database enables you to provision new SQL database servers for your platform subscription from the Windows Azure Management Portal. In this video; Jonathan Summers demonstrates how to create and configure a Web edition database and new SQL database servers.
-
Creating a Virtual Network
Windows Azure SQL Database enables you to build and configure a virtual network comprising multiple virtual servers from the Windows Azure Management Portal. In this video; Jonathan Summers demonstrates how to create and configure a virtual network by specifying DNS servers; configuring VPN connectivity; and defining virtual network address spaces.
-
Creating a Website
Windows Azure SQL Database offers Quick; Custom; and Gallery web site options you can connect to your Azure web database server. The Gallery's predefined site templates are categorized and cover a range of site types. In this video; Jonathan Summers explores site options from the Windows Azure Management Portal and assign a URL to a template.
-
Creating a Windows Azure Profile
Windows Azure SQL Database allows you to use Internet Explorer to quickly and easily set up an account and user profile; thereby enabling you to access the Windows Azure portal where you are presented with several subscription options. In this video; Jonathan Summers leads you through the process of creating a Windows Azure user account.
-
Creating SSIS Catalog
Microsoft SQL Server 2012 allows users to create a SQL Server Integration Services (SSIS) Catalog to deploy projects or packages to the Integration Services server. In this video; David Dye demonstrates how to create an SSIS Catalog in the SSISDB; and discusses the project-based deployment method.
-
Credentials and Server Roles
New to Microsoft SQL Server 2012 is the ability to easily and quickly create new user-defined server roles and credentials using Transact-SQL. In this video; Rafiq Wayani demonstrates how to create and configure a server role and credential; and assigns members to the server role through the Properties interface.
-
Cryptographic Functions
In Microsoft SQL Server 2012; there are two cryptographic hash functions you can use to provide data security when sending or receiving information. In this video; Jonathan Summers demonstrates how encryption hashing can be used to generate hash values on different types of algorithms.
-
CTE Functions
Microsoft SQL Server 2012's Common Table Expressions (CTEs) are named expressions or temporary result sets – not stored as objects – defined in and confined to the scope and lifespans of single queries. In this video; Jonathan Summers details the syntax; role; and uses of CTEs; creating a query wherein the WITH and AS clauses define the CTEs.
-
Custom Logging
Microsoft SQL Server 2012 Integration Services provides a custom logging feature that users can specify to deal with logging information that falls outside the built-in log providers and event handlers. In this video; David Dye demonstrates how to implement custom logging within a package.
-
Data Compression
Microsoft SQL Server 2012 supports row and page compression for tables and indexes allowing you to reduce the size of data inside a database. In this video; Rafiq Wayani introduces Unicode and page-level data compression; explains that data compression saves storage space but drains CPU resources; and implements row-level compression on a database table.
-
Data Conversion
Microsoft SQL Server 2012 Integration Services' Data Conversion transformations allow you to convert the data in an input table to a different data type and copy it to a new output table. In this video; David Dye uses a Data Conversion transformation to copy data from a flat file ASCII Source to a Unicode Destination.
-
Data Flow
The data flow task in Microsoft SQL Server 2012 is an integral component of data management. In this video; David Dye demonstrates the data flow task within integration services; highlighting the difference between data flow and how the data flow invokes different components within a control flow task.
-
Data Flow Redirection
In Microsoft SQL Server 2012; instead of letting failures stop package execution; you can redirect the failed row to an alternate destination where the error can be examined and processed at a later time. In this video; David Dye demonstrates how to redirect a row containing an error within a Data Flow task to a flat file destination.
-
Data Profiling
In Microsoft SQL Server 2012; you can use the Data Profiling Control Flow Task to analyse data from tables to verify specified information. In this video; David Dye demonstrates how to define different types of data profiling requests before analysing the results.
-
DATETIMEOFFSETFROMPARTS
The new DATETIMEOFFSETFROMPARTS function in Microsoft SQL Server 2012 returns a datetimeoffset value – wherein OFFSET accounts for time-zone difference in hours and minutes - for a specified date and time with the specified precision. In this video; Jonathan Summers details the function's parameters and values before compiling an executable query.
-
DBCC CHECKALLOC
Microsoft SQL Server 2012's DBCC CHECKALLOC command checks disk-space allocation structures and page usage in a database; including indexed views. In this video; Jonathan Summers clarifies the role of the NOINDEX and REPAIR options; details CHECKALLOC syntax; and runs DBCC CHECKALLOC on multiple databases as well as a single database.
-
DBCC CHECKCATALOG
Microsoft SQL Server 2012 uses Data Definition Language (DDL) triggers that fire in response to various DDL events. In this video; Jonathan Summers creates DDL event triggers to prevent users from creating; altering; or dropping tables.
-
DBCC CHECKTABLE
Microsoft SQL Server 2012's DBCC CHECKTABLE command checks and reports on the integrity of all pages making up a table or indexed view. Errors require a database restore rather than running a REPAIR; which might result in data loss. In this video; Jonathan Summers runs a regular and low-overhead DBCC CHECKTABLE command on a database table and a table index.
-
DBCC INDEXDEFRAG
Microsoft SQL Server 2012's DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes; therefore improving index-scanning performance. In this video; Jonathan Summers explains how DBCC INDEXDEFRAG operates on an index; reviews an index's fragmentation; and runs INDEXDEFRAG.
-
DBCC SHOW_STATISTICS
Microsoft SQL Server 2012's DBCC SHOW_STATISTICS command displays current query optimization statistics for a table or index view. The query optimizer uses these statistics to estimate the number of resulting rows; which enables it to create a high-quality query plan. In this video; Jonathan Summers runs DBCC SHOW_STATISTICS on the index of a table and explains the HISTOGRAM option.
-
DDL Triggers and EVENTDATA
Microsoft SQL Server 2012 uses Data Definition Language (DDL) triggers that fire in response to various DDL events. In this video; Jonathan Summers creates DDL event triggers to prevent users from creating; altering; or dropping tables.
-
Debugging
Microsoft SQL Server 2012 allows users to debug Integration Services packages by setting breakpoints within the packages; enabling them to evaluate system and user-defined variables at those points in time. In this video; David Dye demonstrates how to set breakpoints; add variables and statements to a watch list; and stop execution at specific points based on event handlers.
-
Define AlwaysOn
In Microsoft SQL Server 2012; you can use the AlwaysOn feature to ensure your server access is running at maximum efficiency; even if a failure occurs. In this video; Rafiq Wayani demonstrates the principle of nines; and how the AlwaysOn feature can be used to maintain high availability of your servers or datacenter.
-
Delay Validation
In Microsoft SQL Server 2012; DelayValidation is a property of a SQL Server Integration Services (SSIS) package and all of its components that you can use to delay validation until runtime. In this video; David Dye demonstrates the differences between DelayValidation set to True and False; before discussing the use of DelayValidation when a table only gets created on execution.
-
Deploy AlwaysOn
AlwaysOn Availability Groups is a new high-availability feature in Microsoft SQL Server 2012 you can deploy as a way of providing an enterprise-level alternative to database mirroring. In this video; Rafiq Wayani demonstrates how to enable AlwaysOn in SQL Server Configuration Manager before using the New Availability Group Wizard to configure a new availability group.
-
Derived Column
Microsoft SQL Server 2012 allows you to use the Derived Column transformation to add an entirely new column based upon an Integration Services expression. In this video; David Dye demonstrates how to derive a column called FullName from the FirstName; MiddleName; and LastName columns; using Integration Services rather than a T-SQL statement.
-
Detect Running Package
In Microsoft SQL Server 2012; you can implement a script task to detect if a package is already running to prevent conflicts caused by the package running multiple times. In this video; David Dye demonstrates how to configure a script task to assign a global unique identifier to a package to prevent it from executing again if it's already running.
-
Discontinue Feature WITH APPEND
Microsoft SQL Server 2012's discontinuance of support for the WITH APPEND clause affects scripts migrated from earlier versions of SQL Server. In this video; Jonathan Summers explains the implications of the discontinuation; searches for and deletes the clause from a script on SQL Server 2012; and executes it successfully.
-
Dynamic Connections
Microsoft SQL Server 2012 Integration Services allow you to manage data flows using dynamically-generated connection managers based on either variables or parameters. In this video; David Dye uses a connection manager in conjunction with a FOREACH Loop Container and a variable mapping to the fully-qualified names of a specific file type; to iterate through the files in a database collection.
-
Dynamic Management Function
Microsoft SQL Server 2012's Dynamic Management Views and Functions return SQL Server runtime-state information that is used to monitor SQL Server health during runtime; troubleshoot the performance of bottleneck issues; and proactively work to minimize downtime. In this video; Jonathan Summers runs a query containing one of SQL Server 2012's new dynamic management functions.
-
Dynamic SQL Statement
Microsoft SQL Server 2012 allows users to replace Dynamic SQL statements with expressions and Integration Services variables. In this video; David Dye demonstrates the steps to replace statements with expressions and variables; as well as how to use the variables within a data flow or Execute SQL task.
-
Efficient Index
In Microsoft SQL Server 2012; poorly designed indexes have a negative impact on your overall database performance. Designing efficient indexes will help speed the retrieval of rows from tables and help you to improve the effectiveness of your application. In this video; Rafiq Wayani demonstrates the efficiencies of a clustered index scan compared to a table scan.
-
Enable FILESTREAM
Microsoft SQL Server 2012's FILESTREAM feature ensures that your applications can leverage the rich streaming APIs and performance of the file system while maintaining transactional consistency between unstructured data; stored in the NT File System; and structured data; stored in the table. In this video; Jonathan Summers prepares a database to accommodate FILESTREAM.
-
Enable or Disable Change Tracking
Microsoft SQL Server 2012's Change tracking is a lightweight solution and efficient change-tracking mechanism for applications that records INSERT; UPDATE; and DELETE activity applied to track tables - supplying the details in easily-consumed rational format. In this video; Jonathan Summers demonstrates how to enable and disable Change tracking at the database and table levels.
-
Execute Jobs Using Agent
In Microsoft SQL Server 2012; SQL Server Agent allows you to create and execute jobs that automate database administration. In this video; Rafiq Wayani uses SQL Server Management Studio's New Job dialog box to create a job before scheduling the new job and confirming its status in Event Viewer.
-
Execute Package
In Microsoft SQL Server 2012; you can execute a package from within another package to help maintain logic consistency. In this video; David Dye demonstrates how to enable and configure the parent variable configuration using the Execute Package Control Flow task so you can pass a variable from a parent package to a child package.
-
Execute Package Procedure
In Microsoft SQL Server 2012; the SSIS catalog provides various packages that can be executed using system stored procedures. In this video; David Dye demonstrates how to execute a package using a system stored procedure available within the SSIS database; and discusses the requirements and configuration steps necessary to execute a package using the stored procedure.
-
Execute Process
Microsoft SQL Server 2012's Execute Process Task allows you to execute executables; batch files; or command files as long as the appropriate arguments are passed in its configuration. In this video; David Dye uses Microsoft Visual Studio to show you how to configure and provide arguments allowing the Execute Process Task to call and execute a package's dtexec utility.
-
Execute SQL Task
In Microsoft SQL Server 2012; the Execute SQL task allows you to run T-SQL statements or stored procedures from a SQL Server Integration Services (SSIS) package in a control flow. In this video; David Dye demonstrates how to configure an Execute SQL task with a T-SQL statement in conjunction with a Data Flow task to create and populate a table.
-
Executing a Package
In Microsoft SQL Server 2012; you can execute a package using a number of different methods. In this video; David Dye demonstrates how to execute a package from the command line; from Visual Studio or SQL Server Data Tools; from Management Studio; and from the Execute Package Utility.
-
Export Column
In Microsoft SQL Server 2012; you can configure the Export Column Transformation and use the task in various situations. In this video; David Dye uses the Export Column Transformation task to convert a large amount of data into a text stream and export it from the database to an external file.
-
Export SQL Database
Windows Azure SQL Database enables you to export a database; given a BACPAC extension and accessible from your local SQL Server instance; to a container in your storage account location. In this video; Jonathan Summers uses Windows Azure SQL Database’s Export feature to export a database to a BLOB storage account container.
-
Expression Size
Microsoft SQL Server 2012 Integration Services (SSIS) allows users to increase variable sizes to overcome limited expression size. In this video; David Dye demonstrates how to overcome the 4000-character limitation on expression size by creating large variables and concatenating the expressions.
-
Fail Parent
In Microsoft SQL Server 2012; you can determine whether or not a parent container fails when a child container fails by configuring the FailParentOnFailure property and Propagate variable. In this video; David Dye demonstrates how to configure the FailParentOnFailure property; and how to suppress errors at the parent by configuring the Propagate system variable.
-
Failover Mode
In Microsoft SQL Server 2012; there are a number of modes the failover cluster can exist in depending on the type of failover event that occurs. In this video; Rafiq Wayani demonstrates conceptually how automatic synchronous; automatic asynchronous; and forced manual asynchronous failover modes can be implemented.
-
Failover Multi-Subnet
Microsoft SQL Server 2012 allows you to use the cluster validation process to validate the computers on the network on which you wish to install your SQL Server cluster. In this video; Rafiq Wayani uses the Failover Cluster Manager's Validate a Configuration Wizard to name and validate his machines' hardware.
-
File System
Microsoft SQL Server 2012 Integration Services' File System Task enables you to create; move; delete and/or set the standard attributes of directories and files. In this video; David Dye highlights the File System Task's inability to access extended file or directory properties from within the file system; before using it to copy a directory.
-
Filestream
In Microsoft SQL Server 2012; you can utilise FileStream to store unstructured data from a database inside the directory structure in a file system. In this video; Rafiq Wayani demonstrates how to enable and get the most out of using FileStream.
-
For Each
Microsoft SQL Server 2012 Integration Services allow you to use a Foreach Loop Container and variable to iterate through; and enumerate; a defined collection. In this video; David Dye uses a File Loop Container to iterate a file collection of defined file types; the fully qualified filenames of which are assigned to a variable and enumerated before deletion.
-
For Loop
In Microsoft SQL Server 2012; you can use a For Loop container to continue executing an expression based on a defined collection until a specific condition is met. In this video; David Dye demonstrates how to initialize an expression in a For Loop container and define the evaluation and assignment conditions.
-
Format and Convert
In Microsoft SQL Server 2012; you can easily format and convert data settings and formats. In this video Rafiq Wayani demonstrates how you can format date and time settings using locale aware formatting; and convert data using type conversions.
-
FSIS If Expression
Microsoft SQL Server 2012 Integration Services allow you to implement an IF expression as a CASE expression; which is not otherwise supported. In this video; David Dye demonstrates how to create a package comprising a Case string variable based on an expression; containing two integer data type variables; and details the expression's syntax before executing the package.
-
Full Text Index
In Microsoft SQL Server 2012; you can create full-text indices to store and retrieve information. In this video Rafiq Wayani demonstrates a number of different ways in which to create full-text indices for data storage to make it easier to locate information.
-
How to Configure Logical Server Firewall Settings
After a logical server has been created on Windows Azure SQL Database; you will need to configure the firewall for the server to allow inbound connections from your IP address when communicating with the SQL Database in the cloud. In this video; Jonathan Summers uses settings in the Windows Azure Management Portal to specify firewall rules for a logical server.
-
How to Reset SQL Server Database Password
Windows Azure SQL Database enables administrators to reset their server account credentials from the Server dashboard. In this video; Jonathan Summers launches the relevant Server dashboard and resets the password.
-
How to Scale a SQL Database Solution
In Windows Azure SQL Database; large databases sometimes require a scale out strategy when addressing issues related to capacity or performance that can be achieved through federation. However; increasing the size of the database is sometimes the best approach. In this video; Jonathan Summers outlines some of the ways to scale a Windows Azure SQL Database.
-
How to View Import and Export Status of a Database
In Windows Azure SQL Database; you can monitor the amount of processes and resources used when you import or export a database. In this video; Jonathan Summers demonstrates how to query the performance and view the properties of an imported or exported database.
-
Iif and Choose
In Microsoft SQL Server 2012; you can use the Inline IF (iif) and choose logical functions to create multiple statements within statements. In this video; Rafiq Wayani demonstrates some of the applications for the Inline IF and choose functions; and how to create statements within statements.
-
Implement Index
In Microsoft SQL Server 2012; you can speed up the querying process by creating indexes on columns in tables that; when implemented; provide swift access to rows in the data tables. In this video; Rafiq Wayani demonstrates how to index a column in a data table and highlights the advantage of implementing an index while performing a table scan.
-
Import a SQL Database
Windows Azure SQL Database enables you to import a database; given a BACPAC URL and exported from your local SQL Server instance; into your Windows Azure virtual server. In this video; Jonathan Summers uses the Windows Azure Import feature to import a database into a Cloud storage container and specify its edition and size.
-
Index Type and Structures
Microsoft SQL Server 2012 supports clustered; non-clustered; and composite indexes; the different structures of which allow you to access data at different speeds. In this video; Rafiq Wayani demonstrates the structural and data-access speed differences separating the different types of index; and creates an example of each using Microsoft SQL Server Management Studio.
-
Install SQL on Cluster
Microsoft SQL Server 2012 provides configuration options for installing SQL Server on a cluster. In this video; Rafiq Wayani demonstrates how to install SQL Server on a Windows cluster that has already been created and validated.
-
Is Stored Procedure
Microsoft SQL Server 2012 Integration Services apply different parameter marker rules for stored procedures on different connections. In this video; David Dye explains that ADO or ADO.NET connections need only declare their stored procedures when their properties are set to True; but must use OLE DB syntax when set to False.
-
Join Hints
Microsoft SQL Server 2012's Join hints; used with LOOP; HASH; MERGE; and REMOTE arguments; specify that the query optimizer; which typically selects the best execution plan for a query; enforces a join strategy between two tables. In this video; Jonathan Summers compiles and executes a JOIN to extract desired data from two tables.
-
Locating Unused Indexes
Microsoft SQL Server 2012 index maintenance consumes significant resources; degrading performance in a write-intensive system. You can identify and remove indexes not being used with the sys.dm_db_index_usage_stats Dynamic Management View (DMV). In this video; Jonathan Summers searches for unused indexes by running the sys.dm_db_index_usage_stats DMV.
-
Logging
Microsoft SQL Server 2012 Integration Services allow you to configure performance and error logs at the package level and support log providers that write to text; SQL Server; and other log files. In this video; David Dye demonstrates how to configure logging at the package level; executes the package; and reviews the sysssislog table.
-
Lookup
In Microsoft Visual Studio 2012; you use the Lookup Transformation to join data in input columns with columns in a reference dataset. In this video; David Dye demonstrates how to use Lookup Transformation to configure a source that connects the Lookup to the database.
-
Maintenance Plans
In Microsoft SQL Server 2012; you can create Database Maintenance Plans in either SQL Server Management Studio or within SQL Server Data Tools. In this video; David Dye demonstrates how database maintenance plans are actually integration services packages that can be configured in Management Studio or within SQL Server Data Tools.
-
Manage Alerts
In Microsoft SQL Server 2012; you can define and manage alerts that specify how you want SQL Server Agent to respond to event occurrences. In this video; Rafiq Wayani demonstrates how to configure an alert using the SQL Server Management Studio Alert Properties dialog box before using Windows PowerShell to script a new event and an event log.
-
Manage File Tables
Microsoft SQL Server 2012 allows you to easily store and manage unstructured data. In this video; Jonathan Summers demonstrates how to manage filetables using SQL Server Management Studio.
-
Manage Settings in SQL Database
In Windows Azure SQL Database; each database has its own logins and specifically delegated administrators; which can be managed using the Settings feature. In this video; Jonathan Summers demonstrates how to add an administrator to a database subscription; specify filters for event logs; and create an affinity group.
-
Managing Errors
Microsoft SQL Server 2012 has traditionally used the @@ERROR automatic variable to implement error handling. In this video; Rafiq Wayani explains that @@ERROR always contains a value greater than zero; is reset immediately on the next statement executing successfully; and uses RAISEERROR to capture the error number to a variable.
-
Managing SQL Databases from Outside Management Portal
With Microsoft Windows Azure SQL Databases; you have two options to connect to and manage your databases outside the management portal. In this video; Jonathan Summers demonstrates how to connect to your database directly through the Internet; or using SQL Server Management Studio.
-
Manual Failover
Microsoft SQL Server 2012 allows you to implement a manual failover to a failover cluster in order to patch the server on which you host SQL Server. In this video; Rafiq Wayani uses the Failover Cluster Manager to introduce the node services and applications; storage devices; and Microsoft iSCSI Software Target configuration required to implement and test a manual failover.
-
Media Services and SQL Database
Windows Azure SQL Database provides a number of tools and options that allow the database to stream media using media services. In this video; Jonathan Summers demonstrates how to create a new media for your Azure SQL Database.
-
Merge Data
In Microsoft SQL Server 2012; the Transact-SQL MERGE statement allows you to insert; update; or delete data in a target table based on data in a source table; all in one easy statement. In this video; Rafiq Wayani demonstrates how to create and populate a target and source table; and merge data into the target table.
-
Merge Join
In Microsoft Visual Studio 2012; you can use Merge Join Transformation to join data from a table and data from a flat file based on matching criteria. In this video; David Dye demonstrates how to join data and configure the Merge Join Transformation task in integration services.
-
Migrate Data
Microsoft SQL Server 2012 provides configuration options for migrating data between server instances and also between different servers. In this video; Rafiq Wayani explains how to migrate data from one environment over to another.
-
Migrate Logins
Microsoft SQL Server 2012 allows you to migrate Logins from one database to another when switching servers or creating new databases or database instances. In this video; Rafiq Wayani uses Microsoft SQL Server Management Studio to migrate a group of users and a group of logins from one database to another.
-
Virtualizing SQL Server
You can quickly and easily allocate a range of resources to Microsoft SQL Server 2012 virtual machines depending on your requirements. In this video; Rafiq Wayani demonstrates how to add more RAM to a virtual machine based in a Datacenter.
-
Web Service Task
Microsoft SQL Server 2012 Integration Services' Web Service Task allows you to execute a Web service method; which has its returned values contained in an XML file. In this video; David Dye configures his HTTP connection manager to point to a WSDL file which he downloads before configuring and executing the Web Service Task so it returns an XML file.
-
Where Packages are Stored
In Microsoft SQL Server 2012; you can use different models to deploy packages and store them within the file system or in the database. In this video; David Dye demonstrates a couple of ways the package deployment model stores packages in the package store and in the database; and discusses where the project deployment model stores its deployed packages.
-
Windows Azure Add-On Store
In Windows Azure SQL Database; you can access a wide range of additional tools from the Windows Azure Add-On Store. In this video; Jonathan Summers demonstrates how to install a third-party add-on for use in your database.
-
Windows Azure SQL Database Backup
Backing up your Windows Azure SQL Database is a necessary process when protecting against data loss; and it requires a different strategy than when performing an on-premises SQL Database backup. In this video; Jonathan Summers demonstrates how to automate the process of backing up a Windows Azure SQL Database by executing a query in the Management Portal.
-
Windows Azure SQL Database Reporting
In Windows Azure SQL Database; you have access to a range of reporting features similar to those in on-premise SQL installations. In this video; Jonathan Summers demonstrates how to configure and customize SQL reporting options using Windows Azure Data Services and the dashboard.
-
WMI Data Reader
The Windows Management Instrumentation (WMI) Data Reader in Microsoft SQL Server 2012 allows you to return information on an actual physical system. In this video; David Dye demonstrates the ability of the WMI Data Reader task to query and provide information on underlying systems.
-
XML Control Flow
Microsoft SQL Server 2012 Integration Services allows you to pass an XPath query against an XML Control Flow Task. In this video; David Dye uses an XML Control Flow Task; its File connection; and Source; to pass an XPath query against an element in an XML file; and assigns the returned value to a variable.
-
XML Source
Microsoft SQL Server 2012 provides various configuration options for utilizing an XML source within a data flow; as well as the ability to create a schema based upon an input XML file. In this video; David Dye demonstrates how to use the XML source to create a schema.
-
Migrated to New Instance
Microsoft SQL Server 2012 allows you to migrate databases to new instances; whether by detaching and attaching them; or by backing up your database; dropping it; and restoring it to the new instance. In this video; Rafiq Wayani uses Microsoft SQL Server Management Studio's menu-driven Object Explorer and its resulting dialog boxes to demonstrate both methods and their procedures.
-
Mirror
Microsoft SQL Server 2012 databases can be mirrored in a virtual environment for fault tolerance and backup. In this video; Rafiq Wayani demonstrates how to use VMWare to create a read-only copy of a virtual machine running SQL Server 2012 on a secondary virtual machine.
-
Mirror and Certificate Authentication
Microsoft SQL Server 2012 allows you to implement mirroring utilizing Certificate-based Authentication rather than Windows Authentication. In this video; Rafiq Wayani demonstrates the benefits of using Certificate-based Authentication; explains the need for a secondary server on which the mirror can be implemented; configures his database to use the full recovery model; and launches the Configure Database Mirroring Security Wizard.
-
Mirror Setup
Microsoft SQL Server 2012 allows users to use replication; as well as mirroring; to ensure fault tolerance across a database environment. In this video; Rafiq Wayani discusses various methods of implementing mirroring; either through SQL Server 2012 or outside it.
-
Mirror with Windows Authentication
Microsoft SQL Server 2012 allows you to create a mirror utilizing Windows Authentication which you can integrate with Active Directory. In this video; Rafiq Wayani uses Microsoft SQL Server Management Studio to introduce the database properties and mirroring components needed to enable mirroring; the Database Mirroring Monitor; and Windows Authentication.
-
Monitor
Monitoring Microsoft SQL Server 2012 allows you to keep track of your databases and provides you with valuable information when fine tuning performance; or troubleshooting performance issues. In this video; Rafiq Wayani demonstrates the monitoring features of the Windows Resource Monitor; and how to monitor log files in the Log File Viewer.
-
More on Advanced Sequences
In Microsoft SQL Server 2012; you can use the same sequence to supply data across multiple tables. In this video; Rafiq Wayani demonstrates how to link multiple tables to a sequence using the union operator to display the sequence audit results across these tables.
-
More on Mirror Setup
Microsoft SQL Server 2012 and Windows Server 2008 R2 allow users to mirror databases for backup purposes and redundancy. In this video; Rafiq Wayani demonstrates the important differences between replication and mirroring; pointing out that performance issues preclude mirroring as a viable failover option.
-
More on Replication Setup
Microsoft SQL Server 2012 allows users to use replication to create a fully current secondary server for redundancy. In this video; Rafiq Wayani demonstrates how to set up the replication process and two methods of implementing redundancy by using an active; online secondary server or by bringing an offline secondary server online.
-
More on Sequences
Microsoft SQL Server 2012 allows you to use the NEXT VALUE FOR statement to create an integer sequence number that increments by one. In this video; Rafiq Wayani demonstrates the use of the NEXT VALUE FOR statement; and explains that sequences allow you to perform checking or implement advanced batch routines as part of a single transaction.
-
More on SQL Backup
In Microsoft SQL Server 2012; there are different ways of implementing backups that will protect you from data loss and assist when performing routine administrative tasks; such as copying a database from one server to another. In this video; Rafiq Wayani demonstrates some of the different options available to you when using SQL Management Studio to back up a database.
-
More on Using SQL Agent
In Microsoft SQL Server 2012; the SQL Server Agent is a Windows Service that allows you to automate and schedule a specified series of actions called jobs. In this video; Rafiq Wayani uses the New Job dialog box in SQL Server Management Studio to create and configure a new SQL Server Agent job.
-
Move a File Stream Enabled Database
For maintenance purposes; in Microsoft SQL Server 2012; it may be necessary to move a database from one hard disk location to another. In this video; Jonathan Summers demonstrates how to move an active database to a new hardware location.
-
Move Encrypted Data
Microsoft SQL Server 2012 allows users to move secure data from one location to another. In this video; Rafiq Wayani demonstrates how to create a database; populate it; encrypt the data; create a backup to disk; and drop the original database.
-
Moved On Another Instance
In Microsoft SQL Server 2012; you can move or copy databases between clusters or database instances. In this video; Rafiq Wayani demonstrates how you can move and copy multiple database instances in order to have redundancy.
-
Multicast
Microsoft SQL Server 2012 Integration Services' Multicast transformation allows you to create logical copies of input data and distribute them to one or more outputs. In this video; David Dye uses a Multicast transformation to copy a table from an OLE DB source to multiple types of destination.
-
Multi-Statement Table Valued Function
Microsoft SQL Server 2012's multi-statement table-valued functions force a query to return a table structure as its value. In this video; Rafiq Wayani demonstrates how to create a multi-statement table-valued function wherein he employs user-defined functions to specify the construction of the returned table.
-
New Features
Microsoft SQL Server 2012 provides sequences that can be used instead of identity columns because they are not table-bound structures. In this video; Rafiq Wayani demonstrates how to create a sequence that generates a sequence of numeric values which can be used for database-wide sequential numbers.
-
Object Permissions
Microsoft SQL Server 2012 allows you to easily grant and manage permissions for individual database objects; such as user-defined server roles; using Transact-SQL. In this video; Rafiq Wayani demonstrates how to grant insert permissions to an object; and highlights how to manage the permissions through the object's Properties options.
-
OLEDB Connection in Script
Microsoft SQL Server 2012 Integration Services allow you to create an OLE DB connection manager in a Script task to implement a connection. In this video; David Dye demonstrates how to program an OLE DB connection manager by parsing out an ADO.NET ConnectionString to assign the appropriate components to his script; thereby instantiating an OLE DB database connection.
-
On Error Event Handler
In Microsoft SQL Server 2012; the Integration Services (SSIS) OnError event handler provides a means of proactively handling of OnError events that are raised by an event when errors occur. In this video; David Dye demonstrates how to use OnError event handlers to capture package errors and pass them on to other tasks to provide in-depth information on package failures.
-
Operating Modes
Microsoft SQL Server 2012 allows you to implement mirroring in multiple operating modes; including High performance; High safety; Transaction safety; and Witness (used in conjunction with High safety). In this video; Rafiq Wayani demonstrates the roles and properties of the different SQL Server mirroring operating modes and how they complement or interact with each other.
-
Output Action Merge Data
Microsoft SQL Server 2012 allows you to retrieve and view the values of rows affected by a MERGE statement. In this video; Rafiq Wayani modifies a source table before using the Transact-SQL OUTPUT clause and the $action argument in a MERGE statement to return INSERT and UPDATE values.
-
Package Deployment
Microsoft SQL Server 2012 provides a choice of package deployment models: either as single packages or project-wide deployment. In this video; David Dye demonstrates the difference between package and project deployment; and how to configure the steps for package deployment.
-
Percentage Sampling
In Microsoft SQL Server 2012; the SQL Server Integration Services (SSIS) Percentage Sampling transformation allows you to create a sample data set by selecting a percentage of rows from a source data set. In this video; David Dye uses the Percentage Sampling Transformation Editor dialog box to configure a sample output using a percentage to specify the sample size.
-
Pivot
In Microsoft SQL Server 2012; you can use the Pivot transformation task within Integration Services to pivot table data for less system overhead than using the pivot operator. In this video; David Dye demonstrates how to implement and configure a Pivot transformation on a table in the Production database.
-
Precedence Constraints
Precedence constraints in Microsoft SQL Server 2012 help you manage control flow based on the outcome of specific tasks. In this video; David Dye demonstrates how precedence constraints provide the ability to control the flow of a package based upon task outcome.
-
Precedence Constraints Variables
In Microsoft SQL Server 2012; precedence constraints allow you to link executables; containers; and tasks in SSIS packages within a control flow and determine whether an executable runs by specifying variables. In this video; David Dye demonstrates how to use variables in an expression while defining the control flow of SSIS packages and utilizing precedence constraints.
-
Process Cube
Microsoft SQL Server 2012 Integration Services' Analysis Services Processing Task allows you to process a multi-dimensional Analysis Services cube. In this video; David Dye uses the Analysis Services Processing Task to process a cube's dimensions before performing a full process on the cube; and configures and schedules an Integration Services Task to process the cube for him.
-
Project Deployment
Microsoft SQL Server 2012 provides project deployment to deploy all packages; parameters; and configurations in a project; rather than just single packages. In this video; David Dye demonstrates how to implement project deployment using a Wizard; and how to verify deployment and view inherent logging.
-
Propagation
Microsoft SQL Server 2012 Integration Services allow you to stop events propagating or bubbling up to the container or package level. In this video; David Dye demonstrates how propagation bubbles up exponentially through every event and task to the package level; and stops it by defining Propagate as False at each level.
-
Query Hints
In Microsoft SQL Server 2012; hints are options or strategies specified for enforcement by the SQL Server query processor. In this video; Jonathan Summers demonstrates the MERGE UNION operation to override the query optimizer's execution plan.
-
Querying SSIS Catalog
In Microsoft SQL Server 2012; you can query the Integration Services Catalog to access a range of information. In this video; David Dye uses stored procedures to execute a package from within the Integration Services Catalog and automatically log the information to the SSIS database.
-
Raw File
Microsoft SQL Server 2012 Integration Services' raw file binary data format needs no translation and minimal parsing; is native to its source and destination; traverses the server's memory; and eliminates the need for temporary tables or table variables. In this video; David Dye configures a Data Flow Task with a Raw File Source and Destination; and views the file data.
-
Readable Secondary Replicas
In Microsoft SQL Server 2012; AlwaysOn Availability Groups include support for readable secondary replicas that allow read-only access to all its secondary databases in near real-time. In this video; Rafiq Wayani demonstrates the properties of a readable secondary replica and the benefits associated with directing read-only connections to readable secondary replicas.
-
Rebuild and Reorganize Indexes
Microsoft SQL Server 2012 allows you to rebuild and reorganize indexes. In this video; Jonathan Summers demonstrates how to remedy index fragmentation by reorganizing or rebuilding an index using both Object Explorer in SQL Server Management Studio; and TSQL code.
-
Record Set
Microsoft SQL Server 2012 Integration Services' Recordset Destination allows you to create and populate an in-memory ADO recordset written to an Object variable at runtime. In this video; David Dye populates a recordset written to an Object variable and enumerates it with a Foreach Loop Container that writes its values to defined variables.
-
Replicate
Microsoft SQL Server 2012 allows users to specify a range of options once replication has been set up. In this video; Rafiq Wayani demonstrates replication settings; such as components and scheduling; for both near-time and real-time replication.
-
Replication Setup
Microsoft SQL Server 2012 allows users to set up replication to copy databases across multiple environments in real time. In this video; Rafiq Wayani demonstrates how replication creates redundancy and allows a fully current secondary server to take over from a primary server in real time.
-
Restore Database
Once your database is backed up; Microsoft SQL Server 2012 allows you to easily restore the database helping you avoid potentially catastrophic data loss caused by a variety of failures. In this video; Rafiq Wayani uses the SQL Server Management Studio Restore Dialog box to restore an existing database backup file.
-
Result Set
Microsoft SQL Server 2012 Integration Services provides the ability to use an Execute SQL statement to capture a result set. In this video; David Dye demonstrates how to do this in two different ways: using the result set or an output parameter.
-
Review of the Previous Management Portal
Because the new Windows Azure Management Portal is still under development; you may need to access the previous management portal to perform certain tasks. In this video; Jonathan Summers demonstrates how to access the older management portal and runs through which tasks you would still use it for.
-
Review Unused Indexes
Microsoft SQL Server 2012 provides configuration options for reviewing unused indexes. In this video; Rafiq Wayani explains how to review unused indexes if you have too many indexes associated with a table.
-
Role Switching
In Microsoft SQL Server 2012; you can switch servers between their designated roles. In this video; Rafiq Wayani demonstrates conceptually how one server can take the place of another server in the event of a system failure.
-
Row Count
Microsoft SQL Server 2012 Integration Services' Row Count transformation allows you to count rows passed through a data flow and then store the count in a variable. In this video; David Dye uses a Row Count transformation to count the rows passed to it by an OLE DB data source; and stores the row count in a flat file variable.
-
Row Sampling
In Microsoft SQL Server 2012; the SQL Server Integration Services (SSIS) Row Sampling transformation allows you to randomly select a fixed number of rows from a source data set. In this video; David Dye uses the Row Sampling Transformation Editor dialog box to configure a random sampling of rows from a data source.
-
Script Error Handling
Microsoft SQL Server 2012 allows you to use DTS fire advanced methods to assist with error handling in Integration Services scripts. In this video; David Dye demonstrates how to specify a warning; rather than an error; to debug scripts and better handle queries and information.
-
Script Task
Microsoft SQL Server 2012 provides a Script Task feature that can be used to perform functions that are not provided by Integration Services. In this video; David Dye demonstrates how to use a Script Task within Integration Services as a debugging tool; and discusses various configuration properties.
-
Secure Server 2012
Microsoft SQL Server 2012 allows users to secure SQL Server from within SQL Server 2012 itself or secure the SQL Server 2012 platform. In this video; Rafiq Wayani demonstrates a range of security settings; including secure login; backup; authentication; auditing; permissions; and database encryption.
-
Secure Stored Procedures
Microsoft SQL Server 2012 allows you to enhance the security of stored procedures by obfuscating data through its encryption mechanism. In this video; Rafiq Wayani highlights the differences between creating a stored procedure without encryption and using the WITH ENCRYPTION T-SQL keyword to secure stored procedures in a database.
-
Send Mail
Microsoft SQL Server 2012 Integration Services' Send Mail Task allows you to receive e-mail notifications of errors within your packages. In this video; David Dye configures the Send Mail Task on the Event Handler of an Execute SQL Task; utilizing expressions; as well as user and system-defined variables; to define the message body; subject; and recipients.
-
Sequence
Microsoft SQL Server 2012 Integration Services allow you to use Sequence containers to define control flows forming subsets of your package's overall control flow. In this video; David Dye demonstrates how to disable propagation and the FailParentOnFailure property on a task to ensure the successful execution of a sequence to maintain control flow.
-
Set Up SQL Cluster
Microsoft SQL Server 2012 allows you to create a SQL Server cluster by installing the Failover Clustering feature on your servers. In this video; Rafiq Wayani uses Server Manager to install Failover Clustering on both his servers before explaining the need for network cards for internal communications between the two failover clusters and for external communication.
-
Sort
The Sort transformation in Microsoft SQL Server 2012 sorts input data and copies the sorted data to the transformation output; although it can be costly in terms of resources. In this video; David Dye demonstrates the Sort transformation and explains when you would use it.
-
Spatial Data
Microsoft SQL Server 2012 allows users to work with spatial data in a spatial database. In this video; Rafiq Wayani explains what spatial data is; its relation to geographical information systems (GIS); and why it cannot be stored in standard database tables.
-
Split Transformation
Microsoft SQL Server 2012 allows you to use a conditional Split transformation to dynamically route the data through a data flow. In this video; David Dye demonstrates how to split a list of employees into managers and non-managers using a Split transformation on two different tables.
-
SQL Backup
Microsoft SQL Server 2012 allows you to easily create database backups using SQL Server Management Studio so you can safeguard critical data. In this video; Rafiq Wayani uses the Back Up Database dialog box in Management Studio to create a full database backup; and demonstrates how to drop the database using the Detach Database dialog box.
-
SQL Cluster
In Microsoft SQL Server 2012; creating a Windows Server Failover Cluster (WSFC) provides high database availability and is necessary when installing SQL Server in a clustered environment. In this video; Rafiq Wayani demonstrates how to download and use the Microsoft iSCSI Initiator; create iSCSI virtual disks; and establish iSCSI targets while creating a WSFC.
-
SQL Database Connection Security
Windows Azure SQL Database enables you; by way of certificates issued by a Certification Authority; to ensure secure connections between your local computers and your hosted SQL databases. In this video; Jonathan Summers uses the Microsoft Management Console to import a self-signed certificate into a Personal certificate store; and then into Windows Azure SQL Database.
-
SQL Database Connectivity Troubleshooting
Windows Azure SQL Database is hosted in a cloud environment and you may experience connectivity issues from time to time that you will be required to locate and troubleshoot as efficiently as possible. In this video; Jonathan Summers highlights some of the common connectivity issues that you may encounter in Windows Azure and explains how they can be resolved.
-
SQL Errors
Microsoft SQL Server 2012 allows T-SQL errors to be handled in two phases; firstly to check syntax; and secondly; object reference names. In this video; Rafiq Wayani demonstrates how an invalid T-SQL statement can bypass the first phase because it's syntactically correct; but is caught in the second phase; and details different T-SQL error severity levels.
-
SQL on Server Core
Microsoft SQL Server 2012 provides configuration options for installing SQL Server on a Server Core operating system platform. In this video; Rafiq Wayani explains how to install SQL Server 2012 on Server Core.
-
SQL Second Instance
In Microsoft SQL Server 2012; you can create a second instance of SQL server and have it run on the same machine. In this video; Rafiq Wayani discusses how to access a second instance of SQL server and if it will affect the cluster in some way.
-
SQL Server Agent
Microsoft SQL Server 2012 provides SQL Server Agent which can be used to automate tasks; such as monitoring performance. In this video; David Dye demonstrates how to configure SQL Server Agent to schedule Integration Services packages.
-
SQL Server and PowerShell
Microsoft SQL Server 2012 provides direct access to PowerShell; allowing users to run DOS-like commands on the current database. In this video; Rafiq Wayani demonstrates how to access PowerShell from within SQL Server 2012; and how to work with a database by selecting and exporting specific data.
-
SQL Virtualization Concept
In Microsoft SQL Server 2012; you can install SQL Server on a virtual platform. In this video; Rafiq Wayani demonstrates the SQL Virtualization Concept within the VMware virtual environment and explains how the VMware Datacenter organizes virtual machines within a physical machine.
-
SSDT
In Microsoft SQL Server 2012; SQL Server Data Tools (SSDT) are used to create and configure SQL Server Integration (SSIS) packages. In this video; David Dye demonstrates the main components of the SSDT development environment; how they can be customized; and how to access package information during design and at runtime using the different tabs.
-
SSIS Reports
Microsoft SQL Server 2012 Integration Services Catalog provides a range of precompiled reports; as well as the ability to create custom drill-through reports. In this video; David Dye demonstrates how to access a failure report and view the relationship between logging configurations and the level of detail in the report.
-
SSIS Transactions
In Microsoft SQL Server 2012; you can configure the transactions that SQL Server Integration Services (SSIS) packages use to bind the actions that perform tasks. In this video; David Dye demonstrates how to configure the TransactionOption property and start the Distributed Transaction Coordinated service in order to roll back the data flow in a Sequence container when a task fails.
-
Stored Functions
Microsoft SQL Server 2012's stored functions force a query to return a value. Scalar functions wrapped in a BEGIN...END block return a single data value in the RETURNS clause. In this video; Rafiq Wayani demonstrates how to create a function and queries its protocol to show that the result depends on the defined protocol.
-
Stored Procedures Passing Parameters
Microsoft SQL Server 2012 allows you to declare input and output parameters so values can be passed to and from a stored parameter used for a variety of purposes during procedure execution. In this video; Rafiq Wayani demonstrates how to create a stored procedure which includes syntax to declare and execute an OUTPUT parameter.
-
Test Mirror
Microsoft SQL Server 2012; when implemented in a virtual environment; allows users to implement fault tolerance by mirroring databases. In this video; Rafiq Wayani demonstrates how to test a mirror to ensure that it's able to take over from the primary server without impacting user productivity.
-
Test Replicate
Microsoft SQL Server 2012 allows users to test that replication functions as it should. In this video; Rafiq Wayani demonstrates how to test that replication works and that the replication data is accurate.
-
Test SQL Cluster
Microsoft SQL Server 2012 provides configuration options for testing SQL Server on a cluster. In this video; Rafiq Wayani explains how to test a SQL Server on a Windows database cluster.
-
The Windows Azure Platform Management Portal Overview
Windows Azure SQL Database's platform management portal enables centralized management and administration; allowing you to initiate and complete subscription and production tasks from intuitive navigation bars and menus. In this video; Jonathan Summers introduces the key navigation bars and menus in the management portal.
-
Throw Exceptions
Error throwing in SQL Server 2012 allows you to better understand exceptions that have been raised and caught. In this video; Rafiq Wayani uses a throw statement to display the specific error message generated when an exception is raised.
-
Track Missing Indexes
Microsoft SQL Server 2012 provides configuration options for tracking missing indexes. In this video; Rafiq Wayani explains how to create an index seek to track missing indexes on a non-clustered index.
-
Transactions Native
In Microsoft SQL Server 2012; you can avoid the extra overhead incurred when utilizing Integration Services transactions to roll back tasks by implementing Transact-SQL native transactions instead. In this video; David Dye uses Transact-SQL statements to implement native transactions and provide the ability to roll back multiple tasks in an SSIS package.
-
Transfer Database
In Microsoft SQL Server 2012; you can use the Transfer Database Task in SQL Server Management Studio and SQL Server Data Tools to copy or move a database from one instance to another. In this video; David Dye demonstrates the different configuration options available when you copy or move a database.
-
Transfer Objects
In Microsoft SQL Server 2012; you can use the Transfer Objects Control Flow Task to transfer objects from one database to another. In this video; David Dye demonstrates how the Transfer Objects Control Flow Task can be used for a number of different transfer tasks.
-
Troubleshoot Clusters
Microsoft SQL Server 2012 provides configuration options for installing SQL Server on a cluster. In this video; Rafiq Wayani demonstrates how to install SQL Server on a Windows cluster that has already been created and validated.
-
Try Convert Function
Microsoft SQL Server 2012 introduces the TRY_CONVERT function; which returns a null value if any errors are encountered while converting an expression to another data type. In this video; Jonathan Summers demonstrates how the TRY_CONVERT function works to ensure that your application will not be impacted if there is an error when converting.
-
TSQL Parameters
Microsoft SQL Server 2012 Integration Services allow you to assign and implement parameter values in T-SQL statements. In this video; David Dye uses the Execute SQL Task Editor to map input and output parameters to variables in a stored procedure.
-
Understand Stats
In Microsoft SQL Server 2012; understanding query optimization statistics is an important part of improving query performance and modifying a query for the best results. In this video; Rafiq Wayani uses the New Statistics on Table dialog box in Management Studio to create statistics; and highlights some of the statistical information used by the query optimizer.
-
Unions All
In Microsoft Visual Studio 2012; you can use Union All to combine the result-set of two or more select statements into a single result-set that retains all duplicate entries. In this video; David Dye demonstrates how to join two sources together using the Union All set operator.
-
Unsecured Access
Microsoft SQL Server 2012 allows you to secure data in a database using encryption to avoid unauthorized access to unencrypted data. In this video; Rafiq Wayani demonstrates how to create a database and back up the data to a secure .bak file.
-
Using SQL Agent
Microsoft SQL Server 2012 allows users to schedule and submit jobs using the SQL Server Agent. In this video; Rafiq Wayani demonstrates how to create and submit a new job; and then monitor its activity.
-
Using Try Catch
Microsoft SQL Server 2012's T-SQL structured TRY...CATCH construct and THROW statement allows you to raise errors in the TRY block and pass control of them to the CATCH block. In this video; Rafiq Wayani contrasts the capabilities of the TRY...CATCH construct with the @@ERROR type; and demonstrates how the THROW statement identifies the source of the error.
-
Validate Cluster
Microsoft SQL Server 2012 allows you to use the cluster validation process to validate the computers on the network on which you wish to install your SQL Server cluster. In this video; Rafiq Wayani uses the Failover Cluster Manager's Validate a Configuration Wizard to name and validate his machines' hardware.
-
View History of Logical Server
In Microsoft SQL Server 2012; you can use the server history to keep track of the activity taking place on your database. In this video; Jonathan Summers demonstrates how to access and view the history for import and export operations carried out on a server.
-
Character Map
In Microsoft SQL Server 2012; you can configure the Character Map Transformation Task to modify how information is displayed. In this video; David Dye uses the Character Map Transformation Task on information in a database to transform people's middle names into uppercase.
-
Access FILESTREAM Data
FILESTREAM in Microsoft SQL Server 2012 integrates the SQL Server Database Engine with an NTFS file system and is completely transparent to Transact-SQL code; allowing you to access data in FILESTREAM columns just as you would any conventional columns. In this video; Jonathan Summers uses T-SQL statements to insert; update; and delete rows in a table that supports FILESTREAM data.
-
Access Secured
Microsoft SQL Server 2012 provides the capability to secure data in a database using encryption. In this video; Rafiq Wayani demonstrates how to access data in the unencrypted database; how to encrypt the data; and shows that; once it has been encrypted; the data cannot be accessed.
-
Adding Custom Error Messages
Microsoft SQL Server 2012 allows system administrators to use the sp_addmessage stored procedure to add custom error messages which are then issued as native errors. In this video; Rafiq Wayani uses the sp_addmessage stored procedure to define and execute a custom error; and explains the WITH LOG option available to system administrators.
-
Advanced Cycled Sequences
In Microsoft SQL Server 2012; you can alter a sequence to produce a different result when you run it again. In this video; Rafiq Wayani demonstrates how to change the sequence statement to begin the sequence at a different value when the sequence is restarted.
-
Advanced Move Encrypted Data
Microsoft SQL Server 2012 provides the capability of moving secure data from one location to another. In this video; Rafiq Wayani demonstrates how to delete the certificate and the master key; and then restore the database from the backup on a disk.
-
Advanced Sequences
In Microsoft SQL Server 2012 you can go beyond the basic uses of sequences by using a number of advanced features. In this video; Rafiq Wayani demonstrates how to create a sequence to supply audit information from tables.
-
Advanced Sequences in Multiple Tables
One advantage of using sequences in Microsoft SQL Server 2012 is that they can be used with multiple tables. In this video; Rafiq Wayani demonstrates how to generate recurring number sequences and use the same sequence object across multiple tables to produce a result set.
-
Audit
Microsoft SQL Server 2012 provides various configuration options for utilizing an Audit Transformation within a data flow; as well as the ability to capture information at the time of packages executed. In this video; David Dye demonstrates how to use the Audit Transformation tool within a data flow task.
-
Availability Modes
In Microsoft SQL Server 2012; AlwaysOn Availability Groups support two modes that are properties used to regulate whether the primary replica waits to commit transactions on a database until a secondary replica has hardened the log. In this video; Rafiq Wayani demonstrates how AlwaysOn Availability Groups support the asynchronous-commit availability mode and the synchronous-commit availability mode.
-
Basic Sequence
Microsoft SQL Server 2012 allows users to create sequences as database objects which can be used on any table in the database. In this video; Rafiq Wayani demonstrates how to create and call a sequence.
-
BCP Utility
In Microsoft SQL Server 2012; you can easily export and import bulk data from a database; file; or table. In this video; Rafiq Wayani demonstrates how to use the bulk copy utility; also called the bcp utility; to export and import data.
-
Built-In Aggregate Functions
The built-in aggregate functions in Microsoft SQL Server 2012 can be applied to a set of records where they perform calculations on column values in order to summarize the data and return a single value. In this video; Jonathan Summers demonstrates how to use the COUNT grouped aggregate function to perform a calculation.
-
Built-In Functions
Microsoft SQL Server 2012 provides a number of new features; one of which is the ability to do efficient paging. In this video; Rafiq Wayani demonstrates how to do paging using the offset; fetch; next rows; and only keywords.
-
Built-In Functions Enhancements
Microsoft SQL Server 2012 provides various enhancements to built-in functions. In this video; Rafiq Wayani demonstrates how to use the choose logical function; the inline if (iif) function; the concat function to concatenate values; and the format function to specify value formats such as date.
-
Built-In Security Functions
The built-in security functions in Microsoft SQL Server 2012 provide a useful way of managing security by allowing you to return information about users and roles. In this video; Jonathan Summers demonstrates how to return a database username from a specified user ID using the USER_NAME security function.
-
Built-In Test and Image Functions
The built-in test and image scalar functions in Microsoft SQL Server 2012 allow you to perform an operation on a text or image input value or column and return information about the value. In this video; Jonathan Summers discusses the three test and image functions and demonstrates how to use the PATINDEX function.
-
Built-In Windows Functions
Windows functions have been enhanced in Microsoft SQL Server 2012 and additional functions have been provided that can be applied to a result sets partitioned rows in order to compute aggregate values. In this video; Jonathan Summers uses the RANK function to return the rank of each row within the partition of a result set.
-
Bulk Insert
In Microsoft SQL Server 2012; you can configure and use the Bulk Insert Task to bulk insert data. In this video; David Dye demonstrates how to use a Bulk Insert Task and discusses the differences between a Data Flow Task and the Bulk Insert Task.
-
Bypass Prepare
Microsoft SQL Server 2012 Integration Services allow tasks to use the BypassPrepare property so they can bypass the prepare command which doesn't support certain T-SQL keywords. In this video; David Dye demonstrates how a task is able to successfully prepare on execution when BypassPrepare is set to True.
-
Authentication
In Microsoft SQL Server 2012; you can authenticate user credentials and logins. In this video; Rafiq Wayani demonstrates how to use the asymmetric key algorithm to authenticate SQL logins.
-
Expressions
In Microsoft SQL Server 2012; you can use an integration services expression to assign or change variable values within the expression task. In this video; David Dye demonstrates how you can use the Expressions Control Flow Task to assign and utilize integration services expressions language.
-
Stored Procedures
Microsoft SQL Server 2012 allows you to create precompiled sets of one or more statements as Transact-SQL stored procedures that are stored in the database; enabling an efficient reuse of SQL code. In this video; Rafiq Wayani demonstrates how to create; execute; modify; locate; and remove a stored procedure.
-
Variables
Microsoft SQL Server 2012 allows users to set scopes; data types; and expressions within variables. In this video; David Dye demonstrates how to work with the scope of a variable and retrieve information during package execution.
