Reviewed by: Alejandro Saenz, Jos de Bruijn; Joe Sack, Mike Weiner,Kun Cheng, Raghavendra TK

A durable memory-optimized table (created as SCHEMA_AND_DATA) will have it’s transactions recorded into the transaction log so that on failure, recovery is possible. This logging ensures the durability of the transactions. Unlike disk-based tables, memory-optimized tables do not use WAL ( write-ahead logging) protocol, transactions are written to the log only on a commit and dirty data is never written to disk.

The checkpoint process is a background process used by SQL Server to ensure that in the event of a crash, the time it takes to recover isn’t very long.

Checkpoints for disk-based tables

A checkpoint for disk-based tables will result in flushing dirty pages to disk and in the simple recovery model the log will also be truncated. For disk-based tables, the database engine supports automatic, indirect and manual checkpoints. Automatic checkpoints for disk-based tables are based on the recovery interval configuration option. In addition, with SQL Server 2016, the default on newly created databases is to have “indirect” checkpoint enabled which shortens that recovery time period.

Checkpoints for memory optimized tables

For memory-optimized tables, the checkpoint process flushes both the data streams that contain all new versions of records and the delta stream that contains deleted versions of records to data and delta files. Each pair of data/delta files constitutes a checkpoint file pair or CFP and these files have the extensions .hkckp. All the data/delta files are written to sequentially or as streaming IO rather than random IO. To prevent the files from growing indefinitely , the checkpoint process will also do a “merge” if it is possible to merge subsequent checkpoint file pairs that have been closed.

Checkpoint behavior differences with memory-optimized tables

Checkpoint for memory optimized tables is done by a background worker thread in the In-Memory OLTP engine which is separate than the checkpoint thread for disk-based tables. Automatic checkpoint for memory-optimized tables will execute based on the following condition:

• If the log has produced 1.5 GB since the last checkpoint, this will trigger the Engine to checkpoint memory optimized tables.

This difference in behavior in checkpoint can cause confusion with regards when the log is purged when you have memory optimized tables. For disk based tables, the log is truncated on a log backup for full recovery model or an automatic checkpoint for a database in simple recovery mode. This changes when we have a memory optimized table, a transaction log backup doesn’t necessarily truncate the log until the thresholds specified above are met.

Of course, at any time though, a manual checkpoint command can be issued.

Also, with SQL Server 2016 there is a special case, based on the conditions below, which will vary the checkpoint characteristics for memory-optimized tables. This is referred to as the “Large checkpoint” process, which is enabled on larger machines if all of the following conditions are true

• The server has 16 or more logical processors
• The server has 128GB or greater memory.
• The server is capable of greater than 200MB/sec I/O measured for the IO subsystem of that database.

Details on this behavior are provided below in the section titled Large checkpoint for memory-optimized tables

Memory-optimized table checkpoint behavior example

Here is an example that shows what happens with checkpoints on memory-optimized tables. In summary we will:

• Create a database
• Establish the log chain by taking a backup
• Populate some data and commit, thereby growing the log
• Take a log backup

The expectation is that after the log backup, the log space used will be minimal unless there is some open transaction/ un-replicated transaction, or some other factors that prevents the log from being purged.

Example:

CREATE DATABASE [InMemoryOLTP]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'IMOLTP', FILENAME = N'c:\temp\InMemoryOLTP.mdf'), 
 FILEGROUP [IMOLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'IMOLTP_InMemory', FILENAME = N'c:\temp\InMemoryOLTP')
 LOG ON 
( NAME = N'IMOLTP_log', FILENAME = N'c:\temp\InMemoryOLTP_log.ldf')
 GO

USE InMemoryOLTP
GO
CREATE TABLE dbo.SalesOrder_MemOpt
(
 order_id int identity not null,
 order_date datetime not null,
 order_status tinyint not null,
 amount float not null,
 Constraint PK_SalesOrderID PRIMARY KEY NONCLUSTERED HASH (order_id) WITH (BUCKET_COUNT = 10000)
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
-- Create Natively compiled procedure to speed up inserts.
CREATE PROCEDURE [dbo].[InsertSalesOrder_Native_Batch] 
@order_status tinyint = 1,
@amount float = 100,
@OrderNum int = 100
  WITH 
    NATIVE_COMPILATION, 
    SCHEMABINDING, 
    EXECUTE AS OWNER
AS 
BEGIN ATOMIC 
  WITH (TRANSACTION  ISOLATION LEVEL = SNAPSHOT,LANGUAGE = 'us_english')
	declare @counter int = 1
	WHILE @counter <= @OrderNum
	BEGIN
	   INSERT INTO dbo.SalesOrder_MemOpt values(getdate(),@order_status,@amount)
	   SET @counter= @counter+1
	END
END
GO

-- Create backup to start chain, don't need the backups
 CHECKPOINT
 go
 BACKUP DATABASE InMemoryOLTP to disk = 'c:\temp\test.bak' WITH INIT,COMPRESSION
 BACKUP LOG InMemoryOLTP to disk = 'c:\temp\test.bak' WITH COMPRESSION
 GO
-- Check Log Space used column for DB
 DBCC SQLPERF(logspace)
 GO

-- Check Log Space, file checkpoint stats, file states
	-- Do we have any reason why the log can't be purged?
	SELECT log_reuse_wait_desc,* 
	FROM sys.databases
	WHERE name = 'InMemoryOLTP'
	GO
	-- What percentage of the log is used for this DB?
	DBCC sqlperf(logspace)
	GO
	-- How much log is generated since last checkpoint?
	SELECT log_bytes_since_last_close /(1024*1024) as Log_bytes_since_last_close_mb,time_since_last_close_in_ms, current_checkpoint_id,* 
	FROM sys.dm_db_xtp_checkpoint_stats
	GO
	-- Have checkpoint files been flushed?
	SELECT  state_desc, 
       file_type_desc, 
       count(state_desc) count, 
       sum(file_size_in_bytes)/(1024*1024) file_size_in_mb_bytes,
	   Avg(file_size_in_bytes)/(1024*1024) Avgfile_size_in_mb_bytes
	FROM sys.dm_db_xtp_checkpoint_files
	GROUP BY state_desc, file_type_desc
	ORDER BY file_size_in_mb_bytes desc

-- This proc inserts 900K rows 20 times
-- Attempt to grow the log to ~ 1GB
BEGIN TRAN
EXEC dbo.[InsertSalesOrder_Native_Batch]  1,100,900000
DELETE FROM dbo.SalesOrder_MemOpt WITH(SNAPSHOT)
COMMIT
GO 20
-- Backup the log
-- Do you expect the log to be purged?
BACKUP LOG InMemoryOLTP TO DISK = 'c:\temp\test.bak' WITH INIT,COMPRESSION
GO

As shown below, we have ~ 1GB of Log generated since last checkpoint, and after the backup log, the log spaced used % is still at 51%, and there is 1 active data file only which indicates the data/delta file stream has not been flushed to disk given this was a new database.

Now when we issue a manual checkpoint, the active files go up to 5 as data/delta files are flushed, and log bytes since last checkpoint go to 0. However, the log spaced used is still at 51% as we are in full recovery model and a backup log will free that up.

-- Manual checkpoint
CHECKPOINT
GO

After the backup the Log space used drops to minimal amount as expected.

-- Backup to Free up the log
BACKUP LOG InMemoryOLTP to DISK = 'c:\temp\test.bak' WITH INIT, COMPRESSION
GO

Had we waited until 1.5 GB of Log was filled up, no manual checkpoint would have to be issued and the log would be purged based on the size based threshold.

Large checkpoint for memory-optimized tables

Large checkpoints were designed for high throughput systems with very high log generation rates of up-to 1GB/sec. The purpose is to ensure that the checkpoint process would not be continually executing and would scale efficiently.

In the SQL error log, you will see messages indicating that we have detected that is a larger machine and as such by default “Large checkpoints” are used.

 2016-04-29 21:03:58.080               Server   SQL Server detected 4 sockets with 8 cores per socket and 16 logical processors per socket, 64 total logical processors; using 64 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
…
2016-04-29 21:03:58.080               Server   Detected 1048541 MB of RAM. This is an informational message; no user action is required.
…
2016-04-29 21:04:00.110               Server   In-Memory OLTP initialized on highend machine.

With Large checkpoints enabled, there are the following behavior changes:

• Data files created are now 1GB and delta files are 128MB instead of the smaller 128MB/16MB sizes.
• Automatic checkpoints are executed when 12 GB of the log is filled up since the last checkpoint.

Given this new behavior with SQL Server 2016, there are a few scenarios to consider where large checkpoints do have ramifications:

• In particular, on test systems where a workload may be executed and then paused at some point (not continuous workload running) you may notice that on small databases you can have 5GB of FREE files pre-created on disk and 12GB of log not cleared even after a log backup.
• There is the potential big increase in the recovery time if a crash occurs with lots of log between checkpoints. In the case of Large checkpoints, there could be up to 12GB of produced log between checkpoints that may need to be recovered on a failure.

The primary design point of large checkpoints was for the very high throughput systems with high log generation rate. If your average Log generation rate is less than 300MB/sec outside of short spikes, you do not need Large Checkpoints. There will be a fix in CU1 which change the default behavior to small checkpoints and enable Large checkpoints only under a trace flag. When CU1 is released I will update this to reflect that information as well as it will be documented officially.

I taught Power BI to a large organization this week. Naturally, they were interested in sharing content across multiple departments and even entire organization. Let’s revisit the sharing options available in Power BI which I summarize in the following table.

• Simple sharing – This is the only option available in Power BI Free. It allows sharing real-only dashboards and underlying reports to a small audience, such as with your boss or with a few coworkers. The content author would typically invite recipients by email. Recipients can’t change the shared dashboards and reports that someone else shared with them.
• Groups – Power BI groups (workspaces) are based on Office 365 unified groups. Anyone with a Power BI Pro subscription can create a group and add other users to the group. In the process of creating a group, the group owner specifies the group privacy level (private or public) and content access (edit or read-only). Groups are great to share content with members of a team, such as Sales, Finance, or a project. Public groups might seem like a convenient way to share content across the entire organization but they aren’t really “public” because a user still needs to add himself to the group. Once the group is created (give it some 30 minutes to become discoverable), the user can find the group in Outlook and add himself as a member of the group. So, public groups are public to the extent of allowing end users to gain membership without an administrator formally approving them. However, to share content across the entire organization, you probably won’t want to add every user to the group, especially considering that Power BI groups don’t currently support membership via AD groups or distribution lists.
• Organizational content pack – Similar to service content packs, organizational content packs allow the author to package and distribute content to any user or group (distribution and AD groups are supported) who’s interested in the content pack. Organizational content packs are discoverable in the Power BI Content Pack Library (in powerbi.com, click Get Data ð Organization). Content packs are designed for broader content delivery. By default, the content pack is read-only but users can create personal write copies of packaged dashboards and reports. So content packs are flexible.

To recap, if you need to distribute content across multiple departments or entire organization, organizational content packs should be the way to go.

Read more of this story at Slashdot.

Since Windows Server 2008/Windows Vista, from SQL Server 2008 onward, SQL Server installation process automatically generates per-service security identifier (SID). Whenever possible, it is recommended to grant rights to this service SID for security reasons, instead of your SQL Server’s startup account, which typically is domain user account.

For example, for performance reasons, I always want to SQL Server to have the following rights: Instant File Initialization and Lock Pages in Memory. The former enables instantaneous data (not log) file growth; whereas the later prevents Windows system from paging SQL Server data to virtual memory on disk.

Those rights can be granted via the Local Security Policy application, secpol.msc. Navigate to Security Settings -> Local Policies -> User Rights Assignment, you’ll find them there. Please note that Instant File Initialization is actually called “Perform volume maintenance tasks”.

Before service SID was introduced, I always granted those rights to SQL Server’s startup account. In my case it was typically a domain\user account. With the introduction of service SID, SQL Server’s resource access rights is the sum of both its startup account and service SID. Therefore it is recommended to grant rights to service SID, for obvious security reasons.

To prove that’s the case, let’s conduct the following experiment. For default instance of SQL Server, its service SID is NT Service\MSSQLSERVER. For named instance, its service SID is NT Service\MSSQL$InstanceName. Please note instant file initialization, once enabled, only works for SQL Server data files, not logs.

1. Assume your SQL Server instance is running under a domain\user account without “Perform volume maintenance tasks”;
2. Run the following code:

dbcc traceon(3004,3605,-1)
go
 
create database TestDb
go
 
exec sp_readerrorlog
go
 
drop database TestDb
go
 
dbcc traceoff(3004,3605,-1)
go

Pay attention to the output of “exec sp_readerrorlog”. You should see something similar to this:

2016-05-19 23:39:35.830 spid51 Zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDb.mdf from page 0 to 1024 (0x0 to 0x800000)
2016-05-19 23:39:35.890 spid51 Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TestDb.mdf (elapsed = 66 ms)

3. Now using secpol.msc, grant your service SID, in my case, NT Service\MSSQLSERVER, the right of “Perform volume maintenance tasks”;
4. Restart SQL Server instance;
5. Repeat step 2, you shouldn’t see entry similar to the one listed above in the error log, indicating that SQL Server has the combined rights of its startup account and its service SID.

By the way, this also applies to data and log folder permissions. Only grant data and log folder permissions to service SID, not its startup account. I have automated that process here.

Happy learning!

by Angela Guess Bernard Marr recently wrote in Forbes, “I’ll admit it: Big Data is not my favorite term.  It really trivializes and summarizes the trend too far, creating misconceptions and misperceptions of what this incredible shift in our technology, culture and world actually is. Despite the fact that I bill myself as a “big […]

The post 5 Signs You Don’t Get Big Data appeared first on DATAVERSITY.

It is pretty easy for a SQL Server administrator to recover the text of stored procedures, views, functions, and triggers protected using WITH ENCRYPTION. Many articles have been written about this, and several commercial tools are available. The basic outline of the common method is to:

1. Obtain the encrypted form (A) using the Dedicated Administrator Connection.
2. Start a transaction.
3. Replace the object definition with known text (B) of at least the same length as the original.
4. Obtain the encrypted form for the known text (C).
5. Roll back the transaction to leave the target object in its initial state.
6. Obtain the unencrypted original by applying an exclusive-or to each character: A XOR (B XOR C)

That is all pretty straightforward, but seems a bit like magic: It does not explain much about how and why it works. This article covers that aspect for those of you that find these sorts of details interesting, and provides an alternative method for decryption that is more illustrative of the process.

The Stream Cipher

The underlying encryption algorithm SQL Server uses for module encryption is the RC4™ stream cipher. An outline of the encryption process is:

1. Initialize the RC4 cipher with a cryptographic key.
2. Generate a pseudorandom stream of bytes.
3. Combine the module plain text with the byte stream using exclusive-or.

We can see this process occurring using a debugger and public symbols. For example, the stack trace below shows SQL Server initializing the RC4 key while preparing to encrypt the module text:

This next one shows SQL Server encrypting the text using the RC4 pseudorandom byte stream:

Like most stream ciphers, the process of decryption is the same as encryption, making use of the fact that exclusive-or is reversible (A XOR B XOR B = A).

The use of a stream cipher is the reason exclusive-or is used in the method described at the start of the article. There is nothing inherently unsafe about using exclusive-or, provided that a secure encryption method is used, the initialization key is kept secret, and the key is not reused.

RC4 is not particularly strong, but that is not the main issue here. That said, it is worth noting that encryption using RC4 is being gradually removed from SQL Server, and is deprecated (or disabled, depending on version and database compatibility level) for user operations like creating a symmetric key.

The RC4 Initialization Key

SQL Server uses three pieces of information to generate the key used to initialize the RC4 stream cipher:

1. The database family GUID.

   This can be obtained most easily by querying sys.database_recovery_status. It is also visible in undocumented commands like DBCC DBINFO and DBCC DBTABLE.
   

2. The target module's object ID.

   This is just the familiar object ID. Note that not all modules that allow encryption are schema-scoped. You will need to use metadata views (sys.triggers or sys.server_triggers) to get the object ID for DDL and server-scoped triggers, rather than sys.objects or OBJECT_ID, since these only work with schema-scoped objects.
   

3. The target module's sub-object ID.

   This is the procedure number for numbered stored procedures. It is 1 for an unnumbered stored procedure, and zero in all other cases.

Using the debugger again, we can see the family GUID being retrieved during key initialization:

The database family GUID is typed uniqueidentifier, object ID is integer, and sub-object ID is smallint.

Each part of the key must be converted to a specific binary format. For the database family GUID, converting the uniqueidentifier type to binary(16) produces the correct binary representation. The two IDs must be converted to binary in little-endian representation (least significant byte first).

Note: Be very careful not to accidentally provide the GUID as a string! It must be typed uniqueidentifier.

The code snippet below shows correct conversion operations for some sample values:

DECLARE 
    @family_guid binary(16) = CONVERT(binary(16), {guid 'B1FC892E-5824-4FD3-AC48-FBCD91D57763'}),
    @objid binary(4) = CONVERT(binary(4), REVERSE(CONVERT(binary(4), 800266156))),
    @subobjid binary(2) = CONVERT(binary(2), REVERSE(CONVERT(binary(2), 0)));

The final step to generate the RC4 initialization key is to concatenate the three binary values above into a single binary(22), and compute the SHA-1 hash of the result:

DECLARE 
    @RC4key binary(20) = HASHBYTES('SHA1', @family_guid + @objid + @subobjid);

For the sample data given above, the final initialization key is:

0x6C914908E041A08DD8766A0CFEDC113585D69AF8

The contribution of the target module's object ID and sub-object ID to the SHA-1 hash are hard to see in a single debugger screenshot, but the interested reader can refer to the disassembly of a portion of initspkey below:

call    sqllang!A_SHAInit
lea     rdx,[rsp+40h]
lea     rcx,[rsp+50h]
mov     r8d,10h
call    sqllang!A_SHAUpdate
lea     rdx,[rsp+24h]
lea     rcx,[rsp+50h]
mov     r8d,4
call    sqllang!A_SHAUpdate
lea     rdx,[rsp+20h]
lea     rcx,[rsp+50h]
mov     r8d,2
call    sqllang!A_SHAUpdate
lea     rdx,[rsp+0D0h]
lea     rcx,[rsp+50h]
call    sqllang!A_SHAFinal
lea     r8,[rsp+0D0h]
mov     edx,14h
mov     rcx,rbx
call    sqllang!rc4_key (00007fff`89672090)

The SHAInit and SHAUpdate calls add components to the SHA hash, which is eventually computed by a call to SHAFinal.

The SHAInit call contributes 10h bytes (16 decimal) stored at [rsp+40h], which is the family GUID. The first SHAUpdate call adds 4 bytes (as indicated in the r8d register), stored at [rsp+24h], which is the object ID. The second SHAUpdate call adds 2 bytes, stored at [rsp+20h], which is the subobjid.

The final instructions pass the computed SHA-1 hash to the RC4 key initialization routine rc4_key. The length of the hash is stored in register edx: 14h (20 decimal) bytes, which is the defined hash length for SHA and SHA-1 (160 bits).

The RC4 Implementation

The core RC4 algorithm is well-known, and relatively simple. It would be better implemented in a .Net language for efficiency and performance reasons, but there is a T-SQL implementation below.

These two T-SQL functions implement the RC4 key-scheduling algorithm and pseudorandom number generator, and were originally written by SQL Server MVP Peter Larsson. I have a made some minor modifications to improve performance a little, and allow LOB-length binaries to be encoded and decoded. This part of the process could be replaced by any standard RC4 implementation.

/*
** RC4 functions
** Based on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76258
** by Peter Larsson (SwePeso)
*/
IF OBJECT_ID(N'dbo.fnEncDecRc4', N'FN') IS NOT NULL
    DROP FUNCTION dbo.fnEncDecRc4;
GO
IF OBJECT_ID(N'dbo.fnInitRc4', N'TF') IS NOT NULL
    DROP FUNCTION dbo.fnInitRc4;
GO
CREATE FUNCTION dbo.fnInitRc4
    (@Pwd varbinary(256))
RETURNS @Box table
    (
        i tinyint PRIMARY KEY, 
        v tinyint NOT NULL
    )
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Key table
    (
        i tinyint PRIMARY KEY,
        v tinyint NOT NULL
    );
 
    DECLARE
        @Index smallint = 0,
        @PwdLen tinyint = DATALENGTH(@Pwd);
 
    WHILE @Index <= 255
    BEGIN
        INSERT @Key
            (i, v)
        VALUES
            (@Index, CONVERT(tinyint, SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1)));
 
        INSERT @Box (i, v)
        VALUES (@Index, @Index);
 
        SET @Index += 1;
    END;
 
    DECLARE
        @t tinyint = NULL,
        @b smallint = 0;
 
    SET @Index = 0;
 
    WHILE @Index <= 255
    BEGIN
        SELECT @b = (@b + b.v + k.v) % 256
        FROM @Box AS b
        JOIN @Key AS k
            ON k.i = b.i
        WHERE b.i = @Index;
 
        SELECT @t = b.v
        FROM @Box AS b
        WHERE b.i = @Index;
 
        UPDATE b1
        SET b1.v = (SELECT b2.v FROM @Box AS b2 WHERE b2.i = @b)
        FROM @Box AS b1
        WHERE b1.i = @Index;
 
        UPDATE @Box
        SET v = @t
        WHERE i = @b;
 
        SET @Index += 1;
    END;
 
    RETURN;
END;
GO
CREATE FUNCTION dbo.fnEncDecRc4
(
    @Pwd varbinary(256),
    @Text varbinary(MAX)
)
RETURNS varbinary(MAX)
WITH 
    SCHEMABINDING, 
    RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @Box AS table 
    (
        i tinyint PRIMARY KEY, 
        v tinyint NOT NULL
    );
 
    INSERT @Box
        (i, v)
    SELECT
        FIR.i, FIR.v
    FROM dbo.fnInitRc4(@Pwd) AS FIR;
 
    DECLARE
        @Index integer = 1,
        @i smallint = 0,
        @j smallint = 0,
        @t tinyint = NULL,
        @k smallint = NULL,
        @CipherBy tinyint = NULL,
        @Cipher varbinary(MAX) = 0x;
 
    WHILE @Index <= DATALENGTH(@Text)
    BEGIN
        SET @i = (@i + 1) % 256;
 
        SELECT
            @j = (@j + b.v) % 256,
            @t = b.v
        FROM @Box AS b
        WHERE b.i = @i;
 
        UPDATE b
        SET b.v = (SELECT w.v FROM @Box AS w WHERE w.i = @j)
        FROM @Box AS b
        WHERE b.i = @i;
 
        UPDATE @Box
        SET v = @t
        WHERE i = @j;
 
        SELECT @k = b.v
        FROM @Box AS b
        WHERE b.i = @i;
 
        SELECT @k = (@k + b.v) % 256
        FROM @Box AS b
        WHERE b.i = @j;
 
        SELECT @k = b.v
        FROM @Box AS b
        WHERE b.i = @k;
 
        SELECT
            @CipherBy = CONVERT(tinyint, SUBSTRING(@Text, @Index, 1)) ^ @k,
            @Cipher = @Cipher + CONVERT(binary(1), @CipherBy);
 
        SET @Index += 1;
    END;
 
    RETURN @Cipher;
END;
GO

The Encrypted Module Text

The easiest way for a SQL Server administrator to get this is to read the varbinary(max) value stored in the imageval column of sys.sysobjvalues, which is only accessible via the Dedicated Administrator Connection (DAC).

This is the same idea as the routine method described in the introduction, though we add a filter on valclass = 1. This internal table is also a convenient place to get the subobjid. Otherwise, we would need to check sys.numbered_procedures when the target object is a procedure, use 1 for an unnumbered procedure, or zero for anything else, as described previously.

It is possible to avoid using the DAC by reading the imageval from sys.sysobjvalues directly, using multiple DBCC PAGE calls. This involves a bit more work to locate the pages from metadata, follow the imageval LOB chain, and read the target binary data from each page. The latter step is a lot easier to do in a programming language other than T-SQL. Note that DBCC PAGE will work, even though the base object is not normally readable from a non-DAC connection. If the page is not in memory, it will be read in from persistent storage as normal.

The extra effort to avoid the DAC requirement pays off by allowing multiple users to use the decrypting process concurrently. I will use the DAC approach in this article for simplicity and space reasons.

Worked Example

The following code creates a test encrypted scalar function:

CREATE FUNCTION dbo.FS()
RETURNS varchar(255)
WITH ENCRYPTION, SCHEMABINDING AS
BEGIN
    RETURN 
    (
        SELECT 'My code is so awesome is needs to be encrypted!'
    );
END;

The complete decryption implementation is below. The only parameter that needs changing to work for other objects is the initial value of @objectid set in the first DECLARE statement.

-- *** DAC connection required! ***
-- Make sure the target database is the context
USE Sandpit;
 
DECLARE
    -- Note: OBJECT_ID only works for schema-scoped objects
    @objectid integer = OBJECT_ID(N'dbo.FS', N'FN'),
    @family_guid binary(16),
    @objid binary(4),
    @subobjid binary(2),
    @imageval varbinary(MAX),
    @RC4key binary(20);
 
-- Find the database family GUID
SELECT @family_guid = CONVERT(binary(16), DRS.family_guid)
FROM sys.database_recovery_status AS DRS
WHERE DRS.database_id = DB_ID();
 
-- Convert object ID to little-endian binary(4)
SET @objid = CONVERT(binary(4), REVERSE(CONVERT(binary(4), @objectid)));
 
SELECT
    -- Read the encrypted value
    @imageval = SOV.imageval,
    -- Get the subobjid and convert to little-endian binary
    @subobjid = CONVERT(binary(2), REVERSE(CONVERT(binary(2), SOV.subobjid)))
FROM sys.sysobjvalues AS SOV
WHERE 
    SOV.[objid] = @objectid
    AND SOV.valclass = 1;
 
-- Compute the RC4 initialization key
SET @RC4key = HASHBYTES('SHA1', @family_guid + @objid + @subobjid);
 
-- Apply the standard RC4 algorithm and
-- convert the result back to nvarchar
PRINT CONVERT
    (
        nvarchar(MAX),
        dbo.fnEncDecRc4
        (
            @RC4key,
            @imageval
        )
    );

Note the final conversion to nvarchar because module text is typed as nvarchar(max).

The output is:

Conclusion

The reasons the method described in the introduction works are:

• SQL Server uses the RC4 stream cipher to reversibly exclusive-or the source text.
• The RC4 key depends only on the database family guid, object id, and subobjid.
• Temporarily replacing the module text means the same (SHA-1 hashed) RC4 key is generated.
• With the same key, the same RC4 stream is generated, allowing exclusive-or decryption.

Users that do not have access to system tables, database files, or other admin-level access, cannot retrieve encrypted module text. Since SQL Server itself needs to be able to decrypt the module, there is no way to prevent suitably privileged users from doing the same.

The post The Internals of WITH ENCRYPTION appeared first on SQLPerformance.com.

Read more of this story at Slashdot.

by Angela Guess A recent article out of Redgate Software reports, “The long-awaited release of SQL Server Management Studio (SSMS) 2016 is approaching, bringing with it a more modern look and feel, more features, and more advantages for users. Redgate Software has been working closely with Microsoft to make sure the latest version of one […]

The post Teamwork is the Key to Developing SQL Source Control appeared first on DATAVERSITY.

Tweet

In another few days time SQL Server 2016 (RTM) will be available for the users and officially supported within the production environments. 2016 version is the first database born in the cloud, a new innovation in the world of data platform.

There are many exciting announcemnts happend within SQL Server world, such as SQL Server 2016 Developer Edition  will be a free download. Here is the document that you can pin up to know features by edition and compare them based on your requirement. If you have noticed or not, there is no Business Intelligence edition in SQL Server 2016, as the plan is to enable customers transition to get similar BI capabilities with available editions and Standard Edition will have basic HA capabilities (2 node single database failover including primary and non-readable synchronous replica and few more, see features by edition link above)

On that note I have an exciting news about first of its kind of event happening in the Scotland to discover possibilities from SQL Server, learn from industry experts, participate in hackathon-type competition in a group with exciting grand prize ($250 worth) and grab Microsoft swag too. So do not forget to register and share the excitement across your network

The event link:

.

Resources

New SQL Server 2016 performance benchmarks :Lenovo published a new #1 TPC-H 30 TB world record³ using SQL Server 2016 and Windows Server 2016 on Lenovo System x3950 X6

Series of whitepapers:

•  Introducing Microsoft SQL Server 2016 Download the free e-book

Read more of this story at Slashdot.

by Angela Guess Grace Peters recently wrote in HPCwire, “Thanks to machine learning, the page you see when you log-on to Amazon.com is likely very different from the one I see. Advertising, product recommendations, and special deals are all tailored to our unique customer profiles based on historical browsing trends and buying behavior. Online retailers […]

The post Improving Customer Experience with Machine Learning appeared first on DATAVERSITY.

Click to learn more about author Raghu Thiagarajan. Business Intelligence (BI) and analytics have, perhaps rightfully, been slower to take off in the cloud than other software categories. Customer Relationship Manager (CRM) was an early mover, driven by Salesforce, and since then we’ve seen other applications like payroll and expense reporting go heavily toward the cloud. […]

The post Four Reasons Why Big Data Analytics in the Cloud Makes Sense Now appeared first on DATAVERSITY.

Hopefully you went through my presentations Should I move my database to the cloud?, Introducing Azure SQL Database and Implement SQL Server on an Azure VM and are convinced to move your databases to the cloud.  If you are going to migrate to Azure SQL Database and want more info on how to copy your SQL Server databases from on-prem to Azure SQL database (PaaS), check out Migrate from on-prem SQL server to Azure SQL Database.  If you want to copy data from on-prem to SQL Server in an Azure VM (IaaS), check out the number of migration methods below:

• Use the Deploy a SQL Server Database to a Microsoft Azure VM wizard. Recommended method for migrating an on-premises user database when the compressed database backup file is less than 1 TB.  Fastest and simplest method, use whenever possible to migrate to a new or existing SQL Server instance in an Azure virtual machine.  Use on SQL Server 2005 or greater to SQL Server 2014 or greater.  But note this method is for only the classic deployment model
• Perform on-premises backup using compression and manually copy the backup file into the Azure virtual machine and then do a restore (only if you cannot use the above wizard or the database backup size is larger than 1 TB).  Use on SQL Server 2005 or greater to SQL Server 2005 or greater
• Perform a backup to URL and restore into the Azure virtual machine from the URL.  Use on SQL Server 2012 SP1 CU2 or greater to SQL Server 2012 SP1 CU2 or greater
• Detach and then copy the data and log files to Azure blob storage and then attach to SQL Server in Azure VM from URL.  Use on SQL Server 2005 or greater to SQL Server 2014 or greater
• Convert on-premises physical machine to Hyper-V VHD, upload to Azure Blob storage, and then deploy as new VM using uploaded VHD.  Use when bringing your own SQL Server license, when migrating a database that you will run on an older version of SQL Server, or when migrating system and user databases together as part of the migration of database dependent on other user databases and/or system databases.  Use on SQL Server 2005 or greater to SQL Server 2005 or greater
• Ship hard drive using Windows Import/Export Service.  Use when manual copy method is too slow, such as with very large databases.  Use on SQL Server 2005 or greater to SQL Server 2005 or greater
• If you have an AlwaysOn deployment on-premises and want to minimize downtime, use the Add Azure Replica Wizard to create a replica in Azure and then failover, pointing users to the Azure database instance.  Use on SQL Server 2012 or greater to SQL Server 2012 or greater
• If you do not have an AlwaysOn deployment on-premises and want to minimize downtime, use SQL Server transactional replication to configure the Azure SQL Server instance as a subscriber and then disable replication, pointing users to the Azure database instance.  Use on SQL Server 2005 or greater to SQL Server 2005 or greater
• Others: Data-tier Application, T-SQL scripts, SQL Server Import and Export Wizard, SSIS, Copy Database Wizard

More info:

Migrate a SQL Server database to SQL Server in an Azure VM

SQL Server 2014 Hybrid Cloud Scenarios: Migrating On-Premises SQL Server to Windows Azure Virtual Machines

How To Move or Migrate SQL Server Workload to Azure SQL Database Cloud Services or Azure VM – All version of SQL Server – Step-By-Step

Free ebook: Microsoft Azure Essentials Migrating SQL Server Databases to Azure

by Angela Guess Hugo Moreno recently wrote in Forbes, “The fact that corporations around the world are embracing business intelligence (BI) should come as no surprise. As you’d imagine, the advanced analytics developed by world-class BI practitioners leads to deeper insight and significantly enhanced performance. But decidedly newsworthy is the degree to which the most […]

The post The Case for Self-Service, Distributed BI appeared first on DATAVERSITY.

Read more of this story at Slashdot.

by Angela Guess Randy Lea of Teradata recently wrote in Forbes, “The word easy is not often associated with big data, but there is at least one pretty easy answer when it comes to the question of why companies embark on big data deployments: money, whether it is making it or saving it. What may […]

The post Getting Lost Along the Big Data Road appeared first on DATAVERSITY.

Hi Folks,

My series of articles for SQL Server Magazine continues. Last time, I continued a short series about a large database that needed to go on a diet. Last time, I look at the internals of row and page compression, to see what happens when they are used. We saw the significant differences in how ROW and PAGE compression are implemented. So how do you decide what to use?

This time, we look at when ROW and PAGE compression make sense, and provide detailed guidance on how to decide which should be used for which tables and indexes, or even for which partitions of which tables and indexes. A blended approach is usually the appropriate outcome.

http://sqlmag.com/sql-server/data-tales-8-case-database-diet-part-3

Enjoy!

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Originally published on Channel 9

The way this story starts is pretty interesting: one day in the lunch room in building 25 I happened to have a chat with the great Anders Hejlsberg about compilers (yeah, it was pretty cool). One thing he mentioned (in passing I might add) was that there is a huge gap between the way compilers are taught in school and the way compilers are implemented nowadays. I mentioned (again in passing) that we should do a whiteboard on the topic for Channel 9. He agreed!

In this video Anders a great foundation of compiler construction by describing the traditional methodologies that have been used in the last 30 or so years. He then uses that foundation to describe modern tooling needs and how compilers have adapted to meet increasing demands. The discussion was not only enlightening but also went a long way to show how Microsoft is taking great care to meet the modern needs of a wide array of developers. I hope you enjoy the discussion as much as I did!

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.