SharePoint & SQL Thoughts

Apr 09, 2014 |

As you may know by now if you’ve been following SQL 2012 HA capabilities, AlwaysOn availability groups allows readable secondary replicas to allow offloading of backups, database consistency checks, index fragmentation checks, reporting and so on. One of the question I see being asked a lot is how do I tell my connection that I want you to go to read-only replica, do I have to always go and change the connection to point to the actual server name? The answers lie here.

AlwaysOn availability groups comes with a feature referred to as Application Intent filtering which is a process of telling you application that I want you to only perform read-only operations. When the intent is defined as read-only, Availability Groups Listener will ensure that the application connects to the first available read-only secondary replica through a mechanism referred to as Application Intent Read-Only Routing.

For the routing to take place some conditions have to in-place

1.       The intent of the application has to be defined as read-only (very obvious), if not defined the connection will be assumed to be read/write intent.

2.       The primary replica has to be on-line.

3.       There has to be readable secondaries.

4.       Read-Intent only has to be configured in the readable secondary dropdown list on all the replicas

5.       Configure the AG to be aware of read-intent only connections

TSQL Sample

ALTER AVAILABILITY GROUP [AG1]

 MODIFY REPLICA ON

N'labmac1' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [AG1]

 MODIFY REPLICA ON

N'labmac1' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://labmac1.mynetwork.local:1433'));

 

ALTER AVAILABILITY GROUP [AG1]

 MODIFY REPLICA ON

N'labmac2' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [AG1]

 MODIFY REPLICA ON

N'labmac2' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://labmac2.mynetwork.local:1433'));

 

PowerShell Sample

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg

$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"

$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://labmac1.mynetwork.local:1433" -InputObject $primaryReplica

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://labmac2.mynetwork.local:1433" -InputObject $secondaryReplica

 

6.       The database has to be part of the AG group.

7.       Configure your application to use TCP protocol defined in conditions above the connection string should appear as follows as you can see

Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

a.       The Server is actually my TCP protocol

b.      Application intent is defined

c.       A database that is part of AG

Here is how it all goes down.

An application with read-only intent filtering makes a connection to the Listener (yes the application will be connecting to the primary replica listener)

The listener determines that the application has read-only intent and performs the routing redirection to the available readable secondary.

And wallaah you are connected to readable replica. In case of failover the connections will be reset and you will still be on a readable unless the above conditions are not met which would result to connection refused.

In conclusion AlwaysOn has some very good features that are convincing enough to trigger the upgrade of your SQL Server environment to SQL 2012.

Jul 17, 2013 |

Its official, my Microsoft Virtual Technology Specialist (VTSP) has been approved, another mile stone to helping clients and partners find value in Microsoft Products in particular SQL Server, Business Intelligence and SharePoint.

As per Microsoft VTS Program is defined as:

The Microsoft Virtual Technology Specialist Program (VTSP) is a select group chosen from the elite in Microsoft’s partner community, whose focus is to augment Microsoft’s internal Technology Specialist team. Their primary role is to communicate the value of Microsoft Solutions to customers and to provide architectural guidance for Enterprise Integration solutions. The Microsoft VTSP program was designed to create a deeper relationship with Microsoft Partners, the Product Teams at Microsoft Corporate, and Regional Microsoft Offices, in order to provide highly skilled solution specialists to Microsoft customers. It is designed to enable a high performance team of partner-based resources to deliver pre-sale activities and resources to empower customers and help them meet their solution and integration needs.

This is exciting because I get to access Microsoft’s Corp network knowledge bases, technical and training materials and other resources. I believe this will add value to our clients and the community in general and I will put it in good use.

Thank you Microsoft and AOS.  

May 06, 2013 |

When creating your partition schemes you get the following error

The associated partition function MyPartitionScheme generates more partitions than there are file groups mentioned in the scheme ' MyPartitionScheme'.

If you look at the error closely, its yelling that SQL Server has more than one File Group and in your savvy Scheme creation you decided to shove everything in one filegroup hence the yelling.

 You can resolve it by either defining your file groups in the scheme see snippet below or you if you are still convinced that you only need one file group then define your syntax with ALL see snippet below.

 Multi file groups – ofcourse they have to exist.

CREATE PARTITION SCHEME [myPartitionScheme]
AS PARTITION myDataPartition
TO (test1fg, test2fg, test3fg, test4fg);

Syntax with ALL

CREATE PARTITION SCHEME [myPartitionScheme] 

AS PARTITION myDataPartition

ALL  TO (Archived)
GO

May 01, 2013 |

So you have two tables that you want to compare to see what’s common between them and what’s not. Traditionally I have used JOINS to get what I need but did you know you can compare the two tables results simply by using the EXCEPT and INTERSECT syntaxes?

Let’s look at these two beauties.

EXCEPT – returns any distinct values from the left (sounds familiar LEFT JOIN) that are not found on the right query.

Assuming that everyone has a master database that they can query, run the following queries to see what this beauty is doing. You’ll see that its returning the result set from my left query (unfiltered query) that’s filtered from the right query.

USE MASTER

GO

SELECT *

FROM sys.objects

WHERE type = 'U'

EXCEPT

SELECT *

FROM sys.objects

WHERE type = 'U' AND name LIKE '%spt_%'

How about INTERSECT – This beauty return distinct results that match between my  left and the right query example. Going back to the master database query the result set only contains data that matches on both queries.

USE MASTER

GO

SELECT *

FROM sys.objects

WHERE type = 'U'

INTERSECT

SELECT *

FROM sys.objects

WHERE type = 'U' AND name LIKE '%spt_%'

So how is this useful? Think of those dupes, or when you just want to catch the culprit they make things very easy.

Happy tscripting.

Apr 19, 2013 |

Some literature first

If you are not, you should start considering database encryption for your organization as data is the only reason you are in business. If and when it becomes compromised bad things are bound to happen.

When TDE is enabled or disabled for that matter a background process is initiated by the server that encrypts all the database files.

This is an Enterprise or Developer version(s) only feature.

How it works

DDL is initiated (SELECT something FROM SOMEWHERE WHERE otherthing = 'athing') and update lock is taken on the database causes an encryption scan, by the way this is asynchronous to DDL any operation that do not conflict with the update and shared lock proceeds without interruption excluding the database detach and file structure modification.

After the scan is complete DEK is set to encrypted and all the files on disk, the database and the log files writes to the disk will be encrypted.

What's encrypted?

Files on disk will be encrypted the *.mdf, *.ndf, snapshots, backups, "*.ldf” and TempDB is by default encrypted when encryption is enabled on user databases.

Transaction logs by design are write-once fail safe hence a challenge for TDE thus not everything is encrypted on them.

What's not encrypted?

Data that is in memory (data in use) is not encrypted TDE does not offer in memory encryption (Consider Cell-Level encryption if this is a requirement).

Data in transit is not encrypted either (consider using SSL if this is a requirement)

Database page files (written to disk), the headers are also not encrypted because they may be used for reload.

System databases are currently not encrypted by TDE.

Impact on databases

TDE as the name suggests tries to make the encryption as transparent as possible, no application changes and user experience is meant to remain the same as non-encrypted database.

Being a database level process is what makes it possible to perform as a non-encrypted database because the database can still leverage the usual database optimizations.

Encryption is CPU intensive hence generating high I/O. Consider your I/O impact prior to enabling encryption. It’s usually estimated to be about 30% increase in CPU usage.

During the initial encryption scan process, performance is sharply impacted thus consider enabling the process during light usage period in your environment.

There is no padding on database files on disk though transaction logs are padded.

User permissions are not impacted.

Since it’s an Enterprise/Developer versions only feature, encrypted databases cannot be used in other versions.

Impact on Backups

Don’t loose the key, I repeat don’t loose the key else you are doomed... enuf sed.

See Create a backup of the DEK on making it work

Making it work

--- Check if encryption exists

SELECT name, is_encrypted FROM SYS.DATABASES

--- Check if certificate exists

SELECT name, is_encrypted FROM MASTER.SYS.CERTIFICATES WHERE pvt_key_encryption_type = 'MK' AND NAME LIKE '%TDE'

--- Create an encryption key DMK database master key

--- Ensure that the DMK is encrypted by SMK

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password'

--- Create a certificate to be used by DEK database encryption key 

CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate';

--- Create a backup of the DEK this is the one that goes under the bed you lose it you lose your data

