Carter - Expert Scripting and Automation for SQL Server DBAs
Here you can read online Carter - Expert Scripting and Automation for SQL Server DBAs full text of the book (entire story) in english for free. Download pdf and epub, get meaning, cover and reviews about this ebook. City: Berkeley;CA, year: 2016, publisher: Apress; Apress : Imprint: Apress, genre: Computer. Description of the work, (preface) as well as reviews are available. Best literature library LitArk.com created for fans of good reading and offers a wide selection of genres:
Romance novel
Science fiction
Adventure
Detective
Science
History
Home and family
Prose
Art
Politics
Computer
Non-fiction
Religion
Business
Children
Humor
Choose a favorite category and find really read worthwhile books. Enjoy immersion in the world of imagination, feel the emotions of the characters or learn something new for yourself, make an fascinating discovery.
Expert Scripting and Automation for SQL Server DBAs: summary, description and annotation
We offer to read an annotation, description, summary or preface (depends on what the author of the book "Expert Scripting and Automation for SQL Server DBAs" wrote himself). If you haven't found the necessary information about the book — write in the comments, we will try to find it.
Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse.
The market has seen a trend towards there being a much smaller ratio of DBAs to SQL Server instances. Automation is the key to responding to this challenge and continuing to run a reliable database platform service.Expert Scripting and Automation for SQL Server DBAsguides you through the process of automating the maintenance of your SQL Server enterprise.
Expert Scripting and Automation for SQL Server DBAsshows how to automate the SQL Server build processes, monitor multiple instances from a single location, and automate routine maintenance tasks throughout your environment. You will also learn how to create automated responses to common or time consuming break/fix scenarios. The book helps you become faster and better at what you do for a living, and thus more valuable in the job market.Extensive coverage of automation using PowerShell and T-SQL
Detailed discussion and examples on metadata-driven automation
Comprehensive coverage of automated responses to break/fix scenariosWhat You Will Learn
Automate the SQL Server build process
Create intelligent, metadata-drive routines
Automate common maintenance tasks
Create automated responses to common break/fix scenarios
Monitor multiple instance from a central location
Utilize T-SQL and PowerShell for administrative purposes
Who This Book Is For
Expert Scripting and Automation for SQL Server DBAsis a book for SQL Server database administrators responsible for managing increasingly large numbers of databases across their business enterprise. The book is also useful for any database administrator looking to ease their workload through automation. The book addresses the needs of these audiences by showing how to get more done through less effort by implementing an intelligent, automated-processes service model using tools such as T-SQL, PowerShell, Server Agent, and the Management Data Warehouse.
The market has seen a trend towards there being a much smaller ratio of DBAs to SQL Server instances. Automation is the key to responding to this challenge and continuing to run a reliable database platform service.Expert Scripting and Automation for SQL Server DBAsguides you through the process of automating the maintenance of your SQL Server enterprise.
Expert Scripting and Automation for SQL Server DBAsshows how to automate the SQL Server build processes, monitor multiple instances from a single location, and automate routine maintenance tasks throughout your environment. You will also learn how to create automated responses to common or time consuming break/fix scenarios. The book helps you become faster and better at what you do for a living, and thus more valuable in the job market.Extensive coverage of automation using PowerShell and T-SQL
Detailed discussion and examples on metadata-driven automation
Comprehensive coverage of automated responses to break/fix scenariosWhat You Will Learn
Automate the SQL Server build process
Create intelligent, metadata-drive routines
Automate common maintenance tasks
Create automated responses to common break/fix scenarios
Monitor multiple instance from a central location
Utilize T-SQL and PowerShell for administrative purposes
Who This Book Is For
Expert Scripting and Automation for SQL Server DBAsis a book for SQL Server database administrators responsible for managing increasingly large numbers of databases across their business enterprise. The book is also useful for any database administrator looking to ease their workload through automation. The book addresses the needs of these audiences by showing how to get more done through less effort by implementing an intelligent, automated-processes service model using tools such as T-SQL, PowerShell, Server Agent, and the Management Data Warehouse.
Carter: author's other books
Who wrote Expert Scripting and Automation for SQL Server DBAs? Find out the surname, the name of the author of the book and a list of all author's works by series.
Carter
Peter A. Carter
Joseph D’Antoni
Vinith Menon
Expert Scripting and Automation for SQL Server DBAs — read online for free the complete book (whole text) full work
Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "Expert Scripting and Automation for SQL Server DBAs" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.
Light
Font size:
↓
↑
Reset
Interval:
↓
↑
Bookmark:
Make
Peter A. Carter 2016
Peter A. Carter Expert Scripting and Automation for SQL Server DBAs 10.1007/978-1-4842-1943-0_11. T-SQL Techniques for DBAs
Peter A. Carter 1
(1)
Botley, UK
Electronic supplementary material
The online version of this chapter (doi: 10.1007/978-1-4842-1943-0_1 ) contains supplementary material, which is available to authorized users.
As a DBA, you will almost certainly understand the basics of the T-SQL language and use it for querying metadata views, such as sys.databases or sys.tables . Often, DBAs do not have advanced T-SQL skills, however, as they do not need them in day-to-day operations. In order to implement sophisticated automation and, in turn, reduce operational costs and overheads, DBAs should gain an understanding of some more advanced scripting techniques. Therefore, this chapter will focus on exploring some of the T-SQL techniques that we will use in this book. I will make the assumption that if you are reading this book, you are familiar with rudimentary T-SQL, such as the SELECT statement, including FROM , JOIN , WHERE , GROUP BY , HAVING , and ORDER BY clauses. If you do need a refresher, however, full details of the SELECT statement can be found at https://msdn.microsoft.com/en-us/library/ms189499.aspx # .
We will start by looking at the APPLY operator to call a function against rows within a result set. We will then look at how XML (eXtensible Markup Language) and how the native XML data type can be harnessed by SQL Server DBAs. It is critical for DBAs to have a handle on the use of XML, due to the volume of metrics and information that are exposed via this data type. We will then explore how to efficiently iterate through multiple objects.
Using the APPLY Operator
The T-SQL APPLY operator allows you to call a table valued function against every row in a result set returned by a query. There are two variations of the APPLY operator: CROSS APPLY and OUTER APPLY . When CROSS APPLY is used, the query will only return rows in which a result set has been produced by the table valued function. When OUTER APPLY is used, no filter will be applied to the result set, and where no result is returned by the table valued function, NULL will be returned, in each column of the table valued function.
The APPLY operator can be very useful to DBAs when they are retrieving metadata from dynamic management views (DMV) and functions (DMF). For example, the query in Listing will return a list of all sessions, detailing the session ID, the login time, the login name, and whether the process is a user or system process. It will then use OUTER APPLY to run the sys.dm_exec_sql_text dynamic management function against each row. This function returns a column called text, which is the SQL statement associated with the SQL handle in the sql_handle column in the sys.dm_exec_requests dynamic management view.
Note
The text column that is referenced in the SELECT list is the column returned by the sys.dm_exec_sql_text DMF.
SELECT s.session_id
,s.login_time
,s.login_name
,s.is_user_process
,[text]
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) ;
Listing 1-1.
Using OUTER APPLY
You will notice that this query returns many rows in this result set with NULL values for the text column. This is because they are system processes, running background tasks for SQL Server, such as the Lazy Writer and the Ghost Cleanup Task.
Tip
We can be sure that they are system processes because of the is_user_process flag. We will not rely on the session ID being less than 50. The assertion that all system processes have a session ID of less than 50 is widely believed, but also a fallacy, because there can potentially be more than 50 system sessions running in parallel.
If we use the CROSS APPLY operator for the same query, as shown in Listing , the only rows that will be returned will be rows where the result of applying the tabled valued function is not NULL .
SELECT s.session_id
,s.login_time
,s.login_name
,[text]
,s.is_user_process
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) ;
Listing 1-2.
Using APPLY
Understanding XML
XML is a markup language, similar to HTML, that was designed for the purpose of storing and transporting data. Like HTML, XML consists of tags. Unlike HTML, however, these tags are not predefined. Instead, they are defined by the document author. An XML document has a tree structure, beginning with a root node and containing child nodes (also known as child elements). Each element can contain data but also attributes. Each attribute can contain data that describes the element. For example, imagine that you require details of sales orders to be stored in XML format. It would be sensible to assume that each sales order would be stored in a separate element within the document. But what about sales order properties, such as order date, customer ID, product IDs, quantities, and prices? These pieces of information could either be stored as child elements of the sales order element, or they could be stored as attributes of the sales order element. There are no set rules for when you should use child elements or attributes to describe properties of an element. This choice is at the discretion of the document author. The XML document in Listing provides a sample XML document, which holds the details of sales orders for a fictional organization.
Listing 1-3.
Sales Orders Stored in an XML Document
There are several things to note when looking at this XML document. First, elements begin with the element name, encapsulated within angle brackets. They end with the element name, preceded by a backslash and enclosed in angle brackets. Any elements that fall between these two tags are child elements of the tag.
Attributes are enclosed in double quotation marks and reside within the beginning tag of an element. For example, OrderID is an attribute of the element .
It is acceptable to have repeating elements. You can see that is a repeating element, as two separate sales orders are stored in this XML document. The element is the documents root element and is the only element that is not allowed to be complex. This means that it cannot have attributes and cannot be repeating. Attributes can never repeat within an element. Therefore, if you require a node to repeat, you should use a nested element as opposed to an attribute.
The format of an XML document can be defined by an XSD schema. An XSD schema will define the documents structure, including data types, if complex types (complex elements) are allowed, and how many times an element must occur (or is limited to occurring) within a document. It also defines the sequence of elements. A full description of XSD schemas can be found at en.wikipedia.org/wiki/XML_Schema_(W3C) .
Tip
An XML document requires a root element in order to be well-formed. An XML document without a root element is known as an XML fragment. This is important, because it is not possible to bind an XML fragment to an XSD schema. This means that the structure of the document, including data types, cannot be enforced.
Converting Results Sets to XML
T-SQL allows you to convert relational results sets into XML, by using the FOR XML clause in your SELECT statement. There are four modes that can be used with the FOR XML clause: FOR XML RAW , FOR XML AUTO , FOR XML PATH , and FOR XML EXPLICIT . The following sections will demonstrate how the FOR XML clause works in RAW mode, AUTO mode, and PATH mode. EXPLICIT mode is beyond the scope of this book, as its functionality is very similar to PATH mode but is far more complex and does not often prove useful for DBAs.
Light
Font size:
↓
↑
Reset
Interval:
↓
↑
Bookmark:
Make
Similar books «Expert Scripting and Automation for SQL Server DBAs»
Look at similar books to Expert Scripting and Automation for SQL Server DBAs. We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.
Peter A. Carter
Joseph D’Antoni
Vinith Menon
Reviews about «Expert Scripting and Automation for SQL Server DBAs»
Discussion, reviews of the book Expert Scripting and Automation for SQL Server DBAs and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.