T-SQL ENHANCEMENTS Table 7-2: Versioning Database at SERIALIZABLE

T-SQL ENHANCEMENTS Table 7-2: Versioning Database at SERIALIZABLE Isolation Step User 1 User 2 1 BEGIN TRAN SELECT name FROM tab WHERE id = 1 **value is Name 2 BEGIN TRAN UPDATE tab SET name = Newname WHERE id = 1 3 SELECT name FROM tab WHERE id = 1 **value is Name 4 COMMIT 5 SELECT name FROM tab WHERE id = 1 **value is Name 6 COMMIT 7 SELECT name FROM tab WHERE id = 1 **value is NewName ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON GO USE pubs GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRANS SQL Expressions COMMIT TRANS The SQL expression in the preceding batch will be executed, in effect, against a snapshot of the database that was taken when BEGIN TRANS was executed. Statement-level SNAPSHOTisolation requires the use of an additional database option, READ_COMMITTED_SNAPSHOT. If this database option and ALLOW_ SNAPSHOT_ISOLATIONare ON, all transactions done at the READ UNCOMMITTED or READ COMMITTED levels will be executed as READ COMMITTED level

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

SNAPSHOT ISOLATION Table 7-1: Versioning Database at READ

SNAPSHOT ISOLATION Table 7-1: Versioning Database at READ COMMITTED Isolation Step User 1 User 2 1 BEGIN TRAN SELECT name FROM tab WHERE id = 1 **value is Name 2 BEGIN TRAN UPDATE tab SET name = Newname WHERE id = 1 3 SELECT name FROM tab WHERE id = 1 **value is Name 4 COMMIT 5 SELECT name FROM tab WHERE id = 1 **value is NewName 6 COMMIT 7 SELECT name FROM tab WHERE id = 1 **value is NewName next value until user 1 commits his transaction. He sees the new value only in step 7. In SQL Server this is called transaction-level SNAPSHOT isolation. Both statement- and transaction-level SNAPSHOT isolation require that SNAPSHOT be enabled by using the SNAPSHOT isolation option of the ALTER DATABASE command. The following SQL batch does this for the pubs database. ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON SNAPSHOTisolation can be turned on or off as needed. Once SNAPSHOT isolation has been enabled, transaction-level isolation is used by specifically setting the transaction isolation level to SNAPSHOT. The following SQL batch does this.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

T-SQL ENHANCEMENTS Transaction 1 Transaction 2 Snapshot Transaction

T-SQL ENHANCEMENTS Transaction 1 Transaction 2 Snapshot Transaction 3 Initial Database Snapshot Snapshot Figure 7-1: Snapshot Versioning The row versioning model is built upon having multiple copies of the data. When reading data, the read happens against the copy, and no locks are held. When writing the data, the write happens against the real data, and it is protected with a write lock. For example, in a system implementing row versioning, user A starts a transaction and updates a column in a row. Before the transaction is committed, user B wants to read the same column in the same row. He is allowed to do the read but will read an older value. This is not the value that A is in the process of updating to, but the value A is updating from. In statement-level SNAPSHOT isolation, the reader always reads the last committed value of a given row, just as READ COMMITTEDdoes in a versioning database. Let s say we have a single-row table (called tab) with two columns: ID and name. Table 7-1 shows a versioning database at READ COMMITTEDisolation. The other transaction isolation level in a versioning database, SERIALIZABLE, is always implemented by the behavior that the reader always reads the row as of the beginning of the transaction, regardless of whether other users changes are committed during the duration of the transaction or not. This was shown qualitatively in Figure 7-1. Table 7-2 shows a specific example of how two transactions interoperate when the SERIALIZABLElevel of SNAPSHOTisolation is used. The difference between this table and Table 7-1 occurs at step 5. Even though user 2 has updated a row and committed the update, user 1, using the SERIALIZABLE transaction isolation level, does not see the

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

SNAPSHOT ISOLATION locks pessimistically; that is, they physically

