2 SQL Server memory manager

SQL Server 2005/2008/2008R2
SQL server has 2 Memory Manager. Buffer Pool and Memory manager. Buffer Pool manage the single page , the primary consumer for the single pages are Data Buffer and procedure cache. There are other components not data buffer also use single page, it is call stolen page.  When we configure SQL server MAX Memory, we actually only setup the the Max Size of the Buffer Pool ( Single page) only. When SQL Server needs multi pages ( page more than 8K), it switch to new implement memory manger.  components outside of single pages are CLR, MPA, DWA and TS.
SQL Server 2012
Only one memory manager that mange all pages, including Buffer Pool. CLR does not directly manage by memory manager but track through the VAS allocator. components outside of memory manager are DWA and TS.

0 NUMA ( Non Unify Memory Access) & SQL Server

The hardware trend is to increase the number of CPU ( physical or logical) not to increase the CPU frequency speed. All the CPU require access the memory through the system bus ( memory controller) . However, the more CPU needs to go through the system bus, the more contention would happen. One solution is to increase the speed of the system bus. NUMA is born to solve the solution in different way.

NUMA is group the CPU and memory to the Node ( NUMA node). Each NUMA node can have its set of the CPUs , system bus and memory. The basic hardware is has at least 2 NUMA nodes but recently hardware trend can have more than 2 nodes as well.

1 NUMA Support MSSQL Server in AWS

I like to test my lab environment with NUMA. However, since it is not possible to simulate that in VMWare and getting my own physical hardware is too expensive. I have been thinking about using AWS.

After truing out different configuration, I finally got what I need with cr1.8xlarg

Here is the coreinfo output:

1 Unattended install SQL Server 2008R2

Here are some notes for unattended installation of SQL Server 2008R2.

• OS: Windows Server 2008R2
• SQL Server version:  SQL Server 2008R2 Enterprise Edison

 

Enable dotNet feature

0 How to Fix Admin share access error

I have issue access the admin share on Windows server 2008R2 in the domain environment .

The error message is similar to the below screenshot.

Many people suggest to enable below 2 keys and reboot the computer.

reg add HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\LanmanServer\Parameters /v AutoShareServer /t REG_DWORD /d 1

reg add HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\system\ /v LocalAccountTokenFilterPolicy /t REG_DWORD /d 1

I tried the above approach but does not make much difference.

It turns out that somehow didn’t enable the “File and Printer Sharing for Microsoft Networks” in the network card. After I enable it, the admin share works fine without issue.

 

0 Fixed “The trust relationship between this workstation and the primary domain failed”

 

I am messing around with my VM. One of thing I did was clone the VM to another server.

The VM already has join the domain. However, once the new VM has join the domain with new computer name.

When I tried to login the the old VM, I received below error :

The trust relationship between this workstation and the primary domain failed

After goggling the error, I found the solution from here .

The solution is login to the host with the local account and update the domain account password.

NETDOM RESETPWD /Server:<domain controller> /UserD:<domain admin account> /PasswordD:<password>

NETDOM RESETPWD /Server:SGC /UserD:PO /PasswordD:XXXXX

0 Setup Kerberos connection to SQL Server

Kerberos is popular security best practice.  In this post, I am going to work through how to setup the Kerberos connection for SQL Server.

Prerequisite:

Windows domain controller setup and have SQL Server on the same domain but in different host.

Domain Controller KDC

Make sure the KDC is running in the DC.

Make sure the FQDN is pintable from client

In my lab case, the FQDN is sgc1.stargate.com , We basically need to use the pingable name for the SPN.

If the pingable name is SGC1 only, than the SPN must to use the SGC1.

If the pinable name is SGC1.stargate.com. the SPN must be use SGC1.stargate.com

If both are pingable, than we have to use the FQDN which is SGC1.stargate.com

0 SSDT

• Download SSDT from here http://msdn.microsoft.com/en-us/jj650015 
• Down load SSDT-BI from here http://www.microsoft.com/en-us/download/details.aspx?id=36843 

Install SSDT

The installation is base on the ISO image. I don’t have Visual Studio installed on the host before installing SSDT.

Once it is installed, you will see the Visual Studio 2012

The SSDT coming with localDB, which is the local sql server database ( not express version).

Within the SSDT, we only have SQL Server database project for now.

Install SSDT-BI

SSDT-BI installation is much more like SQL Server setup .

Conclusion

After you install both, SSDT and SSDT-BI , you will find the startup menu has been changed. as below :

The additional SQL Server Data Tools for Visual Studio has been added.  I would expect this would be added right after the SSDT install not SSDT-BI.  Both Startup has the BI templates.

It turns out.

1. You only get the “SQL Server Data Tools for visual Studio” when you install SSDT-BI.
2. You would get Visual Studio 2012 by either install SSDT or SSDT-BI. But the Database project would only be available after you install SSDT.
3. After you install both, both Visual Studio 2012 and SQL Server Data tools for Visual Studio would have all the projects.

 

I really think this cause quite of confusion, SQL server team should just remove the short cut of Visual Studio 2012 and Use “SQL Server Data Tools for Visual studio” for both product.

 

Reference

http://msdn.microsoft.com/en-us/data/hh297027

0 Disable the Enhance Security in IE

I am using Windows Server as my day to day operation system. One of the security feature on the server is IE would keep pop up the security warming.

It is true that we “normally” would not use the server OS for internet browsing  but in my case, this is what I want to be. The way work around it is not to use IE but even so, it is possible other application could trigger that ( such as Windows Live writer ).

0 Change the Windows Live Writer Default Post location

I am using the Windows Live writer to write my blog post. By Default, the post is store under C:\Users\%USERNAME%\Documents\My Weblog Posts

Because I like to use Sky drive to backup my Post, I like to change my default post location to the child directory under the sky drive.

The way to achieve this is to add the additional register key under HKEY_CURRENT_USER\Software\Microsoft\Windows Live\Writer

The Key string name is PostDirectory and the Value is where you want it goes.

Once you restart the live writer, it will create 2 child folders , Drafts and Recent Posts.

0 Import WIM to VMWare

Recently, I have the need to import the WIM image file to VMWare.

Here are the steps how I achieve this.

 

Convert WIM to VHD

This can be easily done by using Convert-WindowsImage.ps1.

The script can be down load from Microsoft ( http://gallery.technet.microsoft.com/scriptcenter/Convert-WindowsImageps1-0fe23a8f ) and only executable on Win8/Win2012 .

C:\Users\PO\Downloads\Convert-WindowsImage.ps1  -SourcePath D:\win7_64.wim -Edition 1

At this Point, you get the VHD file and you can open it in Virtual Box or Mount to the disk. But what I really want is to import into VMWare . Hence I need to do another conversion .

0 SQL Server Performance Dashboard

SQL Server performance dashboard are the step of reports that will give user the overview of the SQL Server performance .

You can download the installer from here.

0 SQL Server 2012 Sample database

This post is to illustrate how to install sample database for SQL Server 2012.

• Adventure Work 2012
• Adventure Work 2012 DW
• Pubs
• Northwind

AdventureWorks2012

Down load from here.

CREATE DATABASE MyAdventureWorks 

    ON (FILENAME = 'E:\DATA\MSSQL11.MSSQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf'), 

    (FILENAME = 'E:\DATA\MSSQL11.MSSQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf') 

    FOR ATTACH; 

 

adventure work DW 2012

Down load from here.

The file for this database is one single MDF file. so we need to use ATTACH_REBUILD_LOG;

CREATE DATABASE MyAdventureWorksDW

    ON (FILENAME = 'E:\DATA\MSSQL11.MSSQL2012\MSSQL\DATA\AdventureWorksDW2012_Data.mdf')

    FOR ATTACH_REBUILD_LOG; 

3 SQL Server 2012 Express includes full SSMS function

SQL Server management Studio is the primary tool that use against SQL Server.

It is not difficult to find the comparison between SQL Server Edition and the Free express version. However, it is not so much discussion for the management tools itself.

We know that the SSMS express version looks like a full version of the SSMS but in fact it actually not the full version and lack many functionality. In order to get the full version of the SSMS, we have to install it from the license media.

Good news is , it is no longer the case with SQL Server 2012 SP1.

With SQL Server 2012 SP1, the full feature of the SSMS is free to download.  Here. The binary name is SQLManagementStudio_x64_ENU.exe. The Size is over 900MB which is significant large than the RTM version here ( 600 MB).

7 Install SQL Server 2012 SP1 on Windows 2008R2

 

Environment

Windows Server 2008R2

 

Prerequisite

Enable dot Net 3.5 Features.  If we don’t enable Dot Net 3.5, we would get the below error during the installation of the SQL Server.

To enable dotnet 3.5, we can enable at ad ”Application Server” role.

Install dot Net framework 4. This is to work around the below error during the installation . The application can be download from here .

Microsoft .NET Framework 4.0 installation has failed with exit code 1.

Error writing to file: DTEParseMgd.dll.  Verify that you have access to that directory.