SQL Server 2014 - Part 1

A database may consist of multiple , with each containing at least one data file. Data Files are divided into 8KB , which are part of 64KB .

The option when creating or altering a database controls how full each data page is for a database.

Within each instance of SQL Server 2014, the database contains meta data about each hosted database - and the database contains information about the instance itself.

If you want to include a stored procedure in all newly created databases, you should place this within the database.

In order to group related objects together within a database, you could create and assign them to a

How would you select all data from a table called "Employees" within a schema called "HumanResources"? ....

SELECT * FROM

You can create an alternative name for database objects, either on local or linked server instances, using a

If you regularly need to reference a table within a database on a linked server within your SQL Code, you could use a in order to avoid widespread direct references to the server (therefore creating a binding dependency) in your code.

As a general rule of thumb, character data types requiring 5 or less bytes of data should be stored in a data type, above that, a data type should be used.

A char data type can store a maximum of characters, however an nchar data type can store a maximum of characters.

The data type can store the values 0, 1 or null.

The data type can store whole numbers between 0 and 255.

The data type can store whole numbers between -32,768 and 32,767.

The data type can store whole numbers between -2,147,483,648 and 2,147,483,647.

The data type can store whole numbers between -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

The data type can store financial values from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.

The data type can store financial values between -214,748.3648 and 214,748.3647.

If you wanted to define a field which could store the value 99999.999 using a decimal data type - you would specify the scale to be the value and the precision to be the value .

An image data type can store up to GB of binary data.

If you wished to store binary database within a data type, which *could* exceed 2GB of data - you would use the data type .

The data type can represent data from January 1st, 0001 to December 31st, 9999.

The data type can stored dates between January 1st, 1753 to December 31st, 9999 accurate to the nearest 3.33 millisecond.

The (n) data type can stored dates between January 1st, 0001 to December 31st, 9999 with fractions of a second represented to n decimal places.

The data type can stored dates between January 1st, 1900 to June 6th, 2079 accurate to 1 minute.

The (n) data type stores time in Hours, Minutes, Seconds with fractions of a second represented to n decimal places.

The data type can store the value of any other system data type - except text, ntext, image, timestamp, xml, varchar(max), nvarchar(max) and varbinary(max).

The data type (synonym: ) is automatically updated whenever a row is inserted or updated.

A data type can be used to store a GUID.

SQL Server 2014 Standard Edition can use CPU Cores and GB of memory.

SQL Server 2014 Business Intelligence Edition can use CPU Cores and GB of memory for databases (unlimited memory for business intelligence).

If your server has two CPU Sockets with 8 cores each, you would need to purchase SQL Server 2014 license packs in order to fully license it.

When licensing SQL Server 2014 - you need to license a minimum of cores for each CPU present on the server.

SQL Server specifies the set of rules regarding how data is stored, sorted and compared.

In SQL Server 2014 Analysis Services UDM mode ... is a storage and processing mechanism where aggregates are stored in a separate specialised database to a typical SQL Database - and used to answer queries instead.

In SQL Server 2014 Analysis Services UDM mode ... is a storage and processing mechanism where aggregates are calculated via query cache or directly from the relational SQL Database data source.

In SQL Server 2014 Analysis Services UDM mode ... is a storage and processing mechanism where aggregates are stored and queried partially in a separate specialised database with more detailed queries retrieved directly from the SQL Server Relational Database data source.

Analysis Services databases are column based databases that enable high levels of compression because of the reduced need to store discrete values along with advanced compression algorithms.

Analysis Services Tabular Mode supports a new approach model called the (BISM)

When setting the Maximum Memory available to SQL Server, this should at least GB less than the total available server memory.

The default size for SQL Server 2014 is 4096 bytes. If are supported by the server hardware, this should be increased to 8192 bytes in order to increase performance when the server is subject to a large number of bulk operations.

SQL Server 2014 when installed in the default instance, communicates on port .

Use the SQL Server tool to check for conditions, situations or known errors that might affect the upgrading process.

RESTORE | BACKUP { DATABASE | LOG } WITH is no longer supported.

SET for INSERT, UPDATE and DELETE statements has been depcrecated.

During an upgrade to SQL Server 2014 - all databases with Full-Text Catalog are marked as Full-Text - because of the potential time involved in rebuilding the catalog.

After upgrading to SQL Server 2014 - query performance may be poor due to outdated or unsupported statistics. In order to rectify this situation, you should call the sp_ stored procedure with the argument to rebuild the statistics based on an inherited sampling ratio.

In order to administer the SQL Server services - for example: changing the listening port for an instance - you would use the SQL Server Tool.

If you wish to change the startup parameters of an instance of SQL Server 2014 - you should use the SQL Server tool, access properties of the SQL Server instance and access the tab.

You can use the (uppercase) switch from the command line to start SQL Server and turn on Trace flags for all connections.

