My articles published on SSWUG.org from March 2014 to July 2014

The following is the list of my articles published on SSWUG.org from March 2014 to July 2014. To read these articles, you must have SSWUG.org standard-level membership.

SSWUG.org logo


SQL Server 2014 Replication, Published on July 29, 2014

You use replication to move data between servers. Replication is important in distributed environments, when archiving data, and as an inexpensive way to support high availability.

This article explores the different types of replication, the components involved in replication, and the steps necessary to configure replication.


Microsoft SQL Server 2014: Database Engine key new features, Published on July 15, 2014

Earlier this year, Microsoft released SQL Server 2014. Like previous releases of SQL Server, Microsoft further enhances the SQL Server Database Engine. Microsoft not only improved existing SQL Server Database Engine features, but also introduces many new Database Engine features, including new in-memory OLTP engine (aka Hekaton), Buffer Pool Extension (BPE), Updateable Columnstore indexes, encryption for backups, Resource Governor enhancements for physical IO control, AlwaysOn enhancements, SQL Server Data Files in Windows Azure, Windows Azure Integrated Backups, new design for Cardinality Estimation, Incremental Statistics, Security enhancements, Transact-SQL enhancements, and so on. With the help of these new features of SQL Server 2014 Database Engine we can design, build and deploy mission-critical database applications.

This three part article series discusses in-detail the key new features of the Microsoft SQL Server 2014 Database Engine.


Developing Report Models, Published on July 8, 2014

A report model is a metadata description of a data source. It contains a business model of a data, a physical model of the underlying database, and a mapping between the two. The business model, also known as a semantic model, describes the data by using familiar business names, usually recorded in a business lexicon.

This article reviews the benefit of using report models.


Guidelines and Best Practices for developing and implementing a Reporting Solution, Published on June 6, 2014

Your reporting solution design should match technologies provided by Microsoft SQL Server™ 2014 Reporting Services to the requirements and abilities of users, and the requirements of the business. The documents that make up your reporting specification should also describe the purpose, data content, layout, and user interaction that are required for each report.

After you have an appropriate design in place, the next step is to develop the reports as effectively as possible by using appropriate tools that Microsoft SQL Server 2014 Data Tools (SSDT 2014) provides. SQL Server 2014 Data Tools (SSDT 2014) is a Microsoft Visual Studio environment with enhancements that are specific to business intelligence solutions. SQL Server 2014 Data Tools (also known as SSDT 2014) is included with Microsoft SQL Server™ 2014. You use SQL Server 2014 Data Tools for creating and managing solutions and projects for Reporting Services reports and report-related items. SQL Server 2014 Data Tools provides the Report Designer authoring environment. In Report Designer, you can open, modify, preview, save, and deploy report definitions, shared data sources, shared datasets, and report parts. For more information about SQL Server 2014 Data Tools solutions, projects, project templates, and configurations used for Reporting Services, and the views, menus, toolbars, and shortcuts that you can use in Report Designer, refer to SQL Server 2014 Books Online article “Reporting Services in SQL Server 2014 Data Tools (SSDT 2014)”.

This two part article series offers up some basic high-level guidelines, best practices and considerations for developing and implementing reporting solutions with SQL Server 2014 Data Tools (SSDT).


Handling blocks and deadlock in SQL Server, Published on May 15, 2014

A database server should be able to service requests from a large number of concurrent users. When a database server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it.

This article offers up several options for troubleshooting and resolving blocks in SQL Server.


Understanding the purpose of SQL Server Agent, Published on April 21, 2014

The SQL Server Agent is a Windows service that runs scheduled jobs. A job is a set of one or more management tasks. The SQL Server Agent service depends on the SQL Server service.

This two part article series shows you how you can configure and manage SQL Server Agent service and jobs.


SQL Server Integration Service (SSIS): Import and Export wizards, Published on April 14, 2014

SQL Server includes SQL Server Integration Services (SSIS). SSIS is primarily a data transform and load utility, but its functionality goes beyond that. SSIS provides a flexible development environment that you can use to identify sources and destinations, processing actions, and decision logic. Several SQL Server data utilities, such as the Database Copy Wizard, are based on SSIS.

This article shows technique of importing and exporting data to and from SQL Server database using SQL Server Import and Export Wizard.


Diving deep into SQL Server Integration Services Transactions, Published on March 31, 2014

A transaction is a logical unit of work made up of one or more tasks. The concept of transaction usually applies to a relational database such as those in Microsoft SQL Server. However, in Integration Services, you can create a transaction that includes any task or container. In general, a transaction is considered to have four primary characteristics: atomicity, consistency, isolation, and durability (ACID).

This article provides an overview of transactions and there characteristics. It also shows how you can implement transactions in an SQL Server Integration Services packages.


I hope you will find these articles useful. 🙂

Have a good weekend!

Basit

Advertisements

Availability databases in unhealthy data synchronization state (Error: 35285, Severity: 16, State: 1.)

After power surge last night, I realized few availability databases (also known as a “database replica”) have an unhealthy data synchronization state. What I mean from unhealthy is that they have a status of “Not Synchronizing” in SQL Server Management Studio for both primary and all secondary availability group replicas (See below):

avalability_group_011

According to MSDN documentation here, this issue can be caused by the following:

  • The availability replica might be disconnected.
  • The data movement might be suspended.
  • The database might not be accessible.
  • There might be a temporary delay issue due to network latency or the load on the primary or secondary replica.

To investigate which one of those is relevant in my case, I tried to access the database in SQL Server Management Studio, but received the error similar to the one below:

The target database, ‘YourDatabase’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)

I also searched the SQL Server ErrorLogs for more information, and found following errors:

04/02/2014 12:06:23,spid37s,Unknown,AlwaysOn Availability Groups data movement for database ‘YourDatabase’ has been suspended for the following reason: “failover from partner” (Source ID 1; Source string: ‘SUSPEND_FROM_PARTNER’). To resume data movement on the database<c/> you will need to resume the database manually. For information about how to resume an availability database<c/> see SQL Server Books Online.

04/02/2014 12:06:23,spid37s,Unknown,The recovery LSN (969:3766:1) was identified for the database with ID 7. This is an informational message only. No user action is required.

04/02/2014 12:06:23,spid37s,Unknown,Error: 35285<c/> Severity: 16<c/> State: 1.

04/02/2014 12:06:23,spid37s,Unknown,AlwaysOn Availability Groups connection with primary database established for secondary database ‘YourDatabase’ on the availability replica with Replica ID: {f46ea26e-3162-4f49-97c6-b93946e78733}. This is an informational message only. No user action is required.

This information from ErrorLogs is quiet useful, as it tells me what’s wrong and how I can fix this issue. First, it tells that the attempt was made to connect to the primary availability replica. Then, it shows the “recovery LSN” for the database. And, finally, it tells that the Availability Groups data movement for database has been suspended, and we should manually resume the movement.

Solution: Resuming data movement on the database manually

To resume data movement on the database manually, I simply execute the following Transact-SQL statement on primary and all secondary replicas for the databases that are showing this status:


ALTER DATABASE [YourDatabase] SET HADR RESUME

After I issued this statement, the database is successfully synchronized.

Further Reading

Determining when statistics were last updated in SQL Server?

I received an email from friend today asking how he can see when statistics were last updated in SQL Server. Well, in SQL Server, there are two ways to access the last modified date of a statistic, which are:

  1. Through the header information using DBCC SHOW_STATISTICS.
  2. Through STATS_DATE() function and sys.stats system catalog view.

1) Through the header information using DBCC SHOW_STATISTICS

According to Microsoft Books Online, DBCC SHOW_STATISTICS returns the header, histogram, and vector density based on the data stored in the statistics object. The syntax lets you specify a table or indexed view along with a target index name, statistics name, or column name (see below):

USE [<Database_Name>]
GO

DBCC SHOW_STATISTICS (table_or_indexed_view_name 
                     ,index_or_statistics_name_coloumn_name') 
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]
< option > :: =
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

Example: Returning all statistics information

The following example returns all statistics information for the IXC_ServerChecks_Results_LoadID index of the ServerChecks.Results table.

USE [SQLComplianceDW]
GO

DBCC SHOW_STATISTICS('ServerChecks.Results'
                     ,IXC_ServerChecks_Results_LoadID)

01

The first result set of the DBCC SHOW_STATISTICS command returns the header information, including when the statistics were last updated. To only return the header information about the statistic, you executed DBCC SHOW_STATISTICS with STATS_HEADER option.

USE [SQLComplianceDW]
GO

DBCC SHOW_STATISTICS('ServerChecks.Results', IXC_ServerChecks_Results_LoadID) WITH STAT_HEADER;

02

Note that STAT_HEADER, HISTOGRAM and DENSITY_VECTOR options are only available in SQL Server 2005 and later releases.

As you can see from above example, DBCC SHOW_STATISTICS returns statistics information for the index name, statistics name, or column name of the specified table or in indexed view, however, if you only want to see the statistics update date for all statistics objects that exists for the tables, indexes, and indexed views in the database, you query sys.stats and use STATS_DATE() function.

2) Through STATS_DATE() function and sys.stats system catalog view

According to SQL Server Books Online, sys.stats system catalog view is the best way to see each statistics object information that exists for the tables, indexes, and indexed views in the database. This catalog view exists in SQL Server 2005 and later. You can use this system catalog view with STATS_DATE() function, to view most recent update date for each statistics object that exists for the tables, indexes, and indexed views in the database. This function accepts two parameters, that is, object_id, stats_id. To determine date when the statistics where last updated, you execute sys.stats system catalog view with STATS_DATE() function, as follow:

SELECT OBJECT_NAME(object_id) AS [ObjectName]
      ,[name] AS [StatisticName]
      ,STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats;

03

As you can see, this query returns date of the most recent update for statistics on a table or indexed view.

References

The PUBLIC role – Do not use it for database access!

01As per Microsoft Books Online and SQL Server Security best practice white paper, it is recommended to periodically review privileges granted to public role, and revoke any unnecessary privileges assigned to this role. This is because public role is a special database role that exists in every user database, and by default, every database user is automatically assigned to this built-in role. This role is similar to Windows NT Everyone group, for example, if you grant privileges to this role, then all members of this role automatically get’s this permission. Due to this reason, when locking down access controls, then, we need to look at each individual user’s privileges as well as the privileges assigned to public role.

You can use the following query, which lists all privileges that has been granted to public database role in the specified database:

USE [<Database_Name>] -- Specify database name
GO

WITH [PublicRoleDBPermissions]
AS (
	SELECT p.[state_desc] AS [PermissionType]
		,p.[permission_name] AS [PermissionName]
		,USER_NAME(p.[grantee_principal_id]) AS [DatabaseRole]
		,CASE p.[class]
			WHEN 0
				THEN 'Database::' + DB_NAME()
			WHEN 1
				THEN OBJECT_NAME(major_id)
			WHEN 3
				THEN 'Schema::' + SCHEMA_NAME(p.[major_id])
			END AS [ObjectName]
	FROM [sys].[database_permissions] p
	WHERE p.[class] IN (0, 1, 3)
		AND p.[minor_id] = 0
	)
SELECT [PermissionType]
	,[PermissionName]
	,[DatabaseRole]
	,SCHEMA_NAME(o.[schema_id]) AS [ObjectSchema]
	,[ObjectName]
	,o.[type_desc] AS [ObjectType]
	,[PermissionType] + ' ' + [PermissionName] + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME([ObjectName]) + ' TO ' + QUOTENAME([DatabaseRole]) AS [GrantPermissionTSQL]
	,'REVOKE' + ' ' + [PermissionName] + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME([ObjectName]) + ' TO ' + QUOTENAME([DatabaseRole]) AS [RevokePermissionTSQL]
FROM [PublicRoleDBPermissions] p
INNER JOIN [sys].[objects] o
	ON o.[name] = p.[ObjectName]
		AND OBJECTPROPERTY(o.object_id, 'IsMSShipped') = 0
WHERE [DatabaseRole] = 'Public'
ORDER BY [DatabaseRole]
	,[ObjectName]
	,[ObjectType]

This query is written using following two system catalogs: sys.database_permissions and sys.objects, and returns the following columns:

  • PermissionType – Returns the type of granted permission.
  • PermissionName – Returns the name of permission.
  • DatabaseRole – Returns the public role name.
  • ObjectSchema – Returns the schema of database securable, to which the securable belongs.
  • ObjectName – Returns the name of the database securable.
  • ObjectType – Returns the type of database object.
  • GrantPermissionTSQL – Returns the statements to grant public database role access on all database objects. Note: The results of this statement are used for rollback purposes.
  • RevokePermissionTSQL – Returns statements to revoke public database role access from all database objects, where it has permissions.

Sample Ouput

img01

img02

If this query returns rows, this means that unnecessary privileges have been granted to public role. To remove these unnecessary privileges, create a user defined database role, and grant this user define database role same permissions that has been granted to public role. Once done, add required database users to this role, and after that, revoke the permissions assigned to public database roles.

For more information about SQL Server Server-Level and Database-Level roles, see Server-Level Roles and Database-Level Roles.

Find the (available) amount of memory available to SQL Server (instance / machine)

From time to time, I see the following question posted on various SQL forums asking how we can determine the (available / assigned) amount of memory for an SQL Server instance / machine. As we understand that there is no common script or procedure, which we can use in all versions of SQL Server to determine the amount of memory available and assigned to SQL Server instance and machine. Therefore, I decided to write this blog post, where I’ll share a script for each version of SQL Server that will help us determine the total amount of physical memory available on the operating system and the total memory available in SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2012.

SQL Server 2000 Script:

-- To get the total physical memory installed on SQL Server
CREATE TABLE #OS_Available_Memory (
	ID [int]
	,NAME [sysname]
	,Physical_Memory_In_MB [int]
	,Physical_Memory_In_Bytes [nvarchar](512)
	)

INSERT #OS_Available_Memory
EXEC [master]..[xp_msver]

SELECT [Name]
	,[Physical_Memory_In_MB]
	,[Physical_Memory_In_Bytes]
FROM #OS_Available_Memory
WHERE NAME = 'PhysicalMemory'
GO

DROP TABLE #OS_Available_Memory

--To get the minimum and maximum size of memory configured for SQL Server
SELECT *
FROM [master]..[sysconfigures]
WHERE [comment] IN ('Minimum size of server memory (MB)', 'Maximum size of server memory (MB)')

SQL Server 2005 Script:

-- To get the total physical memory installed on SQL Server
SELECT physical_memory_in_bytes / 1024 / 1024 AS [Physical_Memory_In_MB]
	,virtual_memory_in_bytes / 1024 / 1024 AS [Virtual_Memory_In_MB]
FROM [master].[sys].[dm_os_sys_info]

--To get the minimum and maximum size of memory configured for SQL Server
SELECT [name] AS [Name]
	,[configuration_id] AS [Number]
	,[minimum] AS [Minimum]
	,[maximum] AS [Maximum]
	,[is_dynamic] AS [Dynamic]
	,[is_advanced] AS [Advanced]
	,[value] AS [ConfigValue]
	,[value_in_use] AS [RunValue]
	,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')

SQL Server 2008/200R2 and SQL Server 2012 Script:

-- To get the total physical memory installed on SQL Server
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
	,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
	,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
	,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
	,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
	,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
	,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

--To get the minimum and maximum size of memory configured for SQL Server.
SELECT [name] AS [Name]
	,[configuration_id] AS [Number]
	,[minimum] AS [Minimum]
	,[maximum] AS [Maximum]
	,[is_dynamic] AS [Dynamic]
	,[is_advanced] AS [Advanced]
	,[value] AS [ConfigValue]
	,[value_in_use] AS [RunValue]
	,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')

For SQL Server 2000, I used internal system table and extended stored procedure to find out information about available memory to SQL Server instance and machine whereas for SQL Server 2005 and above, I used system view and dynamic management view to retrieve the same information.

Basit's SQL Server Tips

This article walks the user through installation of SQL Server 2012 on a Windows Server 2008 system using the SQL Server setup installation wizard. The installation process is simple, straight forward and is very similar to SQL Server 2008 / 2008 R2 setup. The procedure described is for a non-clustered server, and can be applied either to a default instance or a named instance. It is intended to be read by Database Administrators and anybody interested in the technical aspects of carrying out this task.

View original post 1,221 more words

Getting Started with Partially Contained Databases

Before SQL Server 2012, when we move or restore databases to a different SQL Server instance, then any logins associated with the database users do not automatically relocates to the new instance. As a result of this, we end up having orphaned users inside databases and these databases do not work straightaway after migration until we create and map logins associated with the database user to the target instance. Obviously, this makes the database less portable, because they depend on the SQL Server instance objects.

Fortunately, SQL Server 2012 comes with the new and robust security feature called partially contained databases, which addresses this vulnerability and makes databases much more portable compared to the earlier versions of SQL Servers. The great advantage of the partially contained databases is that it deals with orphaned users by storing all server-level security metadata and settings, including login details inside contained databases.

Check out my article (ie. Getting Started with Contained Databases) in which discussed this cool security feature of SQL Server 2012.

This article is published on SSWUG.org.