Mcsa 70-642 P2

Front 1

sqlserv.exe -d

sqlserv.exe -l

sqlserv.exe -T

Back 1

-d switch specifies the DB file

-l switch specifies the log file

-T switch enables you to start given trace flags for all the connections for a SQL Server instance

Front 2

The syntax for the sqlserver runtime is as follows

Back 2

SQLServr.exe [-dmaster_file_path] [-lmaster_log_path]
-eerror_log_path] [-sinstance_name][-c] [-f]
[-gmemory_to_reserve] [-h] [-kcheckpoint Speed in MB/sec]
[-m] [-n] [-Ttrace#] [-ttrace#] [-x]
[-ystack dump on this error] [-B] [-K]

Front 3

sqlserv.exe -f

sqlserv.exe -g

Back 3

-f switch places SQL Server in minimal mode and only enables a single connection

-g switch reserves additional memory outside SQL Server's main memory pool for use by extended stored procedures, OLE DB providers used by distributed queries, and automation objects used by T-SQL

Front 4

sqlserv.exe -m

sqlserv.exe -k

sqlserv.exe -s

Back 4

-m switch puts SQL Server in single-user mode and suspends the CHECKPOINT process

-k switch is used to influence the checkpoint frequency

-s switch starts a named instance of SQL Server

Front 5

By default, SQL Server does not scan for startup stored procedures

Back 5

sp_configure ‘scan for startup procs’, 1;
GO
RECONFIGURE;
GO

Front 6

By default, a 2012 instance does not have contained databases enabled. Use sp_configure to enable contained databases prior to migrating an existing database
to this model:

Back 6

sp_configure ‘contained database authentication’, 1;
GO
RECONFIGURE;
GO

Front 7

Rebuilding the System Databases via cmd prompt

Back 7

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=instance_name /SQLSYSADMI
NACCOUNTS=accounts /SAPWD=sa password

Front 8

SQL Server 2012, your server needs, at a minimum, the following:

Back 8

.NET Framework 4.0
Windows PowerShell 2.0
.NET 3.5 with Service Pack 1
A current instance of SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2

Front 9

SCC abbr & def

Back 9

System Confi guration Checker (SCC)

SCC examines the destination computer
for conditions that would prevent an upgrade from completing, such as not meeting
the minimum hardware or software requirements. If such a condition is found, setup aborts and the SQL Server 2012 components uninstall

Front 10

There are two tools to consider as you begin preparing for an upgrade:

Back 10

SQL Server Upgrade Advisor and
Upgrade Assistant for SQL Server 2012

Front 11

SQL Server Upgrade Advisor

Back 11

SQL Server Upgrade Advisor

The rules checked by Upgrade Advisor represent conditions, situations, or known errors that might
affect your upgrade to SQL Server 2012

Front 12

UAFS def and abbr

Back 12

Upgrade Assistant for SQL Server 2012 (UAFS)

Upgrade Assistant allows you to test the functionality of applications designed for SQL Server 2005, 2008 or 2008 R2 against Microsoft SQL Server 2012. Upgrade Assistant uses workload testing to identify potential compatibility issues that exist in application source code. These kinds of issues can’t be discovered by just looking at the database and can be difficult, if not impossible to find by just looking at the source code.

Front 13

SQL Server Confi guration Manager

Back 13

SQL Server Configuration Manager configures the SQL Server services much like the Services
applet in the Control Panel, but it has much more functionality than the applet.

Front 14

sp_confi gure

Back 14

sp_configure is a stored procedure that enables you to change many of the configuration options
in SQL Server.

When you run the stored procedure with no parameters, it shows you the options and their current
settings. By default, only the basic, or commonly used, settings are returned, of which there are 15.
To see additional options, you need to confi gure the instance to display advanced options. You can do so by running

sp_configure, as shown here:
sp_configure ‘show advanced options’, 1;
RECONFIGURE;

Front 15

Issuing the RECONFIGURE command results in a complete fl ush of the plan cache.

Back 15

This gives you an opportunity to reconsider what may have been a bad choice, such as setting a memory option to more memory than exists on the box. The following code shows you how to issue the override for the setting:

EXEC sp_configure ‘recovery interval’, 90;
RECONFIGURE WITH OVERRIDE;
GO

Front 16

Activity Monitor

Back 16

Activity Monitor
The Activity Monitor gives you a view of current connections on an instance. You can use the
monitor to determine whether you have any processes blocking other processes. To open the
Activity Monitor in Management Studio, right-click the Server in the Object Explorer, and then
select Activity Monitor

Front 17

(NOLOCK)

Back 17

(NOLOCK) query hint should rarely, if ever, be used in a production environment. If you find that (NOLOCK) is used regularly in your application, find out why dirty reads are necessary, and if they are, address that root issue first before resorting to query hints. One option to consider as an alternative to (NOLOCK) if dirty reads are absolutely necessary is to set the transaction isolation level to SNAPSHOT.

Front 18

To see all the connections to your server, run

Back 18

sp_who2 without any parameters

pass in the parameter of ‘active’ to see only the active connections to your server, like so:
sp_who2 ‘active’;

Front 19

sys.dm_exec_connections

Back 19

returns a row per session in SQL Server. Because it’s a DMV, it displays as a table and enables you to write sophisticated queries against the view to filter out what you don’t care about, as shown in the following query, which shows only user connections that have performed a write operation:

SELECT * FROM
sys.dm_exec_sessions
WHERE is_user_process = 1
AND writes > 0;

Front 20

sys.dm_exec_sql_text

Back 20

retrieve the text of a particular query.

Front 21

sys.dm_exec_query_stats

Back 21

It shows you a line for each query plan that has been run

Front 22

To turn on a trace flag, use the

Back 22

DBCC TRACEON (1204)

to turn it off

DBCC TRACEOFF (1204, 3625)

One method to issue the command is to interrogate whether a given trace is running, like so:
DBCC TRACESTATUS (3635)

Front 23

If you want to see all traces that apply to the connection, run the following command with the -1
parameter:

Back 23

DBCC TRACESTATUS (-1)

Front 24

SQLDumper.exe

Back 24

dump of its environment after an exception occurs

If you want to create a dump fi le on demand, you need the Windows process ID for the SQL Server instance.

Front 25

To create a full dump, use the
following command:

Back 25

Sqldumper.exe 0 0x1100

Front 26

To create a minidump, use the following command:

Back 26

Sqldumper.exe 0 0x0120

Front 27

SQLDiag.exe

Back 27

This tool consolidates and collects information about your system from several sources:

Windows System Monitor (sysmon)
Windows event logs
SQL Server Profi le traces
SQL Server error logs
Information about SQL Server blocking
SQL Server confi guration information

Front 28

TCP/IP is disabled by default in what SQL edition

Back 28

Developer - in order to reduce footprint

CLR is off by default on all editions

Front 29

SQL Server Confi guration Manager usage

Back 29

SQL Server Configuration Manager configures the SQL Server services much like the Services
applet in the Control Panel, but it has much more functionality than the applet.

Front 30

“Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE ”
“Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT ”

These messages indicate

Back 30

These messages indicate that SQL Server is trying to free up memory pool space to allocate
the memory in this virtual memory area. In this case, you may want to increase the amount
of memory allocated to these objects by using the -g switch. If the -g switch is not used, the
default of 256MB of memory is allocated to this area.

Front 31

sp_procoption

Back 31

Use the sp_procoption stored procedure to make the stored procedure a startup stored
procedure. The sp_procoption stored procedure sets only one parameter. You must fi rst
specify the stored procedure you want to set; the only available option name is startup,
with a value of 1 (on) or 0 (off).

sp_procoption @ProcName = ‘master.dbo.InsertSQLStartupLog’,
@OptionName= ‘startup’,
@OptionValue = 1;

Front 32

sys.dm_db_uncontained_entities

Back 32

Before you migrate a database to a contained model, use the new DMO sys.dm_db_uncontained_
entities to identify the containment level of your database. The output from the following query
returns objects that can potentially cross the application boundary.
SELECT so.name, ue.*
FROM sys.dm_db_uncontained_entities ue
LEFT JOIN sys.objects so
ON ue.major_id = so.object_id;

Front 33

SET NOCOUNT

Back 33

This setting can prevent the (8 Rows Affected)
message from being sent to the client if they do not request it. There is a small performance
enhancement by doing this because this message is an additional recordset sent from SQL Server and may be unneeded traffi c.

Front 34

SQL Server does a check for invalid or not recommended settings when you use sp_configure. If you have provided a value that fails any of these checks, SQL Server warns you with the following message:

Back 34

Msg 5807, Level 16, State 1, Line 1
Recovery intervals above 60 minutes not recommended. Use the RECONFIGURE WITH
OVERRIDE statement to force this configuration.

Front 35

SQL Server Utility

Back 35

This tool enables you
to set up a server as a Utility Control Point (UCP) to manage multiple instances of SQL Server from
a single location.
The benefi ts of implementing SQL Server Utility include the following:
Monitor CPU and disk usage of all instances.
Monitor utilization to identify under- and over-utilized resources.
Set up health policies for groups of instances or for single instances.
Manage monitoring policies for one or more of the instances you manage.

Front 36

To turn on a trace flag, use the ________________

command, followed by the trace you’d like to turn on, as shown here

Back 36

DBCC TRACEON (1204)

DBCC TRACEOFF (1204, 3625) -- (multiple traces can be separated by commas)

Front 37

After you turn on the traces, you’re probably going to want to determine whether the trace is
actually running. To do this, you can issue the ____________ cmd

Back 37

DBCC TRACESTATUS

Front 38

SQL Nexus Tool

Back 38

available from Codeplex at http://sqlnexus.codeplex.com
can help you read and analyze SQLDiag output more effi ciently

Front 39

There are four basic components of SQL Server Agent

Back 39

Jobs

Schedules

Operators

Alerts

(error logs)

Front 40

alert is an automated response to an event. An event can be any of the following

Back 40

SQL Server event
SQL Server performance condition
Windows Management Instrumentation (WMI) event

Front 41

sp_addmessage stored procedure

Back 41

create error messages with the sp_addmessage

sp_addmessage 50001,16 ,‘MESSAGE’, @with_log = ‘TRUE’

Front 42

To give other users access to SQL Agent, you must add them to one of three fi xed database roles in the msdb database

Back 42

SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

Front 43

SQL Server Agent Subsystems

Back 43

SQL Server Agent subsystems are objects that group similar sets of functionality that can be used by SQL Server Agent proxies. These subsystems provide a security boundary that enables a more complex security model to SQL Agent Proxies

Front 44

You can access mail information in the _____________

Attachments can be accessed
via ________________

mail log is in _________________

Back 44

You can access mail information in the sysmail_allitems view

Attachments can be accessed
via sysmail_mailattachments

mail log is in sysmail_eventlog

If you are using DBMail and sending mail and attachments, the mail you send and the attachments
are stored in tables. These tables don’t have any automatic maintenance, they just continue to grow

Microsoft provides stored procedures to delete items from these tables: msdb.dbo.
sysmail_delete_mailitems_sp and msdb.dbo.sysmail_delete_log_sp

Front 45

token in SQL as pertaining to multiserver management

Back 45

token is a string literal that you use in your job steps (T-SQL scripts, CMDExec job steps, or Active Script).
Before the job runs, SQL Server Agent does a string replacement of the token with its value. Tokens are usable only in SQL Server Agent jobs.
One of the tokens you can use is (STRTDT). For example, you might add the following in a T-SQL job step:

PRINT ‘Job Start Date(YYYYMMDD):’ + $ESCAPE_SQUOTE(STRTDT))
If you capture the output, it should look like this:
Job Start Date(YYYYMMDD):20120923

Front 46

All these tokens must be used with escape macros. The purpose of this

Back 46

All these tokens must be used with escape macros. The purpose of this change is to increase the security related to the use of tokens from unknown sources. Consider the following token, which you might have included in a T-SQL job step:
Print ‘Error message: $(A-MSG)’

The T-SQL job step runs as the result of a user error (raiserror). A malicious user could raise an
error like this one:
Raiserror(‘’’;Delete from dbo.Employee’,16,1)
The error returned would be:
‘;Delete from dbo.Employee
The print message would be:
Print ‘Error message:’;Delete from dbo.Employee
If this happens, it means you have just been attacked with a SQL injection attack. The delete
statement runs if the T-SQL job step has permission.

To combat an attack such as this, you must add an escape macro

Front 47

To combat an attack such as this, you must add an escape macro

Back 47

Print ‘Error message: $(ESCAPE_SQUOTE(A-MSG))’
Continuing the example, you end up with the following:
Print ‘Error message:’’;Delete from dbo.Employee
You then get an error due to the unmatched quote, and the step fails, keeping you safe.

Front 48

The following is a list of escape macros:

Back 48

$(ESCAPE_SQUOTE(token)): Doubles single quotes (‘) in the replacement string.
$(ESCAPE_DQUOTE(token)): Doubles double quotes (“) in the replacement string.
$(ESCAPE_RBRACKET(token)): Doubles right brackets (]) in the replacement string.
$(ESCAPE_NONE(token)): The token replacement is made without changes. This is used for backward compatibility only.

Front 49

asynchronous messaging

Back 49

command is sent and will be processed later when the SQL Server gets around to processing the command

Front 50

SQL Server Service Broker introduces three new commands that are used to send and receive
messages

Back 50

CREATE CONVERSATION DIALOG

SEND

RECEIVE

Front 51

A variety of message queuing technologies are available. Microsoft makes two:

Back 51

SQL Server Service Broker and Microsoft Message Queue (MSMQ)

The big difference between SQL Server Service Broker and other queuing technologies is that
SQL Server Service Broker stores the messages in queue within the database, and other queuing technologies store their data outside of the database.

Front 52

You can also enable SQL Server Service Broker using T-SQL by using the ALTER DATABASE statement.

Back 52

NEW_BROKER flag that configures
SQL Server Service Broker for the first time.

The second is the ENABLE_BROKER flag that turns SQL Server Service Broker on if it is disabled

Front 53

You see if SQL Server Service Broker is enabled by looking at the values of the

Back 53

SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases

1 in is_broker_enabled

or

the GUID represents a specific instance of the SB

Front 54

Message Type in SB

Back 54

Message types validate that the data within a message is the correct, expected format

NONE: Any data can be placed within the body of the message that is sent.

EMPTY: Only messages that are empty can be sent.

WELL_FORMED_XML: Only messages consisting of well-formed XML documents can be sent.

VALID_XML WITH SCHEMA COLLECTION: Only XML documents that fit with the specified
XML schema can be used.

Front 55

The following code snippet shows the creation of a SB message type

Back 55

CREATE MESSAGE TYPE YourMessageType
AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML

Front 56

Contracts in SB

Back 56

Contracts define the message types that are used within a single conversation.

Front 57

Queues in SB

Back 57

Queues are where the messages within the SQL
Server Service Broker are stored in the time period between when they are sent and when they are processed.

Front 58

Services in SB

Back 58

Services can specify which contracts (and therefore which message types) can be used when sending messages to a specific queue. When messages are sent, they are sent to a specific service that then delivers the message into a specific queue.

Front 59

Routes in SB

Back 59

Routes control the database to which the messages should be routed

Front 60

SQL Server Service Broker priorities

Back 60

SQL Server Service Broker priorities assign priorities to conversations to force specific conversations to always be processed before lower priority conversations.

Front 61

Conversation Groups in SB

Back 61

Conversation groups control the order that messages are processed when those messages are sent to different services.

Front 62

View that show you all the DB-level DDL triggers

for server-level triggers

Back 62

sys.triggers

sys.server__triggers

Front 63

To create a trigger that would audit for nay stored procedure change, deletion, or creation, you could use a ___________________ statement such as

Back 63

CREATE TRIGGER

CREATE TRIGGER ChangeWindows

ON DATABASE

FOR DDL_PROCEDURE_EVENTS

as

-- Trigger statement here

Front 64

To monitor for any DROP TABLE, CREATE TABLE, or ALTER TABLE statement

Back 64

CREATE TRIGGER ChangeWindow

ON DATABASE

FOR CREATE _TABLE, DROP_TABLE, ALTER_TABLE

AS

-- Trigger statement

Front 65

Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager not does not appear as an application when running Windows 8. To open SQL Server Configuration Manager

Back 65

in the Search charm, under Apps, type SQLServerManager11.msc (for SQL Server 2012) or SQLServerManager10.msc for (SQL Server 2008), and then press Enter.

Front 66

to turn a trace flag on use

Back 66

DBCC TRACEON

DBCC TRACEON (1204)

DBCC TRACEON (1204,3625)

to turn trace off

DBCC TRACEOFF (1204,3625)

Front 67

to see the status of a trace

Back 67

DBCC TRACESTATUS (3625)

Front 68

SQL Service Broker

Back 68

a message queuing technology/framework

Messages can be sent between DBs. Messages can be processed on demand or on schedule or automatically.

Message can bibiderectional or unidirectional

Front 69

dmv returns a row for each application domain in the server

Back 69

sys.dm_clr_appdomain

Front 70

dmv retunrs a row for each managed user assembly loaded into the server address space

Back 70

sys.dm_clr_loaded_assemblies

Front 71

dmv that returns a row for each property related to SQL Server CLR integration, including the version and the state of the hosted CLR

Back 71

sys.dm_clr_properties

Front 72

DMV that returns a row for all CLR tasks currently running

Back 72

sys.dm_clr_tasks

Front 73

DMV that can be used to view a cached query plan for a CLR query

Back 73

sys.dm_exec_cached_plans

Front 74

DMV that contains a row per query statement within the cached plan

Back 74

sys.dm_exec_query_stats