--- I've always preferred UNC path's ensure that you have appropriate permissions defined.

BACKUP CERTIFICATE tdeCert TO FILE = '\\myuncpath\filename.certbak'

   WITH PRIVATE KEY (

         FILE = '\\myuncpath\filename.pkbak',

         ENCRYPTION BY PASSWORD = 'strong cert password');

--- If data is transmitted beyond your server, consider SSL'ing your box.

--- Create your DEK, ensure that you pick the appropriate algorithm at the time of publishing this article AES-256 (3 Key Triple DES) was the highest encryption per NIST and NSA.

--- This step here is necessary to set the USE database else you will get the error "Cannot change database encryption state because no database encryption key is set."

USE <MyDatabase>

GO

CREATE DATABASE ENCRYPTION KEY

   WITH ALGORITHM = AES_256

   ENCRYPTION BY SERVER CERTIFICATE tdeCert

--- Enable your TDE on your database

ALTER DATABASE myDatabase SET ENCRYPTION ON

--- Monitor progress 

SELECT db_name(database_id), encryption_state

FROM sys.dm_database_encryption_keys

/******** if you already have an existing key that needs to be transferred to a different server ******************/

-- This is necessary if you will be restoring your database in a different SQL Server.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password'

--- Create a certificate generated from the path where you stored the backup certificate

CREATE CERTIFICATE tdeCert FROM FILE = 'path_to_file'     

WITH PRIVATE KEY (

               FILE = 'path_to_private_key_file',

         DECRYPTION BY PASSWORD = 'strong cert password')

Apr 16, 2013 |

After configuring user profile you get the correlation id and in event logs you get

The Forefront Identity Manager Service cannot connect to the SQL Database Server.

 

The SQL Server could not be contacted. The connection failure may be due to a network failure, firewall configuration error, or other connection issue. Additionally, the SQL Server connection information could be configured incorrectly.

 

Verify that the SQL Server is reachable from the Forefront Identity Manager Service computer. Ensure that SQL Server is running, that the network connection is active, and that the firewall is configured properly. Last, verify the connection information has been configured properly. This configuration is stored in the Windows Registry.

 

Open central administration à security à Configure Service Account (On the drop down pick Farm Account) and ensure that the select and account for the component is the same as the one you are using to configure FIM (User profiles) the OK.

That should fix that issue.

 

Happy FIMing

May 14, 2012 |

Here is the list of all the changes in SQL Server engine that has been removed.

http://technet.microsoft.com/en-us/library/ms143729.aspxv

Apr 26, 2012 |

This morning I decided to be a little adventurous and install SharePoint 2010 on SQL 2012 environment and the first thing I noticed is that the installation fails because in SQL 2012 Microsoft has removed [sys].[sp_dboption] system Stored Procedure. This has already been reported as a bug but the response from the MS team was that this is by design and they have deprecated it. No workaround has been provided as of yet but they are working to certify Install SharePoint Service Pack 1.  http://www.microsoft.com/en-us/download/details.aspx?id=26640v

I decided to take the [sp_dboption] from SQL 2008 R2 environment and I also noticed that the built-in functions like DATABASEPROPERTY are also gone. I made a few changes to the SPROC see code from the link below and was able to successfully install SharePoint 2010

https://skydrive.live.com/redir.aspx?cid=3a35c02c8765e840&resid=3A35C02C8765E840!261&parid=3A35C02C8765E840!152&authkey=!AKwXZrZvEi_O7Isv

** Disclaimer this has not been tested in a production environment.

Apr 26, 2012 |

One of the things that I’ve heard people complain over the years after the release of SharePoint 2010 is the disappearance of Drag & Drop web parts capability. This feature was useful because it allowed the page designer to add as many web parts on a page without having to click add and repeating the process all over again.

This feature is available on SharePoint 2010 with a little manipulation of the site URL of the page that you want to modify by adding the “?toolpaneview=2”

If your URL is http://sharepoint_portal/pages/default.aspx, replace it with http://sharepoint_portal/pages/?toolpaneview=2

By making this small change, the “Add Web Parts” zone is displayed on the right hand side of the page similar to what was available in SharePoint 2007 and WSS 3.0 and from here on you can happily drag and drop. v

Mar 08, 2012 |