Cardiff User Group Talk June 24 2014 Speaking Questions

Answer to Questions for Cardiff User Group Talk Jun 24 2014.

1. Backup Encryption in 2014 – Is this an Enterprise only feature?

SQL Server Express and SQL Server Web do not support encryption during backup. However restoring from an encrypted backup to an instance of SQL Server Express or SQL Server Web is supported.

https://msdn.microsoft.com/en-us/library/cc645993.aspx#High_availability

2. SSD Buffer Pool Extension – 32x vs 4x how much addition memory is used inside SQL Server?

There a 2 structures per page in the bufferpool, 1 ‘BUF’ header (~100 bytes,corresponds to sys.dm_os_buffer_descriptors which is a list of BUF structures for all hashed buffers). and the actual page itself.

For pages in the bufferpool extension file they only need a ‘BUF’ structure in memory which contains file_id,page_id and is_in_bpool_extension =1. This points to the actual page in the BPE file!

Therefore only additonal small ‘BUF’ header structures are needed for a large bufferpool file.

NOTE: Bufferpool memory can also be seen via sys.dm_os_memory_clerks type=’MEMORYCLERK_SQLBUFFERPOOL’.

select type,sum(pages_kb) pages_kb

from sys.dm_os_memory_clerks

group by type

having type in (‘MEMORYCLERK_BHF’,‘MEMORYCLERK_SQLBUFFERPOOL’)

order by type

3. SSD or fast disk?

SSD may well be quicker even than fast disk as it can be larger then the cache on a disk array.

Fast Disks will help with writes which SSDs do not.


Database product installation FAQ

Database product installation FAQ http://www.smooth1.co.uk/installs.html

Currently SQL Server,DB2 10.5,Oracle 12.1.0.2,SAP ASE 16.0,Informix 12.10.FC4


Currently reading…

 

DBA Survivor – Become a Rockstar DBA .

http://msdn.microsoft.com/en-us/library/ms191246(v=sql.120).aspx

All DMVs are under sys schema.

http://msdn.microsoft.com/en-us/library/ms188754.aspx

http://msdn.microsoft.com/en-us/library/ms179984.aspx

Wait stats for memory

– CMEMTHREAD

– LATCH_*

– LCM_M_*

– LOWFAIL_MEMMGR_QUEUE

– RESOURCE_SEMAPHORE_*

– UTIL_PAGE_ALLOC

Wait stats for CPU

– CXPACKET

– EXCHANGE

– EXECSYNC

SQL Profile

http://msdn.microsoft.com/en-us/library/ms175481.aspx

http://msdn.microsoft.com/en-us/library/ms190762.aspx

http://msdn.microsoft.com/en-us/library/ms190793.aspx

 


SQL Server 2012 Tabular Data Model

Documented at http://msdn.microsoft.com/en-us/library/hh212945.aspx

Securing the Tabular BI Semantic Model whitepaper http://msdn.microsoft.com/en-us/library/jj127437.aspx

Securing your tabular model http://blogs.msdn.com/b/cathyk/archive/2011/11/21/securing-your-tabular-model.aspx

Getting started with Tabular Model in SQL Server 2012 http://www.mssqltips.com/sqlservertip/2821/getting-started-with-tabular-model-in-sql-server-2012–part-1/

Roles (SSAS Tabular) http://msdn.microsoft.com/en-us/library/hh213165.aspx – includes row filters and dynamic security.

Perspectives – like views http://msdn.microsoft.com/en-us/library/hh230816%28v=sql.110%29.aspx

BISM – Tabular Security Model http://paultebraak.wordpress.com/2011/09/04/bism-%E2%80%93-tabular-security-model – more on row filters.

Implement Dynamic Security by Using Row Filters http://msdn.microsoft.com/en-us/library/hh479759%28v=SQL.110%29.aspx

(DMV Question)
https://www.linkedin.com/groups/Is-there-DMV-find-all-66097.S.5895157457688211456?view=&item=5895157457688211456&type=member&gid=66097&trk=eml-b2_anet_digest-group_discussions-8-grouppost-disc-0&midToken=AQH48zvduqwTWw&fromEmail=fromEmail&ut=0zOeU5w-MNxCk1


SQL Server 2014 Buffer Pool Extension onto SSD.

SQL Server 2014 Buffer Pool Extension onto SSD – http://www.smooth1.co.uk/sqlserver2014/bpe_ssd.html


SQL Supper 22nd April 2014 Questions.

1. To install SQL Server 2014 documentation locally?

SSMS -> Help -> Manage Help Settings -> Help Library Manager then choose  Install Content from online

Under SQL Server 2014 Add  Books Online, Developer Reference and Installation.

You can also check for updates online as well.

2. What is new in SQL Server 2014 Integration Services?

As per books online nothing! However Professional Microsoft SQL Server 2014 Integration Services

http://www.amazon.co.uk/Professional-Microsoft-Server-Integration-Services-ebook/dp/B00JSQ3RLG/ref=sr_1_1?s=books&ie=UTF8&qid=1399224239&sr=1-1&keywords=Professional+Microsoft+SQL+Server+2014+Integration+Services

does mention 2014 enhancements although it says they are from codeplex and not integrated into the base product yet.

3. What is new in Report Services?

Books Online only mentioned addition of support for Chrome as a browser!

4. What T-SQL enhancements are there in 2014?

This is covered at http://www.smooth1.co.uk/sqlserver2014/t_sql_enhancements.html

5. Resource Governor for physical IO, the IO settings do not appear in SSMS.

If you run ‘script resource pool as’ the settings are visible:

USE [master]
GO

/****** Object:  ResourcePool [PoolAdmin]    Script Date: 05/05/2014 10:17:42 ******/
CREATE RESOURCE POOL [PoolAdmin] WITH(min_cpu_percent=0,
max_cpu_percent=100,
min_memory_percent=0,
max_memory_percent=100,
cap_cpu_percent=100,
AFFINITY SCHEDULER = AUTO
,
min_iops_per_volume=20,
max_iops_per_volume=100)

GO

I have mail Mark Souza (General Manager -Data Platform Group) for an update when these will appear in the properties page.


London SQL Supper 22nd April 2014 slides.

The slides for my talk at London SQL Supper 22nd April 2014 are at http://www.smooth1.co.uk/sqlserver2014/2014NewFeatures.zip


Data Compression & TDE

 

http://social.msdn.microsoft.com/forums/sqlserver/en-US/0650314c-6fda-4858-b667-90b0a06e1865/data-compression-tde


SQL Server 2014 installation on Windows 2012 R2 on VMWare 10.

 Windows 2012 R2.evaluation in VMWare 10.

– disable floppy drive to avoid License Terms error

SQL Server 2014 installation on Windows 2012 R2.evaluation

– Server Manager -> Local Server (on left) -> Manage (top right) -> Add Roles and Features -> Features screen -> .NET Framework 3.5 features.-> Specify alternate source path -> D:\Sources\Sxs.

or

dism /online /enable-feature /featurename:NetFX3 /all /Source:d:\sources\sxs /LimitAccess

If you get that “vmware tools installation cannot be started manually while easy install is in progress” then change floppy drive back to a physical drive – http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1017687


SQL Saturday Exeter 2014 (22 March 2014) Speaker Answers

Questions from my presentation at SQL Saturday Exeter 2014:

1. Backup to Azure – Backup to URL https or http?

As per http://msdn.microsoft.com/en-us/library/dn435916%28v=sql.120%29.aspx

“Here is a sample URL value: http[s]://ACCOUNTNAME.Blob.core.windows.net/<CONTAINER>/<FILENAME.bak>. HTTPS is not required, but is recommended.

2. What happens if you turn on 2014 trace flags 2312 (force new cardinality estimator) and 9481 (force old cardinality estimator) at the same time do you get an error?

No, niether is used and the database compatability level determines the cardinality estimator that is used. http://blogs.msdn.com/b/psssql/archive/2014/04/01/sql-server-2014-s-new-cardinality-estimator-part-1.aspx

Is the showplan really changes for columnstore indexes in 2014? ” The EstimatedExecutionMode and ActualExecutionMode properties have two possible values: Batch or Row. The Storage property has two possible values: RowStore and ColumnStore.” Surely that was in 2012?

Books online still says that both EstimatedExecutionMode/ActualExecution Mode and Storage properties are new in 2014 http://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#CCI. Actually only the Storage Propery is new http://www.smooth1.co.uk/sqlserver2014/csi.html