Database Product installation FAQ updated for SQL Server 2016 CTP 2.3 and Polybase

Posted: August 30, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2016 | Tags: , | Leave a comment

Database Product installation FAQ updated for SQL Server 2016 CTP 2.3 and Polybase

http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.9

Connect items

Posted: August 30, 2015 | Author: | Filed under: Microsoft Connect, Microsoft SQL Server 2016 | Tags: , | Leave a comment

Raised my first connect item – 1701396 for adding Application-period temporal tables and Bitemporal tables to SQL Server 2016! https://connect.microsoft.com/SQLServer/Feedback/Details/1701396

Just voted up the Connect item for fixing the disk label on SQL Server 2016 CTP releases – https://connect.microsoft.com/SQLServer/Feedback/Details/1640362

Advertisement

SSMS July 2015 and SQL Server 2016 CTP 2.2 re-release issue

Posted: August 3, 2015 | Author: | Filed under: Microsoft SQL Server 2016 | Tags: | Leave a comment

SQL Server 2016 CTP 2.2 (original release)

Microsoft SQL Server 2016 (CTP2.2) – 13.0.400.91 (X64)   Jul 16 2015 04:41:40   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows 10 Pro Insider Preview 6.3 <X64> (Build 10162: ) (Hypervisor)

SQL Server 2016 CTP 2.2 (2nd release)

Microsoft SQL Server 2016 (CTP2.2) – 13.0.407.1 (X64)   Jul 22 2015 21:19:11   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows 10 Pro Insider Preview 6.3 <X64> (Build 10162: ) (Hypervisor)

After installing the later the SSMS -> Tools -> Update recommends an upgrade to July 2015 preview

SSMS July 2015 preview fails to install as the database engine is too new!

0x81f40001 – A database engine with version 13.0.0.407.1 was found which is incompatible with this application being installed. Please uninstall the current database engine or upgrade it to version 13.0.400.91 and rerun this setup.

FYI SSMS July Preview used to be 33,093,472 bytes, now it is 33,109,984 bytes so it has changed as well.

SQL Server 2016 Query Store with Linked Servers

Posted: June 1, 2015 | Author: | Filed under: Microsoft SQL Server 2016 | Tags: | Leave a comment

On servers [WIN-TC4FTRLFICH\SQL2016X1] run

create database davwill;
use davwill;
create table dbo.justdave1(a int, b int);
insert into dbo.justdave1 values(1,1);
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

On linked server  [WIN-TC4FTRLFICH\SQL2016X2] run

create database davwill;
use davwill;
create table dbo.justdave1(a int, b int);
insert into dbo.justdave1 values(1,2);
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

Then on the first server run:

use davwill;

select top 1 a.b,b.b from davwill.dbo.justdave1 a,
[WIN-TC4FTRLFICH\SQL2016X2].davwill.dbo.justdave1 b
WHERE a.a=b.a

Then check the query store on the first server database davwill

select top 3 * from sys.query_store_query_text order by query_text_id desc

query_text_id    query_sql_text    statement_sql_handle    is_part_of_encrypted_module    has_restricted_text
3    select top 3 * from sys.query_store_query_text order by query_text_id desc    0x09006E854375827B18C9B88B2280364E194F0000000000000000000000000000000000000000000000000000    0    0
2    select top 1 a.b,b.b from davwill.dbo.justdave1 a,  [WIN-TC4FTRLFICH\SQL2016X2].davwill.dbo.justdave1 b  WHERE a.a=b.a    0x0900E5E3F6B306B5BF3E00E6F014C0384CED0000000000000000000000000000000000000000000000000000    0    0
1    SELECT * FROM sys.database_query_store_options    0x09003CD7308C321E569EFC0C639317339F8C0000000000000000000000000000000000000000000000000000    0    0

Check the query store on the 2nd server database davwill:

select top 3 * from sys.query_store_query_text order by query_text_id desc

query_text_id    query_sql_text    statement_sql_handle    is_part_of_encrypted_module    has_restricted_text
2    select top 3 * from sys.query_store_query_text order by query_text_id desc    0x09006E854375827B18C9B88B2280364E194F0000000000000000000000000000000000000000000000000000    0    0
1    SELECT * FROM sys.database_query_store_options    0x09003CD7308C321E569EFC0C639317339F8C0000000000000000000000000000000000000000000000000000    0    0

Nothing appears in the query store on the linked server!

SQL Server 2016 CTP2 – Create Linked Server

Posted: June 1, 2015 | Author: | Filed under: Microsoft SQL Server 2016 | Tags: | Leave a comment

SQL Server 2016 CTP2 – Create Linked Server

First expand linked servers -> Providers and check the properties for “SQLOLEDB”, enable propery “Allow inprocess” – https://support.microsoft.com/en-us/kb/2450479?wa=wsignin1.0

Create the linked server

– on the General Properties page Choose “Server Type” SQL Server, add the server as “machine\server”

– on the Securtiy Properties page under “For a login not defined in the list above,connections will be made:” Choose “Be made using the login’s current security context”

Test with

select top 1 a.name,b.name from master.dbo.spt_values a,
[WIN-TC4FTRLFICH\SQL2016X2].master.dbo.spt_values b
where a.number=b.number and b.number=1

What’s New in SQL Server 2016 BI

Posted: May 21, 2015 | Author: | Filed under: Microsoft SQL Server 2016 | Tags: | 1 Comment

http://prologika.com/CS/blogs/blog/archive/2015/05/11/what-s-new-in-sql-server-16-bi.aspx

What’s New in SQL Server 2016 BI

https://view.officeapps.live.com/op/view.aspx?src=http%3a%2f%2fvideo.ch9.ms%2fsessions%2fignite%2f2015%2fdecks%2fBRK2578_Wong.pptx

What new in Master Data Services and Integration Services in SQL Server 2016

https://channel9.msdn.com/Events/Ignite/2015?sort=sequential&direction=desc&term=&y=What%27s+New

Microsoft Ignite 2015 – What’s New items

2016 SQL Server Temporal Databases

Posted: May 16, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2016 | Tags: , | Leave a comment

Data growing, lot of modifications, not all are correct.

Time Travel – go back in time

Repair db corruptions!

Setup Audit on a Table or object and action (insert,update,deleted)

Alter table with period definition

GENERATED ALWAYS AS ROW START

PERIOD FOR SYSTEM TIME – just like DB2

Alter table with history table

Valid from and Valid To

2016 SQL Server In memory OLTP

Posted: May 16, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2016 | Tags: , | Leave a comment

DDR memory Q2 2014 – Faster,lower voltage,memory cheaper

Insert –

Take Page_EX latch

Query datapage header

add row

update page header

Release latch

80% of time in Storage Engine and Relational Engine

2014 Limitations

– Enterprise only

– 256GB per database

– Removal of in-memory FG not allowed

– No table/index changes allowed

– Row size limit is 8060

2016

– 2TB of durable tables per database

2014 Garbage Collection process is not efficient enough

SQL Server 2016

– Storage management is decoupled from FileStream

– Unused files can be reused immediately after they are derference from the transaction log

2016 now allows TDE with In-memory OLTP

SQL Server 2014 scales to 2 sockets/64 cores

More cores, more log records,Single “Offline Checkpoint” thread

SQL Server 2016

Multiple “Offline Checkpoint” threads – one per container

SQL Server 2016

– Alter is supported

– Alter proc and sp_recompile is support

– Alter Table – add/alter/drop column/constraint

– Alter index ??

Alter table is offline operation, 2x space usage

sp_rename still not supported

SQL Server 2014 join MOT to Colmnstore get Query DOP 1

SQL Server 2016

– Parallel plans are supported

– Join columnstore with MOT

– Realtime analytics

SQL Server 2016

– DML triggers

–

SQL Server 2016 Security

Posted: May 16, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2016 | Tags: , | Leave a comment

Dynamic Data Masking – Only Results Set is affected

Row Level Security, cannot be applied to a view.

Logic is held in the database, apps do not need to change

Predicate function on a table – User Defined Inline TVF 1 applied to 1 table only

Security Predicate

SecurityPolicy hides rows

Best Practice is to create a schema on top of the function

Creation function in a seperate schema

Create function fn_getsalaray (@salary) returns table with schemabinding

AS

Select 1 As fn_GetSalary_Result

where @salary < 100 and USER_NAME() = ‘dave’

OR @salary > 100 and USER_NAME() = ‘dave2’

Create Security Policy Salary Fiter

ADD FILTER PREDICATE Security.fn_getsalary(salary)

ON dbo.Salary

WTH (STATE = ON)

EXECUTE (‘Select * from Salary’) AS USER ‘User1’

Create 1 Security Policy on 1 Table

DMVs

sys.security_policies

sys.security_predicates

Row Level Securiy incompatible with database export using Data Tier Application Framework

Security Policies cannot target views

DBCC Shows_Statistics reports statistics on unfiltered data

Always Encrypted

Client Side Keys – keys not sent to data server – along handles man in the middle

Microsoft say no overhead for Always Encrypted!

Client driver encyrpts – Random

Only encrypted data on the server

New TDE in Trial Mode in Azure portal.azure.com – New Portal

TDE (Preview)

Create database encyption key with Algorithm AES_256

Alter database MYDB set encryption on

SQL Server 2016 Power Bi vNext

Posted: May 16, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2016 | Tags: , | Leave a comment

SQL Server 2016 Power Bi vNext

Power Bi Preview https://powerbi.microsoft.com/

HTML 5, not Silverlight

Dashboard

Mobile

Real Time

Hybrid

2 level of dashboards/reports, native mobile apps iOS,Windows,Andriod (soon)

First App was for iOS!

Power BI Rest API

If do not have provider for your datasource, load the data yourself and use the API to send it to PowerBi with realtime refresh

Hybid – Live Query to On-Premise, Power BI render Dashboard, requires simple gateway, connects directly to SQL Agent

Links to SalesForce

On the website Free Tier you can use  https://powerbi.microsoft.com/ – limits on size, refreshing to on-presime data source

Links to organization for password – no password sent to Microsoft

Report is multi-page clickable,used to be dashboard

New Dashboard can show tiles accessing from different data sources

Dashboard is flat – tile experience, can resize and move tiles, click tile to get into report

You can now edit reports online! Drap and drop to build charts

New Char Types, funnels, tree maps!

To put tile onto Dashboard go to report and click ‘pin’ – pin report to the dashboard

Default for most Dashboards – use Q&A to built an item “sales in 2014 by map”

sum quantity by country as funnel

Can pull data from Google Analytics

Excel Workbook – can upload to power bi as dataset, immediately get dashboard, power bi detects country, dates in workbook

You can share dashboard – sends link

Looks like June Power BI  Designer leaked!

In future users can get your content pack with your logo and pull your data to use!

The iOS,iPad and iPhone apps are different apps and so render well on each device

Power Query will be relesed monthly!

Data Sources for Power BI Designer include Sybase and Postgres!

In PowerQuery there is no PowerPivot but it is there, just done differently

HTML component runs in Power Query, same as on the portal!

When add measure can edit the field and use IntelliSense when editing the formulae

Can also format fields e.g. as currency in euros

Can upload data and report, cannot update though so still use Excel.

Main focus is Power BI Designer rather than Excel now

Apps for Office – Send to Power BI – update excel and dashboard refreshes

Poweer BI Developer Rest API

https:/apis.powerbi.com/beta/myorg/DataSets

PUT Table – Update Schema released yesterday!

http://dev.powerbi.com

PowerShell module PowerBIPS – can push data into PowerBi have to consert the app can send the data!

Can write PowerShell to pickup CSV Files that appear in a directory, infer the schema and upload them to PowerBI

RealTime Power BI Voting App – http://bit.do/PBIEval

Free Version 10,000 updates per hour, 1GB total.

Paid Version 1,000,00 updates per hour – 10GB data

← Older Entries
Newer Entries →