Quiz 1-4 Review

If you are going to perform a SQL Server upgrade in-place, there are several steps that should be taken prior to performing the upgrade. Please check ALL that apply list from the list below (15 points)

- Run the appropriate DBCC consistency checks (for example, DBCC CHECKDB and DBCC CHECKFILEGROUP). - Make sure the system databases on your existing older instances (for example, master, msdb, tempdb, and model) are all set to auto-grow and have sufficient free space to grow. - Create full, verified backups of your existing SQL Server databases.

Which of the following statements are true in SQL Server? (15 points)Select ALL that apply: -Server-side networking for SQL Server is configured at the Database level. -A data page in SQL Server can only hold one object (e.g. a specific table, index, etc.). -It is best practice to set Auto_Update_Statistics & Auto_Create_Statistics to FALSE. -SQL Server pages can be any size between 8K and 64K. -You can have multiple versions of SQL Server installed on the same virtual server. -You must pay for all editions of SQL Server. -SQL Server data files can only grow in multiples of 64K

-A data page in SQL Server can only hold one object (e.g. a specific table, index, etc.). -You can have multiple versions of SQL Server installed on the same virtual server. -SQL Server data files can only grow in multiples of 64K

Select all TRUE statements Select one or more: -SQL Server Profiler is a server-side utility used for tracing events. -DTA is a client-side GUI tool that helps you find data structure changes (e.g. new indexes) that can improve performance. -SQL Server Profiler only allows the admin to filter data based on events and the values in data columns related to these events. -SQL Server Profiler can only send its output to a file or a table in a database. -Because it's so efficient, you can run SQL Server Profiler on OLTP databases as needed. -DTA can accept input for its processing from either a flat file, a table or the plan cache of the instance it's connected to. -The Database Tuning Advisor (DTA) tool is only available on SQL Server Enterprise Edition.

-DTA is a client-side GUI tool that helps you find data structure changes (e.g. new indexes) that can improve performance. -SQL Server Profiler only allows the admin to filter data based on events and the values in data columns related to these events. -DTA can accept input for its processing from either a flat file, a table or the plan cache of the instance it's connected to.

Select all TRUE statements Select one or more: -Database Maintenance plans do not require SQL Server Agent. -Each SQL Server Instance has its own SQL Server Agent process. -A database user needs to be a part of the Sysadmin role in order to execute SQL Server Agent jobs. -Database Maintenance plans help automate many standard DBA maintenance tasks. -It is best practice to create one maintenance plan to support all databases for an instance. -SQL Server job schedules can be attached to multiple SQL Server Agent jobs or none at all. -SQL Server Agent jobs CANNOT be run simply based on CPU idle time; a specific start time is required.

-Each SQL Server Instance has its own SQL Server Agent process. -Database Maintenance plans help automate many standard DBA maintenance tasks. -SQL Server job schedules can be attached to multiple SQL Server Agent jobs or none at all.

Select ALL of the TRUE statements. Select one or more (15 points): -Filegroups apply only to data files and not to log files. -Dirty pages are data pages that have been updated in memory, but not yet written to disk. -A SQL Server data file can be a part of multiple filegroups. -DBCC CHECKDB should be executed at all times to ensure data integrity. -You can control the location of objects within a database onto specific disks/SAN LUNs only if multiple filegroups are created for each disk or SAN LUN. -SQL Server data files and log files perform best on network shares as opposed to locally connected drives. - SQL Server always writes to the transaction log on disk before any data page is updated in the memory buffers

-Filegroups apply only to data files and not to log files. -Dirty pages are data pages that have been updated in memory, but not yet written to disk. -You can control the location of objects within a database onto specific disks/SAN LUNs only if multiple filegroups are created for each disk or SAN LUN.

Choose all of the statements that are true (10 points). -For you to switch data from a partition to a table or from a table into a partition, the source and target of the ALTER TABLE...SWITCH statement must reside in the same filegroup. -When switching data from a partition to a table or from a table into a partition, the indexes for both do not need to be identical. -You use the ALTER PARTITION ...SPLIT RANGE T-SQL command in order to drop a partition. -Using RANGE LEFT partitions for datetime values is usually best because this approach makes it easier to specify the limits of the ranges. -You use the ALTER PARTITION ...MERGE RANGE T-SQL command in order to drop a partition. -Any filegroup referenced in the partition scheme must exist before the partition scheme is created.

-For you to switch data from a partition to a table or from a table into a partition, the source and target of the ALTER TABLE...SWITCH statement must reside in the same filegroup. -You use the ALTER PARTITION ...SPLIT RANGE T-SQL command in order to drop a partition. -Any filegroup referenced in the partition scheme must exist before the partition scheme is created.

Select all TRUE statements Select one or more: -In SQL Server, Isolation is guaranteed through TRANSACTION LOGGING. -Generally, optimistic locking provides for high concurrency. -Poorly performing SQL statements and database locking are two of the most common performance issues within SQL Server and RDBMS' in general. -Phantom reads cannot be avoided using the SNAPSHOT isolation level. -By default sessions wait 30 seconds for locks to clear before failing the current transaction. -To avoid blocking of sessions due to locks, it is advised to use the highest level of isolation possible. -Deadlocks are automatically handled by SQL Server and thus best practice is NOT to monitor for them.

-Generally, optimistic locking provides for high concurrency. -Poorly performing SQL statements and database locking are two of the most common performance issues within SQL Server and RDBMS' in general. -Phantom reads cannot be avoided using the SNAPSHOT isolation level.

Select all TRUE statements Select one or more: -Transparent data encryption (TDE) allows you to limit which users can decrypt the data that is encrypted. -If you are utilizing encryption, then you need to back-up the Service Master Key, Database Master Key and any digital certificate that you are using. -Column-level encryption requires modification to the application in order to implement. -Using column-level encryption allows you to index the encrypted columns. -Prior to restoring an encrypted database backup to another server, the database master key and certificate need to be restored to the new server. -You can determine the encryption algorithm to utilize for the Database Master Key. -Special options need to be provided when restoring an encrypted backup.

-If you are utilizing encryption, then you need to back-up the Service Master Key, Database Master Key and any digital certificate that you are using. -Column-level encryption requires modification to the application in order to implement. -Prior to restoring an encrypted database backup to another server, the database master key and certificate need to be restored to the new server.

Which of the following statements are true in SQL Server? (15 points)Select ALL that apply: -Indexes should be created on columns that have a low cardinality. -Page splits cannot occur on HEAP tables during the addition of a row. -An ALTER INDEX ... REBUILD on a CLUSTERED index cannot defrag the data pages of the table. -Indexes cannot be compressed. -A table can have multiple CLUSTERED indexes. -Indexes are best added to columns referred to by many queries and that are part of a table with many rows. -Filtered indexes require a WHERE clause

-Page splits cannot occur on HEAP tables during the addition of a row. -Indexes are best added to columns referred to by many queries and that are part of a table with many rows. -Filtered indexes require a WHERE clause

Choose the statements below that are true (15 points). -Compressing the backups of encrypted databases are generally a best practice because they compress more than unencrypted databases. -When the database is in Bulk-logged recovery mode, a point in time recovery is not allowed. -Point-in-time recovery is not permitted with file or filegroup backups unless all filegroups are restored to the same point-in-time to maintain database consistency. -You should always perform a log tail backup prior to beginning the restore process. -Individual file or filegroup backups can be restored from a full database backup. -A file or filegroup backup is used to backup a portion of the transaction log.

-Point-in-time recovery is not permitted with file or filegroup backups unless all filegroups are restored to the same point-in-time to maintain database consistency. -You should always perform a log tail backup prior to beginning the restore process. -Individual file or filegroup backups can be restored from a full database backup.

Which of the following is a valid T-SQL Statement that queries the system catalog to display the check constraints associated to the Sales.SpecialOffer table (5 points). -SELECT * FROM [AdventureWorks2014].[INFORMATION_SCHEMA].[CHECK_CONSTRAINTS] Where CONSTRAINT_SCHEMA = 'Sales' and CONSTRAINT_NAME = 'CK_SpecialOffer%' -SELECT * FROM [AdventureWorks2014].[INFORMATION_SCHEMA].[CONSTRAINTS] Where CONSTRAINT_SCHEMA = 'Sales' and CONSTRAINT_NAME like 'CK_SpecialOffer%' -SELECT * FROM [AdventureWorks2014].[sys].[objects] Where type_desc = 'CHECK_CONSTRAINT' and name like 'CK_SpecialOffer% -SELECT * FROM [AdventureWorks2014].[sys].[objects] Where type_desc = 'CHECK_CONSTRAINT' and name = 'CK_SpecialOffer%

-SELECT * FROM [AdventureWorks2014].[sys].[objects] Where type_desc = 'CHECK_CONSTRAINT' and name like 'CK_SpecialOffer%

SQL Server 2017 supports compatibility mode for which of the following versions of SQL Server? (10 points)Select one or more: -SQL Server 2008 R2 -SQL Server 2012 -SQL Server 2008 -SQL Server 2014 -SQL Server 2005

-SQL Server 2008 R2 -SQL Server 2012 -SQL Server 2008 -SQL Server 2014

Select all TRUE statements Select one or more: -Log shipping can be configured for automatic failover at the standby location. -SQL Server database snapshots are read-only. -SQL Server Agent is required for Log Shipping to function. -SQL Server Log Shipping can be used to migrate a database to another location. -In SQL Server, only one database snapshot can be taken of each database. -SQL Server database snapshots CANNOT be used to restore a database. -The CREATE DATABASE statement is used to create a database snapshot. -Log shipping is at the instance level, copying the logs of all databases to another location.

-SQL Server database snapshots are read-only. -SQL Server Agent is required for Log Shipping to function. -SQL Server Log Shipping can be used to migrate a database to another location. -The CREATE DATABASE statement is used to create a database snapshot.

Select all TRUE statements Select one or more: -In SQL Server, permissions CANNOT be dropped from the special guest database user. -Database users can be assigned to Database roles and Application roles. -SQL Server has a nested hierarchy of scope for all securables. -In SQL Server, permissions link principals to securables. -If SQL Server auditing is enabled, then the SQL Server instance will ALWAYS shutdown immediately if logging cannot occur. -In SQL Server, a database user cannot exist without an associated SQL login. -A security principal in SQL Server can be a Server Role, Login, Database User, Database Role or Application Role.

-SQL Server has a nested hierarchy of scope for all securables. -In SQL Server, permissions link principals to securables. -A security principal in SQL Server can be a Server Role, Login, Database User, Database Role or Application Role.

Select all TRUE statements Select one or more: -SSIS packages can not only move data from one data source to another, but also transform the data as it is migrated. -SSIS packages can only be executed with the dtexec and dtexecgui utilities. -SSIS can only be used to transfer data between Microsoft products. -The dtexec command can only run a package loaded in the msdb database of an SSIS server. -You can create basic SSIS packages from SSMS. -SSIS packages created in SSMS can be complex with adjustable workflows and containers. -SSIS supports conditional workflows whereby steps can be skipped or executed based on the result of previous steps

-SSIS packages can not only move data from one data source to another, but also transform the data as it is migrated. -You can create basic SSIS packages from SSMS. -SSIS supports conditional workflows whereby steps can be skipped or executed based on the result of previous steps

Select all TRUE statements Select one or more: -SSRS depends on SQL Server Agent to execute automated reporting jobs. -The two most commonly used SSRS tools to create reports are "Report Builder" and the separately available SSDT tools. -The Report Manager web-based tool is used to configure all URLs used by SSRS. -SSRS includes multiple APIs to call reports as well as report building tools and a report server architecture. -SSAS, SSIS and SSRS are only available with SQL Server Enterprise Edition. -The Report Services Configuration Manager (RSCM) tool can be used to create report models and data source views. -Temporary report data & cached reports are stored in the database named ReportServer.

-SSRS depends on SQL Server Agent to execute automated reporting jobs. -The two most commonly used SSRS tools to create reports are "Report Builder" and the separately available SSDT tools. -SSRS includes multiple APIs to call reports as well as report building tools and a report server architecture.

Select all TRUE statements Select one or more: -The SQL Server Agent service needs to be running for SQL Server Replication to function. -In SQL Server, Merge replication conflict resolution must always be performed manually to avoid consistency errors. -In SQL Server Replication, the Merge agent is only used in merge replication. -In SQL Server replication, the publisher decides if subscribers can PUSH or PULL their subscriptions. -In SQL Server, Snapshot replication is constantly monitoring for updates to apply to subscribers. -In SQL Server Replication, the Snapshot agent is only used in Snapshot replication. -In SQL Server, Transactional replication can be configured for bi-directional replication.

-The SQL Server Agent service needs to be running for SQL Server Replication to function. -In SQL Server Replication, the Merge agent is only used in merge replication. -In SQL Server, Transactional replication can be configured for bi-directional replication.

Select all TRUE statements Select one or more: -The following is a valid linked server table "AccessDB...TableA" because intermediary nodes do not need to be expressed if not applicable for a linked server. -The remote end of a SQL Server Linked Server can be an Excel spreadsheet. -SQL Server Database Mail allows you to send and receive emails within SQL Server In SQL Server. -In order to connect to the remote server through a linked server, the end user needs to be defined with valid credentials at the remote server. -With SQL Server Database Mail, a user is not directly related to an email account, but instead related through an intermediary email profile. -For SQL Server Linked Servers to function properly, configurations must be applied on both the local and the remote server. -SQL Server Database Mail is enabled by default with SQL Server Standard and Enterprise edition.

-The following is a valid linked server table "AccessDB...TableA" because intermediary nodes do not need to be expressed if not applicable for a linked server. -The remote end of a SQL Server Linked Server can be an Excel spreadsheet. -With SQL Server Database Mail, a user is not directly related to an email account, but instead related through an intermediary email profile.

Select all TRUE statements Select one or more: -A user needs to be granted permission to trigger for it to execute during his/her session. -DML triggers cannot perform distinct transactions within them. -To use a DB function that returns a table, you need to be granted the "Select" permission on that function. -Constraint processing occurs BEFORE the execution of INSTEAD OF triggers. -You cannot reference a function from within a function. -Database functions are typically used as part of a SELECT statement. -Database functions can include transactions. -DML triggers can update data on remote databases.

-To use a DB function that returns a table, you need to be granted the "Select" permission on that function. -Database functions are typically used as part of a SELECT statement. -DML triggers can update data on remote databases.

Select all TRUE statements Select one or more: -Stored procedures fail to be created if the objects they depend on do not exist at create time. -Views can be used to control data access to underlining tables by only providing access to a subset of columns. -Stored procedures can only accept input parameters. -With Enterprise edition, Indexed views are considered by the optimizer even if not directly referenced. -Indexed views improve performance and are typically used in OLTP applications. -All views are updateable. -A benefit of stored procedures is a reduction in network traffic. -Using stored procedures generally improves database portability.

-Views can be used to control data access to underlining tables by only providing access to a subset of columns. -With Enterprise edition, Indexed views are considered by the optimizer even if not directly referenced. -A benefit of stored procedures is a reduction in network traffic.

Select all TRUE statements Select one or more: -It's always best to use Synchronous Commit mode with AlwaysOn Availability Groups. -You need to enable AlwaysOn Availability Groups at the instance level within SQL Server Configuration Manager. -AlwaysOn Availability Groups support FileStream, FileTable, and full-text search. -You cannot combine AlwaysOn Availabiity Groups with SQL Server Clustering. -Databases can be a part of multiple AlwaysOn Availability Groups. -SQL Server connection string does NOT require any special parameters to connect to a read-only replica; SQL Server automatically sends SELECT statements to the read-only replicas. -A database must be configured in FULL Recovery Mode in order to have it participate in an AlwaysOn Availability Group.

-You need to enable AlwaysOn Availability Groups at the instance level within SQL Server Configuration Manager. -AlwaysOn Availability Groups support FileStream, FileTable, and full-text search. -A database must be configured in FULL Recovery Mode in order to have it participate in an AlwaysOn Availability Group.

Which of the following SQL Statements that will provide a list of all tables in the 'AdventureWorks2014" database and nothing else? (5 points) -use AdventureWorks2014; Select * from system.tables -use AdventureWorks2014; SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' -use AdventureWorks2014; Select * from sys.objects where type='TABLE' -use AdventureWorks2014; Select * from INFORMATION_SCHEMA.TABLES

-use AdventureWorks2014; SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'

Configuration Manager

Allows for the modification of authentication credentials, startup parameters, networking configuration and other instance-level options for each SQL Server service

Utilizes a data redundancy approach to provide database-level failover and availability; Only available with Enterprise Edition and only on Windows Server (e.g. not a desktop version of Windows).

AlwaysOn Availability Group

SSMS

An integrated application that provides access to most of the graphical tools you can use to perform administrative and development tasks on SQL Server. A complicated tool that provides an entry point to almost all of SQL Server's functionality.

FileTable

Can contain both transactional data (logged and recoverable) and non-transactional data where the BLOBs reside on a file system.

Log Viewer

Can display log files that are generated from several different sources, including Database Mail, SQL Server Agent, SQL Server, and Windows NT.

A data measure located at the intersection of the dimensions of a cube.

Cell

Change process reads any change transactions from the transaction log and pushes them to a series of change tables.

Change Data Capture

Resource

Contains all the system objects deployed with SQL Server. These system objects include the system stored procedures and system views that logically appear in each database but are physically stored in this database

Manually Linked

Contains non-transactional data where the BLOBs are stored on a file system.

Varbinary(MAX)

Contains transactional data (logged and recoverable) where the BLOBs are stored within the database files.

Filestream

Contains transactional data (logged and recoverable) where the BLOBs reside on a file system.

Which of the following T-SQL Statements is valid syntax for creating a table with 3 columns that has a primary key in descending order (5 points). -Create table mytable (col1 int, PRIMARY KEY (col1), col2 int,col3 int) -Create table mytable (col1 int, CONSTRAINT PK_col1 PRIMARYKEY (col1 desc), col2 int,col3 int) -Create table mytable (col1 int, PRIMARY KEY (col1 desc), col2 int,col3 int) -Create mytable table (col1 int, CONSTRAINT PK_col1 PRIMARY KEY (col1 desc), col2 int,col3 int)

Create table mytable (col1 int, PRIMARY KEY (col1 desc), col2 int,col3 int)

Transactional replication providing a warm (almost hot) standby capability; Changing over to replica after failure requires administration and is not transparent to end users.

Data Replication

The origin of a cube's data.

Data Source

Provides "hot standby" with automatic failover; Will be deprecated in the future.

Database Mirroring

The aggregation levels that make up a side of a cube.

Dimension

Advanced Windows OS configuration of 2 to 16 servers as "nodes"; Can be configured to take over cluster-aware applications from any node that fails.

Failover Cluster Services

Aggregations reside in SSAS, but detailed facts remain in OLTP database.

HOLAP

Used to denote that the session is reading a lower-level object.

IS-Lock

Used to denote that the session has changed a lower-level object.

IX-Lock

FORMAT

Indicates that all backups on the destination media (i.e. file) should be erased as the new backup is copied on it.

COPY_ONLY

Indicates that the backup (even if full) should not reset "changed since last backup" flags in the extents of the database.

Assuming that the SQL Browser is activated on a destination SQL Server 2014 DB server, which if the following would be a valid connection string for the ADO.NET provider for the .NET Framework trying to connect to the "AdventureWorks2014" database available at IP address "1.2.3.4" and the named instance "Adventure" with integrated security.

Integrated Security=true; database=AdventureWorks2014; server=1.2.3.4\Adventure

Provides an "almost hot" spare; Continuously applies transaction log backups from the original database to the copy.

Log Shipping

Query language used to access multi-dimensional cubes in SSAS.

MDX

Detailed facts and aggregations all reside in SSAS.

MOLAP

Built on top of FCS (Failover Cluster Services) for its underlying detection of a failed server and for the shared disk with the database.

SQL Clustering

DIFFERENTIAL

Only backup the extents that have changed since the last full backup.

SQLCMD

Provides the capability to connect to SQL Server from the command prompt and execute T-SQL commands. The T-SQL commands can be stored in a script file, entered interactively, or specified as command-line arguments

Detailed facts and aggregations reside in OLTP database, not in SSAS.

ROLAP

Used to denote that the session is reading the object.

S-Lock

SQL Server 2008 SP4 Enterprise Edition can be upgraded to which of the following SQL Server 2017 editions?

SQL Server 2017 Enterprise Edition

Tempdb

Stores temporary data and data objects. The temporary data objects include temporary tables, temporary stored procedures, and any other objects you want to create temporarily. The longevity of the data objects in this database depends on the type of object created. Ultimately, all objects in this database are removed when the SQL Server service is restarted. This database is re-created, and all objects and data added since the last restart of SQL Server are lost.

MIRROR TO

Used to create a second copy of the backup.

Query Editor

The editing tool for writing queries in SSMS

NO_TRUNCATE

Used to create a tail log backup prior to beginning restores.

Model

This database is a template on which all user-created databases are based. All databases must contain a base set of objects known as the database catalog. When a new database is created, this database is copied to create the requisite objects. Conveniently, objects can be added to this database. For example, if you want a certain table created in all your databases, you can create the table in this database, and it is then propagated to all subsequently created databases.

Distribution

This database is utilized during replication, stores metadata and history information for all types of replication. It is also used to store transactions when transactional replication is utilized. By default, replication is not installed with SQL Server, and you do not see this database listed in SSMS. However, the actual data files for this database are installed by default.

Used to denote that the session has the intent to update the object, thus avoiding others from changing it.

U-Lock

MSDB

Used to store information for the SQL Server Agent, the Service Broker, Database Mail, log shipping, and more. When you create and schedule a SQL Server Agent job, the job's parameters and execution history are stored in this database. Backups and maintenance plan information are stored in this database as well. If log shipping is implemented, critical information about the servers and tables involved in this process are stored in this database.

CHECKSUM

Used to verify that the backup was performed without any corruption.

Provides automatic failover to another physical host within cluster of hypervisor-managed hosts.

Virtual Machine Failover

Match the data formats below with the type of data that they contain (15 points). -Word Doc -Relational Database -XML -Blog -Hierarchical Database -EDI

Word Doc- Unstructured Relational Database- Structured XML- Semi-structured Blog- Unstructured Hierarchical Database- Structured EDI- Semi-structured

Used to denote that the session has changed the object (and not yet committed the transaction).

X-Lock

Master

contains server-wide information about the SQL Server system. This server-wide information includes logins, linked server information, configuration information for the server, and information about user databases created in the SQL Server instance. Also contains the actual locations of the database files and key properties that relate to each user database.

SQL Server uses the standard four-part naming convention when referencing database objects. Which of the following is the correct example of the four-part naming? (5 points)

server_name.database_name.schema_name.object_name