Starting SQL Server 2014 from the command line, using the - switch, starts SQL Server in single-user or 'master recovery mode' enabling you to perform critical server maintenance (e.g. restore master database).

In order to make the following stored procedure a 'startup stored procedure' you would use the syntax...

sp_ @procname='master.dbo.MyStoredProcedure', @option='', @optionvalue='1'

When a SQL Server is not allowing new connections, or is overloaded, an administrator can connect using the (DAC) - to force a connection.

In order for an administrator to connect using the Dedicated Administrator Connection - you can connect to the server via SMSS by prefixing the server name with : - or, you can start SQL Server via the command line, using the - switch.

The Dedicated Administrator Connection, as a default, listens on port .

In order to enable the Dedicated Administrator Connection on a remote SQL Instance, you would need to set the appropriate configuration options on the remote instance as follows...

EXEC sp_configure '', 1

In order to view the Edition and version of SQL Server, any non-default configuration options, I/O and CPU statistics by type of activity - you could use the report.

Using SQL Management Studio, you can set configuration options by using the sp_ stored procedure, or use the dialog.

Typically it is recommended that you set the and amount of memory that your SQL instances use in your environment.

Setting the Maximum Worker Threads property to ensures that SQL Server chooses the optimum number of threads to enable on each processor.

By default, sp_configure only shows / sets basic and commonly used configuration options... in order to view and adjust all configuration options, you would use the command...

sp_configure '',1

... and then issue the command

If, when setting a configuration value using sp_configure, SQL Server reports that the value is out of a recommended range - it warns the user. If you wished to proceed and force the change, regardless of the warning, you would use the command...

RECONFIGURE WITH

To view Error Logs within SQL Server Management Studio, you should right click on the node, within the folder in the Object Tree, and select View -> SQL Server and Windows Lg.

Within SQL Server Management Studio, you would use the screen to view current transactions and determine if there are blocking transactions.

Using Transact-SQL, you can see who is connected to your SQL instance with either the sp_ (or, more recently, sp_) stored procedures.

In order to see all active connections to your SQL Instance, you could use the following command....

sp_who2 ''

If you wanted to view the worst performing queries through history in your SQL Instance, you could query the following Dynamic Management View, sys.dm_exec__.

Within SQL Server Management Studio, you can administer many servers at once, issuing commands on each server simultaneously, and consolidating the results into a single result set. This is achieved by creating a Server - and creating within it.

When using a Central Management Server to manage multiple servers, all queries issued to managed servers are sent through connections. If the servers cross domains, you must have relationships between them.

When installed, SQL Server 2014 will not allow SQL Agent to run - having an 'Agent XPs disabled' tag against it in SQL Server Management Studio. In order to allow SQL Agent to start, you must issue the following command...

sp_ '', 1

are a quick and easy way to automate routine maintenance tasks in SQL Server 2014.

You can perform routine maintenance within SQL Server 2014 by using either the Maintenance Plan (the quick way) or the Maintenance Plan (the more advanced editor).

SQL Server can be used to automate tasks within SQL Server, and consists of four basic components...

: Defines the work to be done
: Defines when the job will be executed
: Lists the people who can be notified for job statuses and alerts
: Enables you to set up an automatic response or notification

You can organise your SQL Agent Jobs by creating and assigning a to each job. This helps when there are a large number of jobs within an instance of SQL Server.

In order to log the outcome of a SQL Agent Job Step, the step needs to be executed under a security context belonging to the role.

If you wish to send an alert to an individual if a SQL Server Agent job fails, you must create an in order to represent the individual and their contact details.

In order to send an email to an individual when a SQL Agent Job fails, must be set up and enabled, and the SQL Server must be configured to use it.

When defining an operator to be notified when a SQL Agent Job fails, it is best practice to define an email and send the alerts to it, enabling you to change the recipient list externally to the SQL Job.

In order to send a Net Send message to an individual when a SQL Agent job completes, the Windows Service needs to be installed upon the server - and the individual must provide their name as the recipient destination.

Within SQL Server Agent, an is an automated response to an .

SQL Server Event Alerts are defined based upon an error or error level.

Leveraging the Windows Performance Monitor feature of Windows Servers - an alert can be defined based upon a SQL Server - one of the many performance values that are monitored and tracked natively.

A SQL Server Job Step can run, via impersonation, under a context other than the context of the user that created the job. This can be useful if the job requires access to resources that are denied to the job creator. If this is required, the creator must create a SQL Server Agent that is associated with a domain account that has the appropriate rights.

An unhandled event, in SQL Server, is an event which does not have an defined for it.

In SQL Server Agent advanced properties, you can define what consitutes an condition. When setting this option, you are setting that the is deemed if the CPU usage falls below x and remains below this level for y.

