Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Redgate SQL Provision
The Voice of the DBA
 

The Pyramid of Data

Data is an important part of our world, and arguably the most important asset in computing. All the rest of the devices, platforms, and technologies we use are designed to work with data, by manipulating, storing, accessing, and presenting data in new ways. We need devices and operating systems to host software, and applications to work with data, but the data is the key to fuel for every engine.

I see there being a pyramid of data, with various technologies that are used to store and work with data. This is roughly how I see things, with various sources that store data as the foundation, and then systems to summarize and aggregate data, a new layer of analytics with Data Science, and the ever present ways of interacting with the data in order to use it for insights and decisions.

(SSRS) (Visualizations) (Excel) (Power BI)

(Data Science) (Artificial Intelligence) (Machine Learning)

(Data Warehouses) (Data Marts) (ETL) (Data Streams) (Linked Data Sources)

(SQL Server) (Oracle) (CosmosDB) (ElasticSearch) (Redis) (HDFS) (MongoDB) (PostgreSQL)

That's been my traditional view, but cloud computing, the orchestration of containers, and better ways of analyzing data without moving it lead me to think that this is becoming more of a mesh inside the pyramid that multiplexes connections between layers. While I do think AI and ML systems will become more and more useful to a wider variety of applications and organizations, I do think it will move more slowly than the hype suggests. Likewise, I think containers will grow slowly as there is a need to rearchitect many applications, but the advances in the cloud are driving to forward faster than I expected.

Certainly cloud computing is becoming more and more commonplace. I especially am starting to see more smaller organizations taking advantage of cloud platforms that build SaaS, not for large scales, but for very small scale organizations. The platforms themselves are constantly lowering the cost of engaging with the cloud at small scales, and making it more feasible for application developers to deliver tremendous value and capabilities to very small organizations that aren't, and don't want to be, software companies. They just need services and capabilities without a lot of effort. The Power platform from Microsoft is likely to accelerate this with easy development for any semi-skilled software developer.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Redgate SQL Provision
 
 Featured Contents

Moving Encrypted Data to Azure SQL Database

Steve Jones - SSC Editor from SQLServerCentral

Learn how you can move encrypted data from on premises SQL Server to Azure SQL Database.

Making the switch to SQL Monitor

Additional Articles from Redgate

Australia and New Zealand’s leading cloud account software provider Xero needed a cost-effective monitoring solution with better features and coverage. Read Xero’s story about how they switched from their old monitoring tool to Redgate’s SQL Monitor.

Introduction to the Partition By Window Function

Additional Articles from SimpleTalk

Window functions can be life savers by making a complicated SQL calculation easy. A window function combines that logic and provides row by row or window by window feedback. Read on to learn more!

From the SQL Server Central Blogs - How do you use PowerShell toc check if an active directory user locked out, disabled etc.?

SQLPals from Mission: SQL Homeostasis

If your organization uses a password policy (there are very good odds these days that it does) and, especially stricter password requirement for administrative users, your might have experienced...

From the SQL Server Central Blogs - SQL Undercover TV – Encrypting SQL Backups

david.fowler 42596 from SQL Undercover

In this episode, David looks at how we can encrypt our SQL Server backup files

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Querying for Table Allocated Pages

I am working in a SQL Server 2017 database and am trying to determine how large a table is. This is an unpartitioned table, and I want to know where I can get a count of the pages used by my table. What DMV should I query?

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

The Default NULL

I create a table like this:

CREATE TABLE NullTest
(SomeKey INT);
GO

If I now run this:

INSERT NullTest VALUES (NULL)

Under what conditions does this return an error?

Answer: When ANSI_NULL_DFLT_OFF = ON

Explanation: There is not instance level setting for this. When ANSI NULL defaults are set to off, meaning either the data database or the session is set with ANSI_NULL_DEFAULT_OFF = ON, the default column is set to NOT NULL. If ANSI_NULL_DEFAULT_ON = ON, the default is NULL. Ref: Nullability rules within a table definition - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15#nullability-rules-within-a-table-definition

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration
Unable to Join Databases to AG - I have a stand alone SQL 2008 R2 SQL Server that I'm migrating to a 3 node AG. At various times during the testing of the restore process to get the databases onto the new SQL Server 2017 servers that are on CU16  I run into a scenario where one or more databases will join […]
Using Storage DeDupe for AlwaysOn Availability Groups - I have a Sr. Sysadmin here who wants to turn on storage dedupe for the three nodes that will be host AG's because he really wants space savings.  The setup is that I actually have 3 sets of 3 nodes.  Each set of three nodes will be hosting databases from what is now a single […]
SQL Server 2017 - Development
sqlcmd - Unable to pass in exact file path as an input variable to :out - Hi there I have a routine which outputs an ID generated from a stored procedure into a text file as follows: :!!if exist \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt  del \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt --:connect localhost\SQL2008R2 :out \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt select Ltrim(Rtrim(@StoredProcedureRunTrackerID)) Now i wanted to change this , so that the path for :out is not hardcoded and passed in as a variable, ie […]
SQL Server 2016 - Administration
Package error - when i ran the package through sql agent job  it got failed but i did not find any information in job history Executed as user: . Started: 11:59:55 AM Finished: 12:00:43 PM Elapsed: 47.281 seconds. The package execution failed. The step failed. How can we trouble shoot
Extended events - MY EXTENDED EVENT SCRIPT: CREATE EVENT SESSION [CLogins] ON SERVER ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1) ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE (([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DELETE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INSERT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CREATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DROP%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%RENAME%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TRUNCATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%COMMENT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MERGE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CALL%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%EXPLAIN PLAN%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%LOCK TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%GRANT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%REVOKE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEXES%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEX%')) […]
SQL Server 2016 - Development and T-SQL
The data types varchar and datetime2 are incompatible in the add operator. - In the procedure i have date column as datetime2 declare @date datetime2 = null,  @CaptureError varchar(100) when i am logging this variable like below SET @CaptureError= @CaptureError + ' ,Date: ' + coalesce(@Date, 'NULL') this is getting error as data types varchar and datetime2 are incompatible in the add operator. what is the workaround for […]
SSIS For each Loop not finding files when deployed - Hi, I have an issue with a SSIS package deployed to the catalog. This package includes a For Each container that scans a folder for CSV files. I can run the package from Visual Studio on my development box using my credentials and it completes successfully finds the files targeted by the for each loop […]
Administration - SQL Server 2014
Problem with calculating an approximate space required for online index rebuild - I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes of mdf and ldf files were as below:                   total size      […]
Development - SQL Server 2014
Determining if more CPU is needed - We have a data import & processing process that often longer than expected, often delaying the time at which users can begin working. I've determined that the CPU runs around 80% for the 1-2 hours the process runs (90 minutes is the SLA), but rarely hits 100 percent. I've found some queries that look at […]
SQL 2012 - General
Join Query, two tables, two databases? - Is that possible?  To have two databases with one query?  If so the query below should populate some data!  If not, how can I accomplish the task? SELECT JobSheet.dbo.JobHrs.JobNo, JobSheet.dbo.JobHrs.ActualHrs, JobSheet.dbo.JobHrs.ReworkHrs, TimeReporting.dbo.TimeData.Time, TimeReporting.dbo.TimeData.Rework FROM JobSheet.dbo.JobHrs INNER JOIN TimeReporting.dbo.TimeData ON JobSheet.dbo.JobHrs.JobNo = TimeReporting.dbo.TimeData.JobNo WHERE JobSheet.dbo.JobHrs.JobNo = '19-0228B'  
General Cloud Computing Questions
Archival Options due to Regulation -   Hi Guys, I have an ongoing need to provide a archival solution for an ex-production system for Regulatory reasons. In a nutshell: active database is around 30GB, but there will be others pending of varying sizes there may be the need for a couple of queries a year initially, tapering off potentially need to […]
SSDT
Using variables in multiple packages - I have a SSIS Solution built in Visual Studio Community 2019 using SSDT. The solution contains about 15 packages. I am using 5 variables (created in the first package) that I would like to use in all of the other packages. Is there a way to make a variable usable in other packages within the […]
Expression won't evaluate for property - I am attempting to parameterize the three properties for checkpoints in a SSDT 2017 SSIS project and keep receiving this error for the CheckpointUsage property: Error loading: The result of the expression "@[$Project::parCheckpointUsage]" on property cannot be written to the property. The expression was evaluated, but cannot be set on the property. I have the […]
Analysis Services
Row Level Security based on multiple factors - Hey there, I recently got a request from some people here to implement another level of RLS for a couple of tabular models that I manage. Background: I work for a commercial real estate company, so the security that I have implemented is based on region. Someone in Toronto can only view data from Toronto. […]
Events
PASS Local Group/Virtual Group/SQL Saturday organizers corner - A dedicated place for PASS Local Group/Virtual Group/SQL Saturday organizers to share marketing and promotional content as well as discuss what is working and what is not (credit for the idea goes to Cecilia Brusatori)
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -