SQL Server – Changing Recovery Model from Full to Bulk Logged whilst a Tranasction is active.

Posted: May 10, 2017 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2016, Uncategorized | Tags: , , | Leave a comment

If have a SQL Server database and change the Recovery Model from Full to Bulk Logged whilst a transaction is open what happens?

If the ongoing transaction which was started under Recovery Model Full does an operation which can be minimally logged what happens?

Does the operation become minimally logged which then means the VLF is tagged as a minimally logged logfile which then does not allow certain operations e.g. restores from the next log backup with STOPAT.

We test with STOPAT and also use fn_dump_dblog to see exactly what ends up in the log backup and how we can identify minimally logged operations in a log backup file.

http://smooth1.co.uk/sqlserver2016/RM_BL_ML.html

Advertisements

SQL Server 2016 – Database Scoped Configuration Parameters and Always On Availability Group failovers.

Posted: May 10, 2017 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2016, Uncategorized | Tags: , , | Leave a comment

In SQL Server 2016 we have database scoped parameters.

With an AlwaysOn Availability Groups we can have different database scoped parameter values on the primary compare to the secondaries.

How does this work with an Always On Availability Group failover ?

http://smooth1.co.uk/sqlserver2016/AA_AG_DSP.html

SQL Server 2016 RTM available, critical dependency!

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

SQL Server 2016 now available!

Visual Studio Benefit – Developer Edition

https://myprodscussu1.app.vssubscriptions.visualstudio.com/Benefits

OR

Technet Evaluation Centre

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016?i=1

Important Note:

SQL Server 2016 has a critical pre-requisite for updated Visual Studio VC++ 2013 Runtime Libraries.These do NOT come via Windows Update, they are a seperate download.

https://support.microsoft.com/en-us/kb/3138367

 

System.Transactions (and AlwaysOn Availability Groups)

Posted: April 24, 2016 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2016, Uncategorized, Windows Server 2016 | Tags: , , | Leave a comment

With SQL Server 2016 AlwaysOn Availability Groups (on Windows Server 2016 TP4) supports MSDTC which can be used for transactions which access more than 1 database.

However there are Transactions which do not use MSDTC.

Within the .NET Framework there is System.Transactions namespace https://msdn.microsoft.com/en-us/library/system.transactions%28v=vs.110%29.aspx

As per https://msdn.microsoft.com/en-us/library/ms229978%28v=vs.110%29.aspx these implement a Transaction Manager which can handle transactions within SQL Server.

System.Transactions only SOMETIMES escalates to MSDTC!

“As long as the System.Transactions infrastructure handles..at most one durable resource that supports single-phase notifications, the transaction remains in the ownership of the System.Transactions infrastructure”

The question then is with SQL Server 2016 AlwaysOn Availability Groups (on Windows Server 2016 TP4) and a transaction which uses more than 1 database

a) Does SQL Server 2016 AlwaysOn Availability Groups support single-phase notifications?

b) With SQL Server 2016 AlwaysOn Availability Groups does more  than 1 single database count as 1 durable resource or more than 1 durable resource?

As per http://msdn.microsoft.com/en-us/library/ms229979.aspx tracing can be used to determine who promotes a transaction and why.

SQL Server Clustering (FCI) setup using Cluster Shared Volumes with multipathed iSCSI disks

Posted: January 5, 2016 | Author: | Filed under: SQL Server FCI, SQL Server Management Studio, Uncategorized, Windows ACtive Directory, Windows Clustering, Windows Domain Controller Setup | Tags: , , , , | Leave a comment

Just finished an article on SQL Server Clustering (FCI) setup using

  • Windows Server 2016 Preview 4 cluster nodes
  • SQL Server 2016 CTP 3.2
  • Windows Server 2016 Preview 4 iSCSI storage using iSCSI targets as disks
  • Cluster Shared Volumes on top of the iSCSI disks
  • Multi-subnet setup
  • Domain Controller Setup
  • SSMS December 2015 release to test connections

http://smooth1.co.uk/installs/dbinstalls.html#3.1.14

SQL Server Clustering (FCI) setup using multipathed iSCSI disks

Posted: January 5, 2016 | Author: | Filed under: Microsoft SQL Server 2016, Microsoft SQL Server future, Microsoft SQL Server Management Studio, SQL Server FCI, Uncategorized, Windows ACtive Directory, Windows Clustering, Windows Domain Controller Setup | Tags: , , , , | Leave a comment

Just finished an article on SQL Server Clustering (FCI) setup using

  • Windows Server 2016 Preview 4 cluster nodes
  • SQL Server 2016 CTP 3.2
  • Windows Server 2016 Preview 4 iSCSI storage using iSCSI targets as disks
  • Multi-subnet setup
  • Domain Controller Setup
  • SSMS December 2015 release to test connections

http://smooth1.co.uk/installs/dbinstalls.html#3.1.13

NOTE: Only DB Engine and Analysis Services are supported as clustered.

SQL Server 2016 CTP 3.1

Posted: December 5, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2016, Uncategorized | Tags: , | Leave a comment

Plenty of new feaures in SQL Server 2016 CTP 3.1

DB Engine

Memory optimized table gain support for LOBs,unique indexs and indexes on nullable columns

CREATE TABLE dbo.A
(
A_No int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
A_Id INT NOT NULL,
A_Units INT NULL,
A_value INT,
A_Description NVARCHAR(MAX), — LOB Support
INDEX i1 NONCLUSTERED (A_Units), — Index on Nullable Column
INDEX i2 UNIQUE NONCLUSTERED (A_Value) — Unique Index
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

Analysis Services Tabular

  • Tabular models can be upgraded to SQL Server 2016 (Compatability Level 1200)
  • In October a new modelling language was released for Tabular Models using JSON as part of SSDT fo Visual Studio 2015
  • The JSON document can now be editing using the Visual Studio JSON Editor that includes syntax highlighting and validation. The free Visual Studio Community Edition 2015 or higher is needed
  • Roles can be created for Tabular Models at Compatability Level 1200

BI

  • SQL Server PowerPivot and Reporting Services/Power View for SharePoint 2016, requires SharePoint Server 2016 Beta 2
  • Office Online Server Preview also needed, SharePoint Server no longer includes Excel Services
  • Excel Services, now called Excel Online Server, is now only available with Office Online Server.
← Older Entries