When you wish for placeholders to be replaced in job responses to alerts, you must enabled the option within the SQL Server Agent Alert System Properties dialog.

You can define how long the SQL Agent Service will wait before killing off executing jobs when it's asked to stop, by setting the time-out interval in the Job System Properties dialog.

You can limit the SQL Agent History logging feature by setting the Maximum job history size (measured in ) - and the maximum number of rows per .

When using Event Forwarding within SQL Agent, if you choose to forward 'All Events', you can specify a filter based upon the level.

Multiserver Administration enables a system to be configured with a server (MSX) and one or more servers (TSX) - where jobs can be sent on it's behalf.

In order to set up MultiServer Administration on a SQL Server, you need to right click on the SQL Server node in SQL Management Studio, then select Multi Server Administration...

When setting up a master server, when configuring Multi Server Administration, the first thing that is requested is you set up a operator - the only operator that can be notified from multiserver jobs.

The SQL Server can be used where a large amount of commands can be expected to be received, and need to be completed reliably, just not at the exact time the command was issued.

The feature of SQL Server enables you to build reliable, asynchronous services in a SQL Server Database.

Service Broker Services can pass messages reliably and transactionally - and can contain up to GB of binary data within each message, and, if so desired, can associate an schema with a message, for automatic schema validation.

The Service Broker addresses wildly varying resource loads by a process that watches for messages on Service Broker queues. This background process is called the monitor - and maintains the optimum number of queue processes required to process the current load.

Within a Service Broker system, Services depend on bidirectional exchange of messages. Requests are made of a service and the service returns responses. These exchanges are persisted within the Service Broker in persisted objects called .

A Service Broker is an object at each end of the message exchange that tracks what messages have been sent and received, what messages have been acknowledged and what acknowledgements have been received.

The Service Broker feature uses three commands to manage conversations. These are...

: which generates a conversation on which messages are sent
: which dispatches messages on a previously created conversation
: which receives messages from a conversation.

When restoring a database, that uses the Service Broker, the Service Broker is disabled as a default - to stop the database immediately processing queued conversations. In order to restart the Service Broker at you convenience, you can issue the following command...

ALTER DATABASE <database name> SET

If you have a database that uses Service Broker, and you wish to delete all existing conversations and messages, and create a new SERVICE_BROKER_GUID so the database will look like a new Service Broker database, as it was before, but with a new Identity, you would use the following command...

ALTER DATABASE <database name> SET

If a database is using a Service Broker, and you wish to flush all conversations into an error state in order to clean up both ends of a conversation cleanly, you could use the following command...

ALTER DATABASE <database name> SET

When configuring the Service Broker for a database, types can be used in your service to determine what kind of data a message contains so that it knows how to process it.

When defining a Service Broker Message type, called RegRequest, that can contain any valid XML snippet as the message body - you should use the following command...

CREATE MESSAGE TYPE
AUTHORIZATION dbo
VALIDATION =

When using the Service Broker within a database, you can define which types can be used in a by defining a .

When setting up the Service Broker within a database, a represents a physical table within the database which stores a message during the time period between it's receipt - and when it is processed.

When creating a queue within the Service Broker, you can instruct it to keep an audit log by leaving actioned messages in the queue table, by using the following command...

CREATE QUEUE <Queue Name> WITH = ON

When using the Service Broker within a database, a message that continually fails processing, causing a transactional roll back and being placed back in the queue for reprocessing is called a message. By default, a queue will become disabled if it detects one of these messages rolling back consecutive times.

The preferred method of handling repeatedly failing transactions in the Service Broker is to turn off _HANDLING in the queue, trap the condition and action it in your own application code then the transaction to remove it from the queue. This prevents the queue from being automatically disabled when discovering repeatedly failing transactions.

When using the Service Broker, if you wanted to create a Queue called RegQueue, that triggers the activator stored procedure, dbo.ActivateRegistration, with 20 simultaneous queue readers - running under the queue owner context - you would use the following procedure...

CREATE QUEUE RegQueue
WITH
(
STATUS = ,
PROCEDURE_NAME = ,
MAX_QUEUE_READERS = ,
EXECUTE AS
)

Within the Service Broker, when you define a , you give it a name, then, you tell it which it will use - and then you choose which it will use (which defines the message types and participants).

When using the Service Broker, messages exchanged within a conversation are sent between the abstraction layer - the .

Within the Service Broker - a is defined to represent the network path that messages to a service will follow.

When defining a route within the Service Broker, you must set the parameter to be so it knows that the service it points to resides on the instance of SQL Server you are adding the route to.

Within the Service Broker, a can be assigned to a in order to fast track communications to a service.

When using the Service Broker service queues, what are the equivalent commands to the typical table commands used for database tables?

INSERT:
SELECT:

When sending a message through the Service Broker, the command can be used with multiple ID's as parameters, in order to send the message to multiple services. This process is known as .