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

Data Orchestration

We continue to deal with larger and larger data sets all the time. In fact, it seems that most people find themselves outgrowing the capabilities of some of their OLTP databases, often RDBMS stores, and need to upgrade hardware or re-architect software. It doesn't matter if you have a 100GB database on a few cores or 10TB on dozens of cores, there is often a need to upgrade to meet our workload demands.

In addition to the transactional needs, there is a growing demand for reporting and analysis workloads. Some people use a separate warehouse, and some want to just query data where it is. Certainly ETL processes and platforms have grown tremendously over the last few decades for those that want to implement the former process, but there is plenty of demand for the latter. In fact, I'm amazed how many customers have inquired if Redgate's SQL Clone product will enable them to do this and spread their workload to other systems (it's not designed for this).

I've been thinking that with SQL Server 2019 we will start to access data where it lives, not move it to another place we want it. To me, this is more of what future data orchestration might involve. I ran across an article that takes a slightly different approach, thinking AI and other products will help better move data around, and perhaps that's true, but I do think more and more we want to query data where it lives, and use larger, distributed compute platforms to do this.

The scale out capabilities of SQL Server 2019, with the separation of compute and storage in Big Data Clusters, is a huge change that I think will be the future for many of us that look to meet reporting needs. The ability to grow hardware to match the workload needs is huge. This alone is a good reason to think about doing this in a hybrid or public cloud scenario.

Of course this doesn't come cheap, easy, or quick. There is work to be done to evolve systems, but it is an area I think is worth experimenting in during the coming year. I bet many companies would be interested in some PoC work here to determine how to better meet the reporting requirements of larger data sets. Perhaps this is something you suggest to someone in your organization.

Steve Jones - SSC Editor

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

Redgate SQL Source Control
 
 Featured Contents

Exploring the DATE Functions in SQL

aveek22 from SQLServerCentral

Learn more about using the DATEADD function in SQL...

But the Database Worked in Development! Checking for Duplicates

Additional Articles from SimpleTalk

As developers should not have access to production data, it’s possible that duplicate values can sneak in during migrations. In this article of the series, Phil Factor demonstrates a way to check for duplicates when unique constraints are disabled.

From the SQL Server Central Blogs - ACM Lecture at SELU

Will Assaf from SQL Tact

Had the pleasure of presenting to Dr. Ghassan Alkadi and a full house at the Southeastern La. University ACM group. Big thanks to the bright student organizers for inviting...

From the SQL Server Central Blogs - Removing ad hoc plans from Query Store

DesertDBA from The Desert DBA

This is not a post about the “optimize for ad hoc workloads” setting on your favorite SQL Server instance, but that is a good place to start. Lots of...

 

 Question of the Day

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

 

Identity Guarantees

Which of these is not a guarantee of the Identity property?

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)

Inserting In the Middle

I have a list of scores in Python for the tests given to a student.

scores = [92, 95, 94, 98]

I realize that I forgot to add a score in the middle, between 95 and 94. How can I insert an 88 in here?

Answer: scores.insert(2,88)

Explanation: There is an insert() method for adding items to the middle of the list. This takes the position of the insert and the value. Note that python is 0 based, so the 2 is used to insert after the 95. Ref: Python Data Structures, 5.1 - https://docs.python.org/2/tutorial/datastructures.html

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
SSMA 8.6 Unable to find functions in extended stored procedures - I have an instance of SQL Server 2017 (Developer) installed with SSMA for Oracle Extension Pack 8.6. Whenever I try to execute one of the three extended stored procedures from the Master db, I see the same error: "Could not find the function xp_ora2ms_versioninfo2 in the library Microsoft SQL Server Migration Assistant for Oracle Extension […]
TLS on SQL Server 2014 - Hi , I need some expert advise from the team here. There is a requirement to enable TLS on SQL Server -Microsoft SQL Server 2014 (SP3-GDR). I have never implemented this and no clue how to start with. I see some topics saying to just enable Force Encryption - to YES, in the SQL server […]
2017 SE - questions on failing over only 1 (of many) AG then connecting to it. - I am just getting my feet wet with always on availability groups, trying to learn as I go along. A company I am doing some work for just deployed MSSQL 2017 SE to a 2-node always on installation, with the intent of migrating a number of databases currently on MSSQL 2008  & 2012 to it. […]
SQL Server 2017 - Development
Merge statement - matching ON all columns - Dear SQL users, I have a need to refresh tables from Source to Target.  there is no particular 'key' or combination of 'key-ish' values for my ON portion.  All I am doing in lieu of that is to match on all columns.  on sourcetable.col1 = targettable.col1 and sourcetable.col2 = targettable.col2  and so on and so […]
SQL Server 2016 - Administration
Running generic query for table information - Hi, I want to run the below query to get information about the tables and columns for better understanding. However my fear is since the databases are in Tb's and has many tables will this query cause any intermittent slowness or any blocking since the system is busy when I am running this query. What […]
Instance level trigger to store login information (who created what) - Hello SSC, I am looking for a simplified way to track which user created which login, Surprisingly enough, the sys.logins table does not have this information. My company has SQL environments ranging from 2008 R2 - 2017. So this is going to be challenging. At the moment, we have no way in knowing who created […]
Administration - SQL Server 2014
REPLICATION, Dropping Subscription Taking A Long Time - USE Published_DB; EXEC sp_dropsubscription @publication = N'Published_DB_Publication', @subscriber = N'all', @destination_db = N'Published_DB', @article = N'all'; I have a fairly large DB. It is replicating approx 7000 articles. When I run the script above, it's taking a long time (now 1 Hr+). Any ideas what I need to check here? Adding, I have all REPL […]
Linked server - I have created linked server from instance A to instance B. when i do a test connection it is successful but when i try to do a test connection from outside the server it shows login failed for user NT authority\anonymous logon. Any inputs?   i registered SPN for services in both instance A and […]
SQL 2012 - General
Table self-join for derived columns - Hi, I have a requirement to derive additional columns and populate the values by doing a self-join. I have tried to use self-join and cross apply but not getting the desired results. I have attached the desired result format and also included the DDL and sample data. Could somebody please help in this regard. Thanks. […]
SQL Server 2019 - Administration
Question upgrading - Hi, I see the post of going from 2012 - 2019 but we were wondering if we should go to 2017 or 2019. Currently, we only have about 20 users and do not do anything with warehousing; of course, both could change down the road. Someone said that they found it to be much less […]
Business Justifications for Upgrading SQL 2012 to SQL 2019 - You'd think it's obvious why we need to upgrade from SQL 2012 to SQL 2019 (4 versions), but I have to justify the upgrade to non-technical executives.  I'm looking for good blog posts/white papers/articles that enumerates reasons why staying on SQL 2012 is not viable, and why upgrading to SQL 2019 is necessary.  Any suggestions?
SQL Server 2019 - Development
Constraint on multiple columns - Hi, I want to restrict users from adding duplicate rows. Is there a way to put one constraint on multiple columns, or use a trigger to do this, maybe BEFORE insert/ BEFORE update? Thanks S
How to use next record's date as End date of current record? - I have a select statement which its result is something like this: I would like to have an end date column which is the next start date. I would appreciate it if someone could guide me.    
Global Unique Identifier Field - I was wondering if someone could give me some good examples or explanations of why I would use the Guid type for a column in my table. Thanks !!!
SQL Azure - Administration
Migrating Azure VM from unmanaged disks to managed disks. - I have worked quite a bit on physical and VM's. Trying to get my hand dirty in Azure world. I was given a task related to performance , i noticed all the disks were created as unmanaged and i confirm via various stress tests and comparing different environments that this could be one of the […]
 

 

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

 

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