Vivek Sanil [MSFT] - SQL Server Premier Field Engineer (PFE)

Did some performance counters like Free Pages disappear in SQL 2012?

2012-11-21T16:38:58Z

Answer: Yes.

Why were the performance counters changed in SQL 2012?: The Memory Manager component of SQLOS was redesigned in SQL Server 2012 and hence some perfmon counters have been changed accordingly. If you have been using the Free Pages performance monitor counter for monitoring SQL memory usage/pressure under the “Buffer Manager”, “Buffer Partition” or “Buffer Node” object in SQL Server 2008 R2 or previous versions then you should know that the Free Pages counter is no longer available in SQL Server 2012. Some counters have been standardized as "kb" rather than bytes or pages and a new Memory Node object has now been added in SQL 2012. Hence instead of Free Pages counter, you can now use Free Memory (kb) or Free Node Memory (KB) in 2012 and instead of Total Pages you can use Total Node Memory (KB) and so on. Also as AWE is now removed from the product, the corresponding counters are also gone.

I queried the sys.dm_os_performance_counters DMV to compare the counters available in SQL 2008 R2 and SQL 2012:

Buffer Node and the new Memory Node counters:

SQL 2008 R2

SQL 2012

Buffer Manager counters:

SQL 2008 R2	SQL 2012

Memory Manager counters:

SQL 2008 R2	SQL 2012

Bottom Line: If you have been using specific counters for monitoring SQL memory usage then be aware of these changes in SQL 2012 and change your monitoring and alerting mechanism accordingly. And it’s not only the perfmon counters that have changed due to redesign of the Memory Manager component, there are changes to some memory related DMVs and the DBCC memory status output.

For a detailed list of all the changes refer to the following blog by the SQLOS team:

http://blogs.msdn.com/b/sqlosteam/archive/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012.aspx

Can I Migrate SQL 2012 AlwaysOn availability groups on a windows 2008 R2 cluster to Windows 2012 cluster?

2012-11-12T16:11:55Z

The Answer is yes. You will need SQL 2012 SP1 to do this. SQL2012 SP1 was announced at the SQL Pass conference last week (Nov 7th).

How: The SQL Server product team will be publishing the steps in the "Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade" white paper. This paper was to be posted in October 2012 on the Microsoft White Papers (SQL Server 2012) download page. However it’s not available yet. So watch the Whitepaper downloads page for updates.

More details on the SP1 Feature can be found here:

Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade

SQL Server 2012 SP1 introduces support for cross-cluster migration of AlwaysOn Availability Groups deployments to a new Windows Server Failover Clustering (WSFC) cluster. A cross-cluster migration moves one AlwaysOn availability group or a batch of availability groups to the new, destination WSFC cluster with minimal downtime. The cross-cluster migration process enables you to maintain your service level agreements (SLAs) when upgrading to a Windows Server 2012 cluster. SQL Server 2012 SP1 must be installed and enabled for AlwaysOn on the destination WSFC cluster. The success of cross-cluster migration depends on thorough planning and preparation of the destination WSFC cluster.

What is the benefit of installing SQL 2012 on windows 2012?

2012-11-12T15:33:51Z

After the release of Windows 2012, I get questions like what is the benefit of installing SQL 2012 on windows 2012 compared to installing it on Windows 2008 R2.

Microsoft recently released the following KB article which answers some of the questions around SQL 2012 support for Windows 8 and Windows 2012

Here is the relevant snippet from the KB article:

The following table summarizes how versions of SQL Server work with some of the new features in Windows 8 and Windows Server 2012:

Note Unless noted in the following table, all features of Windows Server 2012 are supported in all the supported versions of SQL server.

Feature	SQL feature or component that interacts with this new feature	SQL feature that is affected or supported	Minimum version and service pack requirements for SQL	Exceptions or support limitations	Additional information
Storage Spaces			SQL Server 2008 R2 Service Pack 1 or later version, SQL Server 2012 (RTM and later versions)		This feature is supported with the service packs that are specified for the respective versions.
Resilient File System (ReFS)				ReFS is not supported in SQL 2012 and all other down-level versions. In the future, support may be added.
Mitigation of RAM Hardware Errors	LazyWriter	Checksum page sniffer	SQL Server 2012		When SQL Server 2012 is installed on a Windows 2012 operating system with hardware that supports bad memory diagnostics, you will notice new error messages like 854, 855, and 856 instead of the 832 errors that LazyWriter usually generates.
Number of instances per cluster	High availability	Failover Clustering	SQL Server 2012	25 per failover cluster when you use drive letters and up to 50 if you are using SMB file share storage
Cluster Share Volumes (CSV's)				Not supported with any version of SQL Server

Bottom line: Storage Spaces and Mitigation of RAM Hardware errors are the 2 new windows 2012 features that are supported by SQL 2012. Microsoft will update the KB article as more information about SQL working with Windows 2012 features becomes available. Hence watch that space.

KB Article: Using SQL Server in Windows 8 and Windows Server 2012 environments

http://support.microsoft.com/kb/2681562

You might also want to check the Setup Known issues on Windows 2012 section in the same KB article before installing your SQL server.

· Known SQL Server 2012 setup and migration issues

· Known SQL Server 2008 R2 and SQL Server 2008 setup issues

· Additional SQL Server 2012 setup issues

· Setup issues that can occur when the .Net Framework 3.5 is not enabled

Resource Database common Questions

2012-11-02T17:16:25Z

While working with customers, I get quite a few questions on Resource database. Hence I thought about answering some of them in this blog.

Some of the questions that I get:

Can I access it through management studio?
Should I backup my Resource database along with the other system database backups?
Can I change the Resource database path? Should I?
Can the size of Resource database increase over a period a time and with increased usage of the instance?
How to determine the Resource Database version or the last updated date?
How to restore the Resource database?
What should I do if my Resource database gets corrupted?
When does it get updated?
How to detach Resource database?
How to move Resource database?

Discussion in this blog Applies to: SQL 2008, SQL 2008 R2 and SQL 2012

(Note: SQL 2005 Resource database implementation is not included in this discussion; there are a few implementation differences in SQL 2005).

Introduction to Resource Database:

Before I start answering the questions, let me provide some background information on Resource database.

SQL Server has 4 visible system databases i.e. master, model, tempdb and msdb and it also contains a fifth, “hidden” database Resource Database that you will never see using any of the normal SQL commands that list all your databases. It is a read-only database that contains system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Also Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

Database Id: 32767

Actual Name: This database is referred to as the resource database, but its actual name is “mssqlsystemresource”.

Physical File names and path: mssqlsystemresource.mdf and mssqlsystemresource.ldf

Located in: <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\

Questions on Resource Database:

Can I access it through management studio?

In the object explorer you would not be able to see the Resource db.

And if you try to access it through the script window, you will get the following error:

So now shutdown your SQL server instance and start the SQL instance in a single user mode:

Now connect using your management studio and play around with the Resource Database as much as you like :).

Should I backup my Resource database along with the other system database backups?

No. If fact you cannot do a SQL server backup of the Resource database. Treat your resource database as if it were a binary file and not a database file. You can do a manual file copy of the mssqlsystemresource.mdf file. However if for some reason the Resource database has been corrupted or is unusable or unavailable then using the Repair option in the SQL setup would be the preferred method to repair the SQL server installation. If for some reason you still end up having to restore a backup of mssqlsystemresource.mdf file then make sure that you reapply any subsequent service pack/hotfix updates.

Can I change the Resource database path? Should I?

No.

Can the size of Resource database increase over a period a time with increased usage of the instance?

As the Resource database does not contain user data or user metadata its size should not increase with usage of the instance. It would remain constant however it can change with upgrades and service pack installs.

How to determine the Resource Database version or the last updated date?

SELECT SERVERPROPERTY('ResourceVersion') ResourceDatabaseVersion,

SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceDatabaseLastupdatedate;

How to restore the Resource database?

Using the Repair option in the SQL setup would be the preferred method to repair the SQL server installation if Resource database is unavailable. Only other way to restore Resource database is to manually restore a backup of mssqlsystemresource.mdf file. However if you do this then make sure that you reapply any subsequent service pack/hotfix updates.

What should I do if my Resource database gets corrupted?

Refer to the answer for “How to restore Resource database” question.

When does it get updated?

During service pack installs and upgrades.

How to detach Resource database?

Cannot do a SQL detach/attach. You can do a manual file copy of the mssqlsystemresource.mdf file.

How to move Resource database?

Starting SQL 2008 Resource Database cannot be moved.

I will try to add more to his list later.

Thanks