SNAPSHOT ISOLATION locks pessimistically; that is, they physically prevent any access to data that might compromise the required isolation level. In some cases, this will delay a transaction as it waits for a lock to be freed, or may even cause it to fail because of a timeout waiting for the lock. SQL Server 2005 adds SNAPSHOTisolation that, in effect, provides alternate implementations of SERIALIZABLE and READ COMMITTED levels of isolation that use optimistic locking to control concurrent access rather than pessimistic locking. For some applications, SNAPSHOT isolation may provide better performance than pre SQL Server 2005 implementations did. In addition, SNAPSHOTisolation makes it much easier to port database applications to SQL Server from database engines that make extensive use of SNAPSHOTisolation. SQL Server 2005 has two kinds of SNAPSHOTisolation: transaction-level and statement level. Transaction-level SNAPSHOT isolation makes transactions perfect, the same as SERIALIZABLEdoes. Statement-level SNAPSHOT isolation makes transactions that have the same degree of isolation as READ COMMITTEDdoes. The transaction-level SNAPSHOT isolation optimistically assumes that if a transaction operates on an image of that database s committed data when the transaction started, the result will be the same as a transaction run at the SERIALIZABLE isolation level. Some time before the transaction completes, the optimistic assumption is tested, and if it proves not to be true, the transaction is rolled back. Transaction-level SNAPSHOT isolation works by, in effect, making a version of the database by taking a snapshot of it when a transaction starts. Figure 7-1 shows this. There are three transactions in Figure 7-1: transaction 1, transaction 2, and transaction 3. When transaction 1 starts, it is given a snapshot of the initial database. Transaction 2 starts before transaction 1 finishes, so it is also given a snapshot of the initial database. Transaction 3 starts after transaction 1 finishes but before transaction 2 does. Transaction 3 is given a snapshot of the initial database plus all the changes committed by transaction 1. The result of using SERIALIZABLE or transaction-level SNAPSHOT isolation is the same; some transactions will fail and have to be retried, and may fail again, but the integrity of the database is always guaranteed. Of course, SQL Server can t actually make a snapshot of the entire database, but it gets that effect by keeping track of each change to the database until all transactions that were started before the change was made are completed. This technique is called row versioning.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

T-SQL ENHANCEMENTS Hierarchical queries Declarative syntax for tree-based

T-SQL ENHANCEMENTS Hierarchical queries Declarative syntax for tree-based queries PIVOT Declarative syntax aggregations across columns and converting columns to rows APPLY New JOINsyntax made for use with user-defined functions and XML TOP Row count based on an expression Transaction abort TRY/CATCHsyntax for handling errors SNAPSHOT Isolation SQL Server changes the state of a database by performing a transaction on it. Each transaction is a unit of work consisting of one or more steps. A perfect transaction is ACID, meaning it is atomic, consistent, isolated, and durable. In short, this means that the result of performing two transactions on a database, even if they are performed simultaneously by interleaving some of the steps that make them up, will not corrupt the database. Atomic means that a transaction will perform all of its steps or fail and perform none of its steps. Consistent means that the transaction must not leave the results of a partial calculation in the database; for example, if a transaction is to move money from one account to another, it must not terminate after having subtracted money from one account but not having added it to another. Isolated means that none of the changes a transaction makes to a database become visible to other transactions until the transaction making the changes completes, and then they all appear simultaneously. Durable means that changes made to the database by a transaction that completes are permanent, typically by being written to a medium like a disk. A transaction need not always be perfect. The isolation level of a transaction determines how close to perfect it is. Prior to SQL Server 2005, SQL Server provided four levels of isolation: READ UNCOMMITTED, REPEATABLE READ, READ COMMITTED, and SERIALIZABLE. A SERIALIZABLE transaction is a perfect transaction. Functionally, a database could always use SERIALIZABLE that is, perfect transactions, but doing so would typically adversely affect performance. Judicious use of isolation levels other than SERIALIZABLE, when analysis of an application shows that it does not require perfect transactions, will improve performance in these cases. SQL Server uses the isolation level of a transaction to control concurrent access to data through a set of read and write locks. It applies these

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

7 T-SQL Enhancements SQL SERVER 2005 includes new

7 T-SQL Enhancements SQL SERVER 2005 includes new Transact-SQL (T-SQL) functionality. The enhancements span the range from an alternative mechanism for transaction isolation to declarative support for hierarchical queries. And statement-level recompilation even improves existing T-SQL applications that were written before 2005. Improvements to Transact-SQL Microsoft has continually improved the Transact SQL language and the infrastructure of SQL Server itself. In brief, the improvements include the following: SNAPSHOTisolation Additional isolation level that does not use write locks Statement-level recompile More efficient recompilation of stored procedures Event notifications Integration of Data Definition Language (DDL) and DML operations with Service Broker Large data types New data types that deprecate TEXTand IMAGE DDL triggers Triggers that fire on DDL operations Common Table Expressions Declarative syntax that makes a reusable expression part of a query

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

WHERE ARE WE? Where Are We? In Chapter

WHERE ARE WE? Where Are We? In Chapter 2, we started out by declaring that the most important aspect of any database s execution was security. SQL Server 2005, as with every new version of SQL Server, includes features that make the system more secure. SQL Server 2005 mandates password policy enforcement for SQL Server based logins, providing equivalent safety with Windows integrated logins. SQL Server 2005 metadata uses access control like the rest of SQL Server, to prohibit arbitrary access without permission. SQL Server 2005 permits procedural code to specify its execution context, making properly written dynamic SQL safer. And finally, it improves on extended stored procedures with verifiable custom hosted .NET code. Because .NET code can not only access SQL Server objects but also call out to the .NET base class libraries, .NET code inside SQL Server is subject to three levels of checking. The base class libraries are classified to determine which are safe to use, and SQL Server will refuse to load any library deemed to be inapplicable or unsafe. .NET procedural code, including assemblies and user-defined types and aggregates, is subject to normal SQL Server user authorization checks. Finally, SQL Server defines three different security levels for .NET code that can be specified at CREATE ASSEMBLY time. Each of the base class libraries was outfitted with custom permissions that mandate what the assembly will be able to do at each level. This is enforced via .NET code access security.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

SECURITY Create assembly without external access create

SECURITY Create assembly without external access create assembly searchSafe from c:typessearchSafe.dll with permission_set = safe go Then, use the symbolic names to define two versions of the same user- defined function. Create function on assembly with external access create function WebSearchEA(@sym nvarchar(10)) returns real external name searchEA:SearchEngine::WebSearch go Create function on assembly with no external access create function WebSearchSafe(@sym nvarchar(10)) returns real external name searchSafe.SearchEngine.WebSearch go now, attempt to use them declare @a REAL this will work properly SET @a = dbo.GoogleSearchEA( SQL+Server+Yukon ) PRINT @a this fails with a code access security violation SET @a = dbo.WebSearchSafe( SQL+Server+Yukon ) PRINT @a What happens when a stored procedure that has limited access (SAFE) attempts to call a stored procedure that is declared as UNSAFE? Because of the way the stack walk works, this enforces security in a way that is even more restrictive than SQL Server ownership chains. Remember that an ownership chain is only checked when it is broken that is, when the caller is not the owner of the object she attempts to access. The stack walk checks permissions all the way up the stack; failing permission at any level will cause the call to fail. Because System.Security.dll is not allowed, there is no way for an assembly to do an Assert and subvert the stack walk. This may also have some performance impact since it implies that every stack walk goes all the way up to the top. Code access security provides an additional level of security, regardless of the user principal that executes the code.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

WHAT CAN .NET CODE DO FROM WITHIN SQL

WHAT CAN .NET CODE DO FROM WITHIN SQL SERVER In addition to these libraries, SQL Server 2005 may load libraries that are referenced by these libraries. For example, System.Data.dll may have a reference to System.Drawing.dll, which may be loaded, but System.Data.dll will be coded not to reference certain entry points in System.Drawing.dll. The static list of Framework class libraries is enforced at runtime rather than catalog time; attempting to load a library not on the list will not throw an exception at runtime. Let s go through an example of how safety levels would work in practice. The following short program accesses a search service on the Web. This code uses System.Web. public static String WebSearch(String subject) { String url = http://www.websearch.com/search?subject= ; //Submit Web request and get response url = String.Concat(url, subject); WebRequest req = WebRequest.Create(url); WebResponse result = req.GetResponse(); Stream ReceiveStream = result.GetResponseStream(); String outstring = ; //Load response stream into string Byte[] read = new Byte[1024]; int bytes = ReceiveStream.Read(read, 0, 1023); while (bytes > 0) { outstring = String.Concat(outstring, System.Text.Encoding.ASCII.GetString(read, 0, bytes)); bytes = ReceiveStream.Read(read, 0, 512); } return outstring; } We ll use it as part of a class that is compiled into an assembly. Now, we define the assembly to SQL Server, using two CREATEASSEMBLYstatements and two symbolic names with different safety levels. Register the unrestricted access privileged assembly Create assembly with external access create assembly searchEA from c:typessearchEA.dll with permission_set = external_access go

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

SECURITY What these different levels can do is

SECURITY What these different levels can do is enforced by a permission set. Each assembly author indicates which classes and methods might threaten the stability of SQL Server by decorating classes and methods with Host Protection Attributes. These Host Protection Attributes are enforced at execution time (or possibly at JIT-compile time), based on the code s security level. Because SQL Server HPAs and permission sets are documented, third-party library writers are free to instrument their libraries to be sensitive to SQL Server s permissions. Table 6-2 shows a summary of the general behavior of each of the named permission sets. In addition, SQL Server 2005 will load only a certain subset of the Framework class libraries at runtime. The list consists of the following: mscorlib System System.Data System.Data.SqlXml System.Xml System.Security System.Web.Services Microsoft.VisualBasic Microsoft.VisualC CustomMarshalers System.Runtime.Remoting System.Runtime.Serialization.Formatters.Soap Table 6-2: General Behavior of SQL Server Permission Sets Permission Set Guarantees against Information Leakage Guarantees against Elevation Attack (against Malicious Code) Guarantees for Reliability (for Nonmalicious Code) SAFE Yes Yes Yes EXTERNAL_ACCESS No No Yes UNSAFE N o N o N o

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services