Using Azure Data Studio and the SQL Migration Extension to Perform Offline Migrations (2021-10-11)

SQLServerCentral Newsletter for October 11, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Using Azure Data Studio and the SQL Migration Extension to Perform Offline Migrations]( - [State of Database Monitoring Report 2]( - [Building an ETL with PowerShell]( - [From the SQL Server Central Blogs - How old are those stats?]( - [From the SQL Server Central Blogs - Audit Columns]( Question of the Day - [Backup Operator Writing Backup Set Data]( The Voice of the DBA  Daily Coping Tip Write down three things you can look forward to in the next month I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Who's Smarter? Humans or AI Systems? This editorial was originally published on 27 Jul 2017. It is being re-run as Steve is at Data Minds Connect today. I used to watch science fiction movies and imagine we'd have these incredible machines that could interact with humans in amazing ways. I always believed more in the [helpful and useful AI in Star Wars]( than the dangerous systems of movies like [The Terminator](character_concept)) or [The Matrix](. I could even imagine a time when we have sentient systems, though I'm not sure they are coming anytime soon. I used to assume the [Asimov's Laws of Robotics]( would be implemented without any issue. After all, doesn't everyone worry about security? These days I'm more cynical, and I wonder if anyone would actually bother to program limitations in a system. I expect more people would assume we'll add in the laws later, once we know the other features are working. I do think we will get more and more systems like [IBM's Watson]( that are designed to handle many tasks. When I read pieces like [this one from HBR](, I actually get a little worried. Not because machines will destroy humans, but they will greatly impact and change our society, potentially in disruptive ways for society. While we've seen issues with manufacturing over the years, there are still humans involved, and we have learned to find other jobs over time. The AI revolution, if that's what's happening, will occur quicker, and if the HBR piece is correct, will impact many more people, across a shorter period of time. Our economies and workforce may not adapt as quickly, which has a variety of economic implications for all of us, even those of us that might be secure in our positions. If we can teach systems to accomplish tasks at a level even close to what the average human can do, we might find ourselves either out of a job or working with some sort of robot. I don't know how quickly we'll advance, or even how far we will go, but I do find that the leverage offered by implementing advanced technology is certainly increasing in a way that worries me. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Using Azure Data Studio and the SQL Migration Extension to Perform Offline Migrations]( bake13 from SQLServerCentral Learn how you can perform an offline migration of a SQL Server to Azure Managed Instances or IaaS VMs. [State of Database Monitoring Report 2]( Additional Articles from Redgate We’re sharing the results of our State of Database Monitoring survey in four insights reports this year – and the second one is out now. In The value of a monitoring tool for the entire organization you’ll learn why more businesses than ever are using a monitoring tool and how doing so can help the entire business to reduce costs, enhance security and collaborate more efficiently. DBAs - download a copy and send it to your boss, today! [Building an ETL with PowerShell]( Additional Articles from SimpleTalk There are many ways to load data into a SQL Server database. In this article, Greg Moore demonstrates how to get PowerShell to do more of the work. From the SQL Server Central Blogs - [How old are those stats?]( Kevin3NF from Dallas DBAs SQL Server maintains a variety of stats about all sort of performance items. Index usage (or missing indexes) Query performance Corrupt pages Disk IO performance Way more than I... From the SQL Server Central Blogs - [Audit Columns]( Kenneth.Fisher from SQLStudies One of the easiest ways to collect information about table activity is to add a series of audit columns to ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  Backup Operator Writing Backup Set Data I have a default US English installation of SQL Server 2019 and connect as a sysadmin. I create a new user with this script: USE [master] GO CREATE LOGIN [JoeBackup] WITH PASSWORD=N'XXXxxx', DEFAULT_DATABASE=[sandbox], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [sandbox] GO CREATE USER [JoeBackup] FOR LOGIN [JoeBackup] GO USE [sandbox] GO ALTER ROLE [db_backupoperator] ADD MEMBER [JoeBackup] GO This user logs in and is placed in the sandbox database. The user, JoeBackup, then executes this script: BACKUP DATABASE sandbox TO DISK = 'Sandbox_full.bak' Is the backup data written to msdb.dbo.backupmediaset, without granting JoeBackup permissions in msdb? 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) Changing a the Linux port I am running SQL Server 2019 on Linux and want to have the port changed on startup to be 51433. What can I do to accomplish this? Answer: sudo /opt/mssql/bin/mssql-conf set network.tcpport 51433 Explanation: The mssql-conf utility is used to change configuration settings. In this case, we set the network.tcpport value. Ref: Configure SQL Server on Linux with the mssql-conf tool - [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 - Development [Inline Table]( - Please assist creating an inline Table Valued Function. Output  Data [Inline Table]( - Please assist creating an inline Table Valued Function. Output should look like this: Data SQL Server 2016 - Administration [Shrink never ends]( - Hello, Sorry for my English. I am aware of the problems and why we should never perform a shrink, however, in this case it is necessary. I have a database where the mdf file is about 1.6Tb, but the database only uses 800Gb and I need to reduce it. I tried to reduce only 10Gb […] SQL Server 2016 - Development and T-SQL [Need help with calculating data between 6pm-6pm instead of 12am-12am]( - Hi, I am looking to calculate some data. We have orders dropping every day, and I need to calculate the number of orders that dropped between 6pm yesterday till 6pm today. I tried doing DATEADD(HOUR,-6,cast(CONVERT(VARCHAR,CREATE_DATE)+ ' '+ CONVERT(VARCHAR,CREATE_TIME) as datetime)) but that just changes every hour to go back 6 hours. I need to create […] [Dynamic SQL? Inserting Sproc data into table variable]( - Hello, I have a bunch of stored procs that I'd like to execute all at once, insert their results into a table variable, and then insert those results into a physical table. I can get the code to run fine to actually execute all the sprocs one after the other using a cursor (there aren't […] SQL Server 2019 - Administration [SQL 2016 to 2019 upgrade benefits]( - Can someone help me with good reasons / selling points to upgrade from 2016 to 2019? [SQL Server recovery]( - Hi There! Question related to recovery of SQL Server....When an SQL server crashes, from where does SQL server know the recovery path, as the buffer (RAM)contents are lost during crash ? Thanks in advance. Best Regards Arshad [How to start an Index Review Project]( - Dear Colleagues, I’m not sure this is the proper channel If you were in charge of review a group of databases and give index recommendations, and this is the particular point of interest. How could you start your session, I’m thinking in Current index inventory Current index usage New indexes recommendations What do you think […] [Unable to uninstall sql 2005 on windows 2008R2]( - Dear, unable to uninstall sql server 2005 on windows 2008R2 server, when i click on uninstall from control pannel, after a while the uninstall window will disappear and it is not uninstalling. Thanks SQL Server 2019 - Development [Non-clustered Index]( - I have a blog. To log in, I request a User Name. Should I have a non-clustered index on that column? Also, in another table, I query on the email address, should I have a non-clustered index on that column? In both tables, I have an integer field as the primary key. [View or Stored Procedure?]( - To create an output like this: Do i create a stored procedure or view that would allow this end result above?  SQL:  [Extraction of mean, max, min and sd extraction inside 5-95 quantiles]( - I'd like to extraction the mean, max, min and sd extraction inside 5-95 quantiles for the variables B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI aggregate by AGE and ESPAC variables inside CMPC table: CREATE TABLE CMPC( x NUMERIC(8,4) NOT NULL ,y NUMERIC(8,4) NOT NULL ,stand VARCHAR(11) NOT […] Azure Data Factory [data flow "delete if" setting- sink Delta lake]( - Hello, I have the upsert if working for the .parquet files by selecting the upsert if setting to true() For example when the source is update ,then the parquet sink is updated accordingly. But if a row is deleted in source it is still shown in sink parquet even when check the Allow Delete. Any […] Connecting [AZ Sql Database Cross Database Query Identity syntax]( - Attempting to follow directions from @SteveJones & MS documentation for setting up key for Azure Sql Server cross database queries. CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''; CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred WITH IDENTITY = '', SECRET = ''; We're receiving an error with the IDENTITY parameter. The examples look very straightforward but aren't […] General [Is it possible to improve one's environment, by going rogue?]( - Today Steve Jones posted an excellent article on whether or not your company has a talent gap. I recommend you read it. While responding to Steve's article I had a thought. I felt my ideas were too far off Steve's article, so I didn't want to detract from his thread. So, I'm going to ask […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -