💠🛠️🗒️SQL Server: Schemas [Notes]

Disclaimer: This is work in progress based on notes gathered over the years, intended to consolidate information from the various sources. 

Last updated: 15-Mar-2024

[SQL Server 2005] Schemas

• {def} a collection of database objects that are owned by a single user and form a single namespace
• a named container for database objects
• allows to group objects into separate namespaces
• collection of like objects which provide maintenance and security to those objects as a whole, without affecting objects within other schemas [1]
• reside within databases
• fulfilling a common purpose [1]
• each schema can contain zero or more data structures (aka objects) [1]
• all objects within a schema share 
• a common naming context
• a common security context [10]
• behavior of schema changed 
• ⇐ compared to SQL Server 2000
• schemas are no longer equivalent to database users
• each schema is a distinct namespace that exists independently of the database user who created it
• used as a prefix to the object name
• schema is simply a container of objects [3]
• code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users [3]
• can be owned by any database principal
• this includes roles and application roles  [3]
• its ownership is transferable [3]
• every object is contained by a schema [6]
• anything contained by it has the same owner [6]
• separation of ownership [3]
• ownership of schemas and schema-scoped securables is transferable [3]
• objects can be moved between schemas [3]
• a single schema can contain objects owned by multiple database users  [3]
• multiple database users can share a single default schema  [3]
• permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases  [3]
• each user has a default schema [3]
• user’s default schema is used for name resolution during object creation or object reference [7]
•  {warning} a user might not have permission to create objects in the dbo schema, even if that is the user’s default schema [7]
• when a login in the sysadmin role creates an object with a single part name, the schema is always dbo [7]
• a database user can be dropped without dropping objects in a corresponding schema  [3]
• catalog views designed for earlier releases of SQL Server may return incorrect results
• ⇐ includes sysobjects
• more than 250 new catalog views were introduced to reflect the changes
•  when creating a database object, if you specify a valid domain principal (user or group) as the object owner, the domain principal will be added to the database as a schema. The new schema will be owned by that domain principal [3]
• schema-qualified object name (aka two-part object name)
• if schema is omitted, a schema resolution is performed (aka implicit resolution)
• checks whether the object exists in the user's default schema
• if it doesn't, checks whether it exists in the dbo schema [5]
• extra costs are involved in resolving the object name (aka name resolution) [5]
• uses a spinlock [8]
• in rare occasions a spinlock could not be acquired immediately on such an operation
• this may occur on a system under significant load [8]
• the contention appears on the SOS_CACHESTORE spinlock type [8]
• {resolution} ensure that you always fully qualify your table names [8]
• if multiple objects with the same name exist in different schemas, the wrong object might be retrieved [5]
• improves readability
• {recommendation} always use two-part object names in queries (aka schema-qualify objects) 
• {poor practice} partition data and objects by using only schemas 
• ⇐ instead of creating multiple databases [1]
• {poor practice} complex schemas
• developing a row-based security schema for an entire database using dozens or hundreds of views can create maintenance issues [6]
• {benefit} simplify database object management
• groups of tables can be managed from a single point [4]
• by creation of categories of tables [4]
• helps navigation through database [4]
• allow control permissions at schema level 
• {benefit} provide separation of ownership 
• allows to manage user permissions at the schema level, and then enhance them or override them at the object level as appropriate [10]
• {recommendation} manage database object security by using ownership and permissions at the schema level [2]
• {recommendation} have distinct owners for schemas or use a user without a login as a schema owner [2]
• {recommendation} not all schemas should be owned by dbo [2]
• {recommendation} minimize the number of owners for each schema [2]
• {benefit} enhance security
• by minimizing the risk of SQL injection
• by assigning objects to schema it is possible to drop users without rewriting your applications as the name resolution is no longer depend upon the user or principals names 
• used as an extra hierarchical layer for solution and security management [1]
• gives architects and developers the ability to choose between the types of logical separation of objects they have created, as well as benefit from having a combination of multiple databases and multiple schemas within them [1]
• {type} system schemas
• can't be dropped
• [default schema] dbo
• included in each database 
• if an application needs to  create objects in the under the dbo schema then by granting dbo privileges to the application [12]
• increases the attack surface of the application [12]
• increases the severity if the application is vulnerable to SQL Injection attacks [12]
• can be set and changed by using DEFAULT_SCHEMA option of [3]
• e.g. CREATE USER <user_name> WITH DEFAULT_SCHEMA = <schema_name>
• e.g. ALTER USER <user_name> WITH DEFAULT_SCHEMA = <schema_name>
• if DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema [3]
• [SQL Server 2005] Windows Groups are not allowed to have this property [11]
• [SQL Server 2012] Windows Groups can also have a defined default schema [1]
• streamlines the process of creating users
• if no default schema is specified for a new user, instead is used the default schema of a group where the user is a member [9]
• {warning} not to be confused with the dbo role [6]
• INFORMATION_SCHEMA schema
• an internal, system table-independent view of the SQL Server metadata
• enable applications to work correctly although significant changes have been made to the underlying system tables
• guest schema
• 
  
• sys schema 
• provides a way to access all the system tables and views [7]
• {type} user-defined schemas
• {best practice} assign objects to user-defined schemas
• leaving everything in the dbo schema is like putting everything in the root directory of your hard drive [8]
• it saves the Query Processor a step from having to resolve the schema name out by itself [8]
• avoid ambiguity
• {best practice} assign each user a default schema
•  ensures that if they create an object without specifying a schema, it will automatically go into their assigned container [8]
• {type} role-based schemas
• [SQL Server 2012] every fixed database role has a schema of the same name [7]
• {exception} public role5
• {action} create objects in schema
• {prerequisite}
• schema must exist
• the user creating the object must have permission to create the object, either directly or through role membership [7]
• the user creating the object must  either [7]
• be the owner of the schema 
• be a member of the role that owns the schema
• have ALTER rights on the schema 
• have the ALTER ANY SCHEMA permission in the database
• {recommendation} group like objects together into the same schema [2]
• {operation} create schema
• {recommendation} use two-part names for database object creation and access [2]
• {operation} change schema (aka modify schema)
• when applying schema changes to an object and try to manipulate the object data in the same batch, SQL Server may not be aware of the schema changes yet and fail the data manipulation statement with a resolution error [5]
• the parsing does not check any object names or schemas because a schema may change by the time the statement executes [6]
• triggers a database lock
• invalidates existing query plans
• a new plan will need to be recompiled for the queries as soon as they are run anew
• not allowed on
• [SQL Server 2014] [memory-optimized tables]
• [table variables]
• {best practice} explicitly list column names in statements in case a schema changes 
• {operation} schema dropping
• [windows groups]
• an exception in the SQL Server security model [11]
• a secondary identity with additional capabilities that are traditionally reserved only for primary identities [11]
• require handling not seen in any other security system [11]
•  can simplify management but due to their hybrid nature, they come with some restrictions [11]
• {recommendation} for users mapped to Windows groups, try and limit each Windows user to one Windows group that has database access [2]

Previous Post <<||>> Next Post

References:

[1] 40074A: Microsoft SQL Server 2014 for Oracle DBAs, Microsoft, 2015 

[2] Bob Beauchemin et al (2012) SQL Server 2012 Security Best Practices - Operational and Administrative Tasks [whitepaper]

[3] MSDN (2005)User-Schema Separation [link]

[4] Solid Quality Learning (2007) Microsoft SQL Server 2005: Database Essentials Step by Step

[5] Itzik Ben-Gan (2008) Microsoft® SQL Server® 2008 T-SQL Fundamentals

[6] Adam Jorgensen et al (2012) Microsoft® SQL Server® 2012 Bible

[7] Kalen Delaney et al (2013) Microsoft SQL Server 2012 Internals

[8] Buck Woody (2009) SQL Server Best Practices: User-Defined Schemas [link obsolet]

[9] Microsoft (2014) 10977B: Updating Your SQL Server Skills to SQL Server 2014 (Trainer Handbook)

[10] Microsoft (2012) 20465B Designing Database Solutions for SQL Server 2012 (Trainer Handbook

[11] Laurentiu Cristofor (2008) SQL Server: Windows Groups, default schemas, and other properties [link]

[12] Dan Sellers's WebLog (2006) Post Webcast’s Notes: Securing SQL Server 2005 for Developers  [link]

[13] Microsoft Learn (2024) SQL Server 2022: System Information Schema Views (Transact-SQL)

[14] 

Acronyms:
SQL - Structured Query Language

💎SQL Reloaded: The WINDOW Clause in SQL Server 2022 (Part III: Ranking)

In two previous posts I shown how to use the newly introduced WINDOW clause in SQL Server 2022 for simple aggregations, respectively running totals, by providing some historical context concerning what it took to do the same simple aggregations as SUM or AVG within previous versions of SQL Server. Let's look at another scenario based on the previously created Sales.vSalesOrders view - ranking records within a partition. 

There are 4 ranking functions that work across partitions: Row_Number, Rank, Dense_Rank and NTile. However, in SQL Server 2000 only Row_Number could be easily implemented, and this only if there is a unique identifier (or one needed to create one on the fly):

-- ranking based on correlated subquery (SQL Server 2000+)
SELECT SOL.SalesOrderId 
, SOL.ProductId
, SOL.OrderDate
, SOL.[Year]
, SOL.[Month]
, SOL.OrderQty
, (-- correlated subquery
  SELECT count(SRT.SalesOrderId)
  FROM Sales.vSalesOrders SRT
  WHERE SRT.ProductId = SOL.ProductId 
    AND SRT.[Year] = SOL.[Year]
	AND SRT.[Month] = SOL.[Month]
    AND SRT.SalesOrderId <= SOL.SalesOrderId
   ) RowNumberByDate
FROM Sales.vSalesOrders SOL
WHERE SOL.ProductId IN (745)
  AND SOL.[Year] = 2012
  AND SOL.[Month] BETWEEN 1 AND 3
ORDER BY SOL.[Year]
, SOL.[Month]
, SOL.OrderDate ASC

As alternative for implementing the other ranking functions, one could use procedural language for looping, though this approach was not recommendable given the performance concerns.

SQL Server 2005 introduced all 4 ranking functions, as they are in use also today:

-- ranking functions (SQL Server 2005+)
SELECT SOL.SalesOrderId 
, SOL.ProductId
, SOL.OrderDate
, SOL.[Year]
, SOL.[Month]
, SOL.OrderQty
-- rankings
, Row_Number() OVER (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month] ORDER BY SOL.OrderQty DESC) RowNumberQty
, Rank() OVER (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month] ORDER BY SOL.OrderQty DESC) AS RankQty
, Dense_Rank() OVER (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month] ORDER BY SOL.OrderQty DESC) AS DenseRankQty
, NTile(4) OVER (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month] ORDER BY SOL.OrderQty DESC) AS NTileQty
FROM Sales.vSalesOrders SOL
WHERE SOL.ProductId IN (745)
  AND SOL.[Year] = 2012
  AND SOL.[Month] BETWEEN 1 AND 3
ORDER BY SOL.[Year]
, SOL.[Month]
, SOL.OrderQty DESC

Now, in SQL Server 2022 the WINDOW clause allows simplifying the query as follows by defining the partition only once:

-- ranking functions (SQL Server 2022+)
SELECT SOL.SalesOrderId 
, SOL.ProductId
, SOL.OrderDate
, SOL.[Year]
, SOL.[Month]
, SOL.OrderQty
-- rankings

, Row_Number() OVER SalesByMonth AS RowNumberQty
, Rank() OVER SalesByMonth AS RankQty
, Dense_Rank() OVER SalesByMonth AS DenseRankQty
, NTile(4) OVER SalesByMonth AS NTileQty
FROM Sales.vSalesOrders SOL
WHERE SOL.ProductId IN (745)
  AND SOL.[Year] = 2012
  AND SOL.[Month] BETWEEN 1 AND 3
WINDOW SalesByMonth AS (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month] ORDER BY SOL.OrderQty DESC)
ORDER BY SOL.[Year]
, SOL.[Month]
, SOL.OrderQty DESC

Forward (and backward) referencing of one window into the other can be used with ranking functions as well:

 

-- ranking functions with ascending/descending sorting (SQL Server 2022+)
SELECT SOL.SalesOrderId 
, SOL.ProductId
, SOL.OrderDate
, SOL.[Year]
, SOL.[Month]
, SOL.OrderQty
-- rankings (descending)
, Row_Number() OVER SalesByMonthSortedDESC AS DescRowNumberQty
, Rank() OVER SalesByMonthSortedDESC AS DescRankQty
, Dense_Rank() OVER SalesByMonthSortedDESC AS DescDenseRankQty
, NTile(4) OVER SalesByMonthSortedDESC AS DescNTileQty
-- rankings (ascending)
, Row_Number() OVER SalesByMonthSortedASC AS AscRowNumberQty
, Rank() OVER SalesByMonthSortedASC AS AscRankQty
, Dense_Rank() OVER SalesByMonthSortedASC AS AscDenseRankQty
, NTile(4) OVER SalesByMonthSortedASC AS AscNTileQty
FROM Sales.vSalesOrders SOL
WHERE SOL.ProductId IN (745)
  AND SOL.[Year] = 2012
  AND SOL.[Month] BETWEEN 1 AND 3
WINDOW SalesByMonth AS (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month])
, SalesByMonthSortedDESC AS (SalesByMonth ORDER BY SOL.OrderQty DESC)
, SalesByMonthSortedASC AS (SalesByMonth ORDER BY SOL.OrderQty ASC)
ORDER BY SOL.[Year]
, SOL.[Month]
, SOL.OrderQty DESC

Happy coding!

💎SQL Reloaded: The WINDOW Clause in SQL Server 2022 (Part II: Running Totals)

In the previous post I shown how to use the newly introduced WINDOW clause in SQL Server 2022 by providing some historical context concerning what it took to do the same simple aggregations as SUM or AVG within previous versions of SQL Server. Let's look at another scenario based on the previously created Sales.vSalesOrders view - using the same two functions in providing running totals. 

A running total is a summation of a sequence of values (e.g. OrderQty) ordered by a key, typically one or more columns that uniquely define the sequence, something like a unique record identifier (e.g. SalesOrderId). Moreover, the running total can be defined within a partition (e.g. Year/Month).

In SQL Server 2000, to calculate the running total and average for a value within a partition would resume implementing the logic for each attribute in correlated subqueries. One needed thus one subquery for each attribute, resulting in multiple processing of the base tables. Even if the dataset was already in the memory, it still involves a major overhead.

-- running total/average based on correlated subquery (SQL Server 2000+)
SELECT SOL.SalesOrderId 
, SOL.ProductId
, SOL.OrderDate
, SOL.[Year]
, SOL.[Month]
, SOL.OrderQty
, (-- correlated subquery
  SELECT SUM(SRT.OrderQty)
  FROM Sales.vSalesOrders SRT
  WHERE SRT.ProductId = SOL.ProductId 
    AND SRT.[Year] = SOL.[Year]
	AND SRT.[Month] = SOL.[Month]
	AND SRT.SalesOrderId <= SOL.SalesOrderId
   ) RunningTotalQty
, (-- correlated subquery
  SELECT AVG(SRT.OrderQty)
  FROM Sales.vSalesOrders SRT
  WHERE SRT.ProductId = SOL.ProductId 
    AND SRT.[Year] = SOL.[Year]
	AND SRT.[Month] = SOL.[Month]
	AND SRT.SalesOrderId <= SOL.SalesOrderId
   ) RunningAvgQty
FROM Sales.vSalesOrders SOL
WHERE SOL.ProductId IN (745)
  AND SOL.[Year] = 2012
  AND SOL.[Month] BETWEEN 1 AND 3
ORDER BY SOL.SalesOrderId 

Then SQL Server 2005 allowed consolidating the different correlated subqueries into one by using the CROSS APPLY join:

-- running total/average based on correlated subquery with CROSS APPLY (SQL Server 2005+)
SELECT SOL.SalesOrderId 
, SOL.ProductId
, SOL.OrderDate
, SOL.[Year]
, SOL.[Month]
, SOL.OrderQty
, SRT.RunningTotalQty
, SRT.RunningAvgQty
FROM Sales.vSalesOrders SOL
     CROSS APPLY (-- correlated subquery
	  SELECT SUM(SRT.OrderQty) RunningTotalQty
	  , AVG(SRT.OrderQty) RunningAvgQty
	  FROM Sales.vSalesOrders SRT
	  WHERE SRT.ProductId = SOL.ProductId 
		AND SRT.[Year] = SOL.[Year]
		AND SRT.[Month] = SOL.[Month]
		AND SRT.SalesOrderId <= SOL.SalesOrderId
   ) SRT
WHERE SOL.ProductId IN (745)
  AND SOL.[Year] = 2012
  AND SOL.[Month] BETWEEN 1 AND 3
ORDER BY SOL.SalesOrderId 

Even if SQL Server 2005 introduced window functions that work on a partition, an ORDER BY clause was supported only with SQL Server 2012:

-- running total/average based on window functions (SQL Server 2012+)
SELECT SOL.SalesOrderId 
, SOL.ProductId
, SOL.OrderDate
, SOL.[Year]
, SOL.[Month]
, SOL.OrderQty
, SUM(SOL.OrderQty) OVER (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month] ORDER BY SOL.SalesOrderId) RunningTotalQty
, AVG(SOL.OrderQty) OVER (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month] ORDER BY SOL.SalesOrderId) RunningAvgQty
FROM Sales.vSalesOrders SOL
WHERE SOL.ProductId IN (745)
  AND SOL.[Year] = 2012
  AND SOL.[Month] BETWEEN 1 AND 3
ORDER BY SOL.SalesOrderId 

Now, in SQL Server 2022 the WINDOW clause allows simplifying the query as follows by defining the partition only once:

-- running total/average based on window functions and clause (SQL Server 2022+)
SELECT SOL.SalesOrderId 
, SOL.ProductId
, SOL.OrderDate
, SOL.[Year]
, SOL.[Month]
, SOL.OrderQty
, SUM(SOL.OrderQty) OVER SalesByMonth AS RunningTotalQty

, AVG(SOL.OrderQty) OVER SalesByMonth AS RunningAvgQty

FROM Sales.vSalesOrders SOL
WHERE SOL.ProductId IN (745)
  AND SOL.[Year] = 2012
  AND SOL.[Month] BETWEEN 1 AND 3
WINDOW SalesByMonth AS (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month] ORDER BY SOL.SalesOrderId)
ORDER BY SOL.SalesOrderId 

Moreover, the WINDOW clause allows forward (and backward) referencing of one window into the other:

-- running total/average based on window functions and clause (SQL Server 2022+)
SELECT SOL.SalesOrderId 
, SOL.ProductId
, SOL.OrderDate
, SOL.[Year]
, SOL.[Month]
, SOL.OrderQty
-- simple aggregations
, SUM(SOL.OrderQty) OVER SalesByMonth AS TotalQty

, AVG(SOL.OrderQty) OVER SalesByMonth AS AvgQty

-- running totals
, SUM(SOL.OrderQty) OVER SalesByMonthSorted AS RunningTotalQty

, AVG(SOL.OrderQty) OVER SalesByMonthSorted AS RunningAvgQty

FROM Sales.vSalesOrders SOL
WHERE SOL.ProductId IN (745)
  AND SOL.[Year] = 2012
  AND SOL.[Month] BETWEEN 1 AND 3
WINDOW SalesByMonth AS (PARTITION BY SOL.ProductId, SOL.[Year], SOL.[Month])
, SalesByMonthSorted AS (SalesByMonth ORDER BY SOL.SalesOrderId)
ORDER BY SOL.SalesOrderId 

Happy coding!

💠🛠️SQL Server: Administration (Database Recovery on SQL Server 2017)

I installed today SQL Server 2017 CTP 2.1 on my Lab PC without any apparent problems. It was time to recreate some of the databases I used for testing. As previously I had an evaluation version of SQL Server 2016, it expired without having a backup for one of the databases. I could recreate the database from scripts and reload the data from various text files. This would have been a relatively laborious task (estimated time > 1 hour), though the chances were pretty high that everything would go smoothly. As the database is relatively small (about 2 GB) and possible data loss was neglectable, I thought it would be possible to recover the data from the database with minimal loss in less than half of hour. I knew this was possible, as I was forced a few times in the past to recover data from damaged databases in SQL Server 2005, 2008 and 2012 environments, though being in a new environment I wasn’t sure how smooth will go and how long it would take.  

Plan A - Create the database with  ATTACH_REBUILD_LOG option:

As it seems the option is available in SQL Server 2017, so I attempted to create the database via the following script:
 

CREATE DATABASE  ON 
(FILENAME='I:\Data\.mdf') 
FOR ATTACH_REBUILD_LOG 

And as expected I run into the first error:

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "I:\Data\.mdf". Operating system error 5: "5(Access is denied.)".

Msg 1802, Level 16, State 7, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

It looked like a permissions problem, though I wasn’t entirely sure which account is causing the problem. In the past I had problems with the Administrator account, so it was the first thing to try. Once I removed the permissions for Administrator account to the folder containing the database and gave it full control permissions again, I tried to create the database anew using the above script, running into the next error:

File activation failure. The physical file name "D:\Logs\_log.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 1 Could not open new database ''. CREATE DATABASE is aborted.

This approach seemed to lead nowhere, so it was time for Plan B.

Plan B - Recover the database into an empty database with the same name:

Step 1: Create a new database with the same name, stop the SQL Server, then copy the old file over the new file, and delete the new log file manually. Then restarted the server. After the restart the database will appear in Management Studio with the SUSPECT state.

Step 2: Set the database in EMERGENCY mode:

ALTER DATABASE  SET EMERGENCY, SINGLE_USER

Step 3: Rebuild the log file:

ALTER DATABASE <database_name> 

REBUILD LOG ON (Name=’_Log', 

FileName='D:\Logs\.ldf')

The rebuild worked without problems.

Step 4: Set the database in MULTI_USER mode:

ALTER DATABASE  SET MULTI_USER 

Step 5: Perform a consistency check:

DBCC CHECKDB () WITH ALL_ERRORMSGS, NO_INFOMSG 

After 15 minutes of work the database was back online.

Warnings:
Always attempt to recover the data for production databases from the backup files! Use the above steps only if there is no other alternative!
The consistency check might return errors. In this case one might need to run CHECKDB with REPAIR_ALLOW_DATA_LOSS several times [2], until the database was repaired.
After recovery there can be problems with the user access. It might be needed to delete the users from the recovered database and reassign their permissions!  

Resources:
[1] In Recovery (2008) Creating, detaching, re-attaching, and fixing a SUSPECT database, by Paul S Randal [Online] Available from: https://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/ 
[2] In Recovery (2009) Misconceptions around database repair, by Paul S Randal [Online] Available from: https://www.sqlskills.com/blogs/paul/misconceptions-around-database-repair/
[3] Microsoft Blogs (2013) Recovering from Log File Corruption, by Glen Small [Online] Available from: https://blogs.msdn.microsoft.com/glsmall/2013/11/14/recovering-from-log-file-corruption/

💠SQL Server: SQL Server 2012 is almost here [new feature]

I was quite quiet for the past 3-4 months, and this not because of the lack of blogging material, but lack of time. Instead of writing I preferred reading, diving in some special topics related to SQL Server (e.g. tempdb and security), in the near future following to post some of my notes. For short time I was busy learning for ITIL® v3 Foundation Certification, the topics on Knowledge Management giving me more ideas for several posts waiting in the pipe. I started also the online “Introduction to Databases” course offered by Stanford University, attempting thus a scholastic approach of the topic, of importance being the material on Relational Algebra, material I didn’t had the chance to study in the past.

From my perspective, during this time two  important events related to SQL Server took place – the launch of AX Dynamics 2012 and, more recently, the introduction of SQL Server 2012 at PASS (The Professional Association of SQL Server) 2011.

SQL Server 2012

At PASS Summit 2011 were disclosed 4 of the newest SQL Server Products: SQL Server 2012 (code Denali), Power View (code Crescent), ColumnStore Index (code Apollo) and SQL Server Data Tools (code Juneau). The PASS 2011 streamed sessions are available online with quite interesting materials on SQL Server topics like application and database development, database administration and deployment, BI, etc. If you want to learn more about SQL Server, check the CTP 3 Product Guide, which contains datasheets, white papers, technical presentations, demonstrations and links to videos, or the SQL Server 2012 Developer Training Kit Preview (requires Microsoft’s Web Platform Installer).

Dynamics AX 2012

Because lately I’ve been spending more and more time with Dynamics AX, Microsoft’s ERP (Enterprise Resource Planning) solution, I’d like to include related content in my posts, at least presenting resources if I can’t get yet into technical stuff. As its backend is based mainly on SQL Server, AX is the perfect environment to see SQL Server at work, or to perform configuration and administration activities. In addition, AX material (best/good practices, methodologies, various other papers) related to SQL Server could be extended to other environments. I’m saluting Microsoft’s decision of making available publicly more Technet and MSDN content, previously most of the technical content being accessible mainly though Microsoft’s Partner Network and Customer Network. A good compilation of resources is available on AX Technical Support Blog and Inside Microsoft Dynamics AX blog.

As pointed above, recently was launched Microsoft Dynamics AX 2012 (see global and local launch events).  It’s interesting to point out that, with this edition, SSRS becomes the reporting platform for AX, a considerable step forward.

Books

In what concerns the free books there are 3 free “new” appearances: Jonathan Kehayias and Ted Krueger’s book Troubleshooting SQL Server: A Guide for the Accidental DBA (zipped PDF), which provides a basic approach to troubleshooting, Fabiano Amorim’s book on Complete Showplan Operators (PDF, Epub), and Ross Mistry and Stacia Misner’s Introducing Microsoft SQL Server 2008 R2 (PDF, requires registration).