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

Protecting Data Between Services

I saw an announcement this week that Microsoft is shutting down their HealthVault service, where someone might have chosen to store their medical records in a way that it could be shared with health professionals, but an individual could maintain control of the records. They could share them out to friends, download them, etc. It was a good idea, and I was interested in it for the future. Since I have been fortunate to mostly avoid doctors and hospitals, I never signed up, but I still could as of this week.

Google used to have a similar product, but shut it down a few years ago. I suspect that this commercial space just isn’t as lucrative and valuable as either company thought, as I don’t really see any competitors out there that might allow a user to transfer their records. While I don’t know that there needs to be a service for this, I did think of the hassles and potential issues that might exist if there were. Imagine your mother or grandmother keeping their health records here for the last decade and now needing to download them and manage them before they could be used again. What if someone downloaded these to a PC and had a hard drive failure?

Backups are needed, we know that, and I’d hope most consumers know that, but in a rush or in the worry that these need to be copied, but securely, would you want to keep this data on a tablet or PC? Or would you want an encrypted drive. If that’s the case, would you want to ensure you have 2 (or more) of them? Easy to plan this out, hard to think about if you get a notice about the service shutting down. Imagine that your spam filter knocks this down or you’re inundated and miss it and get a final notice on Sept 1 or Oct 1? A real hassle.

The higher level view of this in my eyes is that we need better data formats for capturing and keeping lots of our data in systems. Those of us that are impacted by the GDPR (or similar laws) might be thinking about this already, as we have a need to provide data in response to requests. Providing a report, and packaging this up, is no small task. I assume I’d use a .zip file, but maybe a .nuget with a manifest is actually a better idea for customers.

As the use of services grows, I expect that we will want to get more portability for data over time. Certainly vendors that provide services have an incentive for tooling. WordPress has import/export for other blog services, Microsoft will help you move data from Oracle (as will Oracle in the other direction), and there are specialized vendors in niche applications doing the same, but really I’d like to see us have more open, and extensible, data formats that relate to the types of data in our lives. I know we’ll have more disparate types of data, in various formats, so why not an easy way for each of us to store text, images, and more as a service that contains some metadata, some indexing. A personal data lake of sorts.

We could have other services, like image services, visualization services for numbers, and more be authorized (or de-authorized) for our data. Imagine a way for us to allow a company to hold our data, but we disperse that to other vendors as needed for services. I could easily imagine various “storage” vendors competing and allowing us to “port” our data to a new service as easily as we port phone numbers for mobile phones. Perhaps a whole new era of data storage and management is coming.

Or maybe we’ll just stick with the current separated, proprietary, limited view of data that we manage on hard drives and USB sticks. It’s more likely, but much less preferable in my mind.

Steve Jones - SSC Editor

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

 
Redgate University
 Featured Contents

Service Broker Part 2: Why Service Broker

Klaus Aschenbrenner from SQLServerCentral

The second part of this series focuses on the advantages of Service Broker over other messaged-based technologies to introduce asynchronous message processing into your database applications.

Easing the transition from shared to dedicated database development

Additional Articles from Redgate

Working in a dedicated database model is the ideal. So, what does it take to move from shared development to a dedicated model? Infrastructure costs, merging multiple changes and maintaining an increased estate might be on your list of things to consider before committing to the switch. In this article we show how Redgate tools can ease the transition and new working approach.

SQL For Cosmos DB – Tips and Tricks

Additional Articles from SimpleTalk

Building on the introduction to the SQL used by Cosmos DB in the first article in the series, here you will learn how to handle some of the more classic challenges that you may face when querying JSON documents. Adam Aspin shows you some of the workarounds that are useful in practice when finding and shaping output data ready for further analysis.

From the SQL Server Central Blogs - Database Recovery Monitoring with XE

SQLRNNR from SQLServerCentral

Database recovery (crash recovery) is a nerve wrenching situation under the wrong conditions. It can be as bad as a root canal and just as necessary to endure that…

From the SQL Server Central Blogs - Data Type Precedence and Implicit Conversions

Bert Wagner from SQLServerCentral

SQL Server needs to make sure data types match when performing operations that involve multiple pieces of data. When the data types do not match, SQL Server has to…

 

 Question of the Day

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

 

Stop a Login

I have a login on my SQL Server 2017 instance that a contractor was using to do work in our environment. The contractor has left, and I are not sure if they will come back, but I do not want to lose all the setup and permissions. How can I prevent anyone from using this login without affecting the password that was used? The Login was for CarolD.

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

 

Redgate SQL Source Control
 

 

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

Service Broken Validation

In Service Broker, which of these items is associated with validation of the XML as well formed, against a schema, or ignored?

Answer: Message Types

Explanation: The Message Types are set to be validated or not. This can be well formed, XML validation, against a schema, skipped, or empty. Ref:

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 Agent scheduled job taking too long time to run in SQL Server 2014 - I am having a quite strange problem- the scheduled job in SQL Server Agent is taking abnormally long time to run. However the same job when I right click on the job and select “Start job at step…”, it finishes at desired time. Usually the job should be finished in about 15 to 17 minutes […]
t-sql 2012 row over partition - In the t-sql 2012 listed below there is a value that is determined in the Sped_Max column. There are times when the calculation of SUM(ISNULL(CONVERT(INT, C1023.value), 0)) + SUM(ISNULL(CONVERT(INT, c1024.value), 0))) DESC = 0. Can you tell me why me the value actually = 0, that nothing is displayed from the query Listed below? SELECT […]
Status Update 17 April 2019 - Some progress here, with less dev resources because of a few other internal fires. Forums – quoting posts should look better now, though a fix doesn’t help with code. I’d ask that you stop quoting the code, or at least delete the code when your reply is posted for now. Authors and newsletters linked in […]
Always on - Has anyone has configured always on from on premises to Azure cloud? If yes then have you ever came into any issues with that setup? Were there any pros and cons? Thanks
SQL Server Agent scheduled job taking abnormally long time to run - I am having a quite strange problem- the scheduled job in SQL Server Agent is taking abnormally long time to run. However the same job when I right click on the job and select “Start job at step…”, it finishes at desired time. Usually the job should be finished in about 15 to 17 minutes […]
HA: Reporting workload blocks redo threads due to IO contention - https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-primary-changes-not-reflected-on-secondary?view=sql-server-2017#BKMK_REDOBLOCK   We have HA setup and we are finding that the redo thread falls behind.   This tends to happen when there are large reporting workloads run against our secondary synchronous node.   Are there any options besides just “run fewer queries against the other node”?   If it was blocking we could address […]
SSRS Multi-Select parameter weridness - i’m not an SSRS guru but in the past the behavior of making a parameter multi-select would pass a comma delimited string. ie. selecting red and blue and green would give you ‘red,blue,green’ causing one to have to use some form of string split. However, I am working with a report that is passing the […]
How to sum a number of rows with minute data where column format is datetime - I’m working with a database where a column that includes duration in terms of minutes is stored as datetime.  The data looks like this.  Note the ‘1899-12-30’ info can be effectively ignored. Duration 1899-12-30 00:56:33.000 1899-12-30 00:26:27.000 1899-12-30 01:04:02.000 1899-12-30 00:13:30.000 1899-12-30 01:10:06.000 1899-12-30 00:23:02.000 1899-12-30 00:00:06.000   I am trying to do something like […]
Missing attachments - I remember there was an attachment to the following post (I’ve even got a post on this thread saying that I looked at it) and I can’t find it on the post anymore. https://www.sqlservercentral.com/forums/topic/insert-to-clustered-index Also, it still appears that attachments to articles are not available.  Any idea when this problem will be fixed?  
Unexpexcted behaviour STGeomFromText - Hey guys, I’ve got a weird behavior of SQL-Server regarding spatial objects here.  The code from below defines a postal code area in Frankfurt, Germany as a linestring. I want to transform this to a polygon, to check if some points are liying within this area.  But the resulting polygon is way bigger, than the […]
Viewing database diagrams - Hi all   We’ve designed some database diagrams on our DEV server (we have SA access). We want our analysts to be able to see these diagrams but we don’t want them to have db_owner access (even though it’s a DEV server). What I’ve done so far it to create a role within the database […]
On premises migration to Azure Managed Instance – link AAD login to db user - I am migrating an on premises SQL Server to Azure Managed Instance however having problems with changing the existing Windows groups database users to be Azure AD groups. I can manually add them by dropping users and recreating them but worried this will miss out some permissions as they are quite complex. Reading an article […]
Create a calculated member in a tabular model - Hello All, I am trying to add a calculated member in a dimension. In Multidimensional model, I do that like this: CREATE MEMBER [Material].[Department].[AB] ASAGGREGATE({[Material].[Department].&[A], [Material].[Department].&})   Is there a way to do the same in tabular model ?
Changing table data - Hi, I have a row in a table call ‘Time Reported’ with 29602 rows the data for the table looks like the below: 1899-12-30 09:12:32.000 each row has a different time but keeps the 1899-12-30 & .000 at the end. I beleive it is formatted like this because it was pulled from a old Access […]
Changing table data - Hi, I have a table name ‘Time Reported’ and the format of the data is : 1899-12-30 09:12:32.000 There are 29602 rows of data, this was imported from a old Access database i would like to run a SQL query to remove the 1899-12-30 & the .000 and leave the 09:12:32 Can anyone assist with […]
 

 

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

 

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