Tech News - Databases

Last week I presented on three separate occasions during what is considered the biggest Microsoft Data Platform conference of the year, the PASS Summit: Full-day pre-conference session Speaker Idol panel discussion 75-minute general session On account of the COVID-19 global pandemic going on right now the conference went virtual, which meant a lot of new-> Continue reading Setting yourself up for online streaming success, PASS Virtual Summit style The post Setting yourself up for online streaming success, PASS Virtual Summit style appeared first on Born SQL. The post Setting yourself up for online streaming success, PASS Virtual Summit style appeared first on SQLServerCentral.

The purpose of a foreign key is to ensure data integrity by making sure that data added to a child table actually exists in the parent table and preventing data from being removed in the parent table if it’s in the child table. The rules for these relationships are not terribly complex: The columns in […] The post Database Fundamentals #29: Create Foreign Keys With Table Designer appeared first on Grant Fritchey. The post Database Fundamentals #29: Create Foreign Keys With Table Designer appeared first on SQLServerCentral.

Have you ever cringed when you received your monthly cloud bill? Does it feel like you’re overpaying for your cloud services, but don’t know how to reduce your monthly cloud spend? In this video, I am revealing the secret to save on cloud costs that vendors won’t tell you. This is a must watch for every CXO that wants to incorporate cloud and save money at the same time. The post The Secret to Saving on Cloud Costs first appeared on Convergence of Data, Cloud, and Infrastructure. The post The Secret to Saving on Cloud Costs appeared first on SQLServerCentral.

As all of us know that Microsoft Azure Supporting many different database types such as Azure SQL, Azure Cosmos DB, and Azure support also MariaDB, MySQL, and PostgreSQL so it easily to migrate your current database (MariaDB, MySQL, and PostgreSQL) to Azure , in this post i will share Fundamental information about MariaDB, MySQL, and … Continue reading Fundamental Information for Azure Open Source Databases The post Fundamental Information for Azure Open Source Databases appeared first on SQLServerCentral.

It looks like there was a bug lurking in sp_RestoreScript that was causing the wrong ALTER DATABASE command to be generated when using @SingleUser and a WITH MOVE parameter. 1.8 fixes this issue. For information and documentation please visit https://sqlundercover.com/2017/06/29/undercover-toolbox-sp_restorescript-a-painless-way-to-generate-sql-server-database-restore-scripts/ The post sp_RestoreScript 1.8 Now Released appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to find out something new about someone you care about. I asked someone recently how they were doing during this tough time. They told me that they hadn’t been able to visit a parent in a retirement facility and only were able to video call with Skype. This surprised me, as the person was younger than I was, and I had assumed their parents would not be in that level of care. Shows what I get for assuming. I felt bad, and know what this is like, though my mother is far enough away that I can’t visit her often. I was supposed to go last month, but illness made me cancel. However, this person has their parent just a few miles away. Heartbreaking. The post Daily Coping 16 Nov 2020 appeared first on SQLServerCentral.

Note: This is not exactly a dyanmic linked server. It just gets dropped and recreated in a loop. I recently did a post on testing a linked server where I said I would explain why I wanted to make the test. Basically I needed to scan a few hundred instance names and do the following Check if the instance is one we have access to or even exists? If not make a note of the error so we can tell the difference. Collect information like instance size (total size of all databases), CPU count, memory count etc. Collect a list of database names on the instance, their status, size, etc. So the first thing I did was throw the list of instance names into a cursor and then put the code from my last post inside the loop. -- Create MyLinkedServer using the current server so that it exists and -- the code will compile. EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @@SERVERNAME; GO -- Create temp table to hold instance names. -- You'll probably want a permanent table. CREATE TABLE #InstanceList (InstanceName NVARCHAR(256)); INSERT INTO #InstanceList VALUES ('InstanceA'), ('InstanceB'), ('InstanceC'); -- Declare vars DECLARE @sql NVARCHAR(max); DECLARE @InstanceName NVARCHAR(256); -- setup cursor to loop through servers DECLARE InstList CURSOR FOR SELECT InstanceName FROM #InstanceList; OPEN InstList FETCH NEXT FROM InstList INTO @InstanceName WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @InstanceName; -- Test the linked server. EXEC sp_testlinkedserver @server = N'MyLinkedServer' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL END TRY BEGIN CATCH INSERT INTO dbo.[LinkedServerLog] VALUES ( @InstanceName ,ERROR_NUMBER() ,ERROR_SEVERITY() ,ERROR_STATE() ,ERROR_PROCEDURE() ,ERROR_LINE() ,ERROR_MESSAGE()); END CATCH FETCH NEXT FROM InstList into @InstanceName; END CLOSE InstList; DEALLOCATE InstList; -- Cleanup IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLinkedServer') EXEC master.dbo.sp_dropserver @server=N'MyLinkedServer', @droplogins='droplogins'; I now have a piece of code that loops through a list of instances and creates a linked server for each one. It then tests that linked server to make sure I can connect and if I can’t store the error into an error table. From there I can see which instances I couldn’t connect to and which I could connect but couldn’t log into. Now, all I have to do is add code into the try block that uses the linked server to collect information. -- Create MyLinkedServer using the current server so that it exists and -- the code will compile. EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @@SERVERNAME; GO -- Create temp table to hold instance names. -- You'll probably want a permanent table. CREATE TABLE #InstanceList (InstanceName NVARCHAR(256)); INSERT INTO #InstanceList VALUES ('InstanceA'), ('InstanceB'), ('InstanceC'); -- Create temp table to hold database sizes. CREATE TABLE #DBList ( InstanceName NVARCHAR(256), DatabaseName NVARCHAR(256), DatabaseSize DECIMAL(17,5) ); -- Declare vars DECLARE @sql NVARCHAR(max); DECLARE @InstanceName NVARCHAR(256); -- setup cursor to loop through servers DECLARE InstList CURSOR FOR SELECT InstanceName FROM #InstanceList; OPEN InstList FETCH NEXT FROM InstList INTO @InstanceName WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLinkedServer') EXEC master.dbo.sp_dropserver @server=N'MyLinkedServer', @droplogins='droplogins' EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @InstanceName; -- Test the linked server. EXEC sp_testlinkedserver @server = N'MyLinkedServer' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL INSERT INTO #DBList SELECT @InstanceName, dbs.name, SUM(size)/128/1024.0 FROM MyLinkedServer.master.sys.databases dbs JOIN MyLinkedServer.master.sys.master_files dbfiles ON dbs.database_id = dbfiles.database_id GROUP BY dbs.Name, dbs.database_id; END TRY BEGIN CATCH INSERT INTO dbo.[LinkedServerLog] VALUES ( @InstanceName ,ERROR_NUMBER() ,ERROR_SEVERITY() ,ERROR_STATE() ,ERROR_PROCEDURE() ,ERROR_LINE() ,ERROR_MESSAGE()); END CATCH FETCH NEXT FROM InstList into @InstanceName; END CLOSE InstList; DEALLOCATE InstList; -- Cleanup IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLinkedServer') EXEC master.dbo.sp_dropserver @server=N'MyLinkedServer', @droplogins='droplogins' ; A couple of notes here. I have a piece of code at the top that adds MyLinkedServer to make sure it exists when the code starts. Otherwise it won’t compile. Also, because of the connection time for the test, particularly if you have a bunch of instances that you can’t log into/don’t exist, this script is going to take a while just to handle the loop. Make sure that the data collection code is hitting system tables and/or is as quick as you can make it. Like I said in the title, this is pretty quick and dirty. This is the kind of thing you throw together because your manager wants some data collected from a bunch of sources ASAP and T-SQL is by far your best language. There are a lot of better ways to handle this. The post A quick and dirty scan of a list of instances using a dynamic linked server. appeared first on SQLServerCentral.

If you have been following me or generally topics around Azure SQL Database and security you would know that it is important to leverage Advanced Data Security (ADS) for Azure SQL Database, if you remember this meant having tools such … Continue reading ? The post Azure Defender for SQL appeared first on SQLServerCentral.

For quite some time now, there’s been the possibility to lift-and-shift your on-premises SSIS project to Azure Data Factory. There, they run in an Integration Runtime, a cluster of virtual machines that will execute your SSIS packages. In the beginning, you only had the option to use the project deployment model and host your SSIS catalog in either an Azure SQL DB, or in a SQL Server Managed Instance. But over time, features were added and now the package deployment model has been supported for quite some time as well. Even more, the “legacy SSIS package store” is also supported. For those who still remember this, it’s the SSIS service where you can log into with SSMS and see which packages are stored in the service (either the file system or the MSDB database) and which are currently running. The following Microsoft blog post gives a good overview of the journey that was made, and it’s a definite must-read for anyone who wishes to migrate their SSIS solution: Blast to The Future: Accelerating Legacy SSIS Migrations into Azure Data Factory. Something that surprised me in this blog post was this: “…our on-premises telemetry shows that SSIS instances with Package Deployment Model continue to outnumber those with Project Deployment Model by two to one” Microsoft (Sandy Winarko, Product Manager) This means a lot of customers still use the package deployment model. Many presentations at conferences about SSIS (even mine) are always geared towards the project deployment model. This is something I will need to take into account next time I present about SSIS. Anyway, I have done some fair amount of writing on the Azure-SSIS IR: Configure an Azure SQL Server Integration Services Integration Runtime Automate the Azure-SSIS Integration Runtime Start-up and Shutdown – Part 1 Azure-SSIS Integration Runtime Start-up and Shutdown with Webhooks – Part 2 Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime – Part 1 Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime – Part 2 Customized Setup for the Azure-SSIS Integration Runtime Execute SSIS Package in Azure with DTEXEC Utility Executing Integration Services Packages in the Azure-SSIS Integration Runtime Parallel package execution in Azure-SSIS Runtime SSIS Catalog Maintenance in the Azure Cloud Migrate a Package Deployment Integration Services Project to Azure Using Files Stored in Azure File Services with Integration Services – Part 1 Using Files Stored in Azure File Services with SQL Server Integration Services – Part 2 Azure-Enabled Integration Services Projects in Visual Studio The post Migrating SSIS to Azure - an Overview first appeared on Under the kover of business intelligence. The post Migrating SSIS to Azure – an Overview appeared first on SQLServerCentral.

Thursday evening, writing a few more notes. Felt like I struggled more to stay engaged and attentive today, which is above average for day 4 of a 5 day event, for me at least. About 3 pm I went for a long walk and listened to a presentation, that worked out well, it was stuff I mostly knew and didn’t need to see the slides. I used the phone app for that and it worked fine. Some general feedback about presenting, I think that it’s easy to spend too much time up front on the ‘me’ slide, the ‘PASS’ slide, and whatever else. I’d be interested to see what everyone thinks, but for me that’s about a max of 2 minutes. The question tab for the presentation tab is just ok. I saw at least one session where the answer was “answered”, presumably sometime during the presentation. That’s not very helpful to attendees (and moderators can mark questions as answered to help sort through the list). Better would be a quick sentence of two to summarize the answer, really superb might be that and/or a time mark in the presentation (go to 2 mins 15 to see more on the answer to this question, that kind of thing). I haven’t gone back to look, but for sessions that were pre-recorded in particular I wonder if the final recording will somehow contain the questions? The phone app has an ‘activity feed’ section, though on my phone it looks more like ‘Activity fee’. Mildly funny. Doesn’t get seem to be much used. Not being able to set a password really annoys me now. The phone app logged me out for whatever reason, so I had to go look up the password in the email they sent. There’s a session evaluation button on each “room”, and that’s good, but I consistently forget to do it while I’m there and then have to go back and fill them out later (which means I do fewer). Some samples below, the eval screenshot is about 50% of the total questions you can answer. Tim Ford talked a bit about financials at the start of the lunch keynote today and it was ok. Most of the key parts were there, but it would be easy to not read entirely between the lines. I appreciate trying to remain positive and highlight moves and decisions that have been made, but I’d have asked and hoped for something far more direct. I don’t see that video up as public anywhere yet. I went to office hours for the Board around 4 pm, it’s the same video chat used in the network bubbles. Grant & Lori were there and there was some casual conversation and some good questions. I like it as a less formal and less stressful way for the Board to interact, but I do like and miss the whole Board being in front of an audience taking questions at the same time. For those who read my post from yesterday about the state of PASS financials, I’d say there is no good news. Summit registrations and PASS Pro signups are both well under the goals in the budget. They should have a final accounting within a couple weeks. I’ll leave it to the Board to share the exact numbers. I didn’t do birds of a feather or really interact much with anyone today. Part introvert, part that it’s just not the same as seeing people and pausing for a few minutes of conversation. I hear that Jen and Sean have a discord channel set up, maybe tomorrow I’ll try to find that. As far as content, there has been a good variety on the schedule and no major technical issues that I’ve seen. The networking piece is just harder to do well and to be fair I don’t have a vision for how to do it better. Maybe one suggestion is that attendees could set up their own times to chat and have it show up on the schedule sometime “Chat with Andy at 4pm” or whatever. Tomorrow is the last day and the only new thing to try I have planned is moderating a session for Steve at 1030 I think. The post Notes on the 2020 PASS Virtual Summit – Part 5 appeared first on SQLServerCentral.

Whether you caught a single session, took a pre-con, or filled your schedule last week with multiple sessions, l’m going to review last week’s PASS Virtual Summit 2020 conference! The week of November 9th to 13th held the annual PASS Summit conference this year. Normally it’s held in Seattle WA, but this year due to COVID-19 it was held this year online on a virtual conference platform. I’m very thankful that they decided to continue with the conference instead of canceling it like so many other events have done this past year. It was certainly different than previous years, but certainly good to continue the conference in whatever venue this year would allow. I was quite fortunate to have presented a pre-conference all day boot camp called Amplify your Virtual SQL Server Performance on Monday November 9th. We spent the entire day with performance and availability topics for virtual enterprise SQL Servers, and I’m really thrilled with the response from the attendees and their line of questions. If any of you who took my precon and have any follow up questions in the future, please don’t hesitate to reach out and let me know. Next, my general session was called 10 Cloudy Questions to Ask Before Migrating Your SQL Servers. The session was the first session as part of the Modernizing with SQL Server Learning Pathway series. Before you can modernize and advance your SQL Server data platform, especially if you’re looking at the cloud, I presented my most important questions that you should have answers to before you embark on your modernization journey. As before, if anybody who attended or watched this after the fact have any follow-up questions, please reach out and let me know and I’ll be happy to answer them. I spent the rest of the week watching in on other people’s sessions and learning as much as I could. I absolutely loved the variety of the sessions and how distributed they were amongst multiple time zones. Given that this is now a global event because of the online nature, it’s great to see the accommodations for people that are not located in North America. If you have not already done so, please fill out your session evaluations. As the speaker, I’m always interested in what worked well and what could have been presented better. Feel free to be critical, but make sure to be constructive at the same time period if you think I was terrible, tell me, but tell me what I can do better or what specifically you didn’t like. All of the speakers do this because we want to become better speakers over time. Any improvements that you can suggest we’re going to take seriously. As a side note, they do have prizes that you can win for filling out your evaluations. Please make sure to do so by Friday November 20th at 5:00 PM eastern Standard Time to be eligible to win the prizes. Overall, the conference exceeded my expectations. I knew with the change of delivery mechanism, it was bound to be different. The in-person nature of these events really adds to the feel and the impact of the conference, as it’s not just the technical sessions that I’m there for. Having that networking time to meet up with longtime friends and make new ones is critical for this community. I had no technical glitches or issues with any of the tools that I was given to do the presentations. The video-based community zone was also a very nice touch so I could see people face to face. Yeah, not being there in person wasn’t the most fun, but the social aspect was still appreciated with the Welcome reception and Birds of a Feather groups. I think the toughest part about attending any conference this year is that work always tends to get in the way of conference activities. When you’re there in person, it’s difficult for people to reach you, so you can focus on the conference. When your remote, work tasks and emergencies always seem to creep up, and it’s difficult to stay focused for an entire day. Thankfully, complimentary streaming for one year is available for all regular sessions at the conference, so I’ll be able to watch the few sessions that I wanted to see but missed at some point over the next 12 months. Like any other conference, there’s always things that could have been done better in hindsight. I’ve got a short list of things that I’ll be sending over to PASS for review. It’s all things that can help the experience. If the event needs to be virtual next year, these are things that I’d like to see to improve the community aspect, such as having the community zone hangout space available all of the hours of the conference each day instead of select blocks of hours. If you have any constructive suggestions for improvement, send them over as part of the event evaluation because I know they are going to review these carefully. I also appreciate the candor that the board presented during the open Q&A sessions with the PASS board. This year’s been quite challenging, and it was comforting to see the openness at which they described the challenges of the year and what they intend to do about them. The amount of pain that accompanies changing a big event from physical to online is really tough, Especially for things like venue and hotel contracts. I applaud their efforts in making these changes as transparent and painless as possible for the speakers, the sponsors, and the attendees. I’d just like to thank all of the PASS board and PASS HQ for all of their hard work to make this event happen. I know it’s been an incredibly challenging year for all of us, especially when this is the primary revenue stream for PASS. I feel the value in the conference, and understand that they still needed to charge something for it while other conferences were going free for the year. I’m glad so many people attended, and I’m looking forward to seeing the evolution of PASS and this conference for 2021. Thank you all for making it to the conference and to take the time to watch this wrap up discussion. If you attended any my sessions or watch them after the fact, thank you very much! Feel free to reach out if anybody has any follow up questions or thoughts, and I’d love to know your opinion on the conference. Everyone stay safe, and hopefully we’ll all be back in person this time next year! The post Virtual PASS Summit 2020 Wrap Up first appeared on Convergence of Data, Cloud, and Infrastructure. The post Virtual PASS Summit 2020 Wrap Up appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to change your speech. When you feel like you can’t do something, add the word “yet”. A number of things I used to do are out of reach for now. In talking with some friends from down under last week at the PASS Virtual Summit, I joked about coming down if they could get me into the country. Australia and New Zealand are locked up against Americans, and for good reason. We don’t seem to be able to control this virus. I was sad, but I should have added a “yet” to the end. I did manage to remember this with my wife. Colorado has restricted gatherings and I had to inform my team that we can’t practice together for now. My wife lamented us competing in upcoming tournaments, saying that we can’t really play. I reminded her this is a “yet.” We’ll find a way to do this again. The post Daily Coping 17 Nov 2020 appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to change your normal routine today and notice how you feel. It’s PASS Virtual Summit week, and I have some commitments for the talks I’m doing. However, my talk today is in the afternoon, so I have some time this am. I’m going to change things up and go for a morning walk and get some exercise, then come in an work later into the evening. We’ll see if I like the chance. I typically prefer to work early and stop, but I’ll try something new today. The post Daily Coping 11 Nov 2020 appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to choose a different route and see what you notice on the way. The pandemic has kept most of us at home. We drive less, go less places, do less. For me, while I still go a few places, I do less for sure. When I saw this item pop up, I had to think about how I’d choose a new route. Walking from my house only gives me one route for a mile to get to the end of my street and out to a place where I can then go in a few directions. Driving around, it often doesn’t make sense to go a different way, but I thought this might be a good way to change my day. I’m lucky in that my gym has been open since May, and while there are restrictions and limitations, I can go. My week usually has 3-4 trips, twice for yoga and 1-2 trips for weights. I’ve avoided most classes, though I may go back to a swim a week as well. The route there is pretty simple, and while the facility is about 10mi away, I can take a separate route, wind through some neighborhoods slightly out of my way, and keep this to about 14mi. I did that recently. I took the long way, which winds alongside E-470 in S Denver, but also has a small bridge over the highway. That leads to the back of the neighborhood where I used to live. I drove through, looking at houses where friends used to live, or I used to bike/walk/horseback ride through. It was a nice trip on which to reminisce. The post Daily Coping 20 Nov 2020 appeared first on SQLServerCentral.

The post Configure SQL Server 2019 Multi-Subnet AlwaysOn Setup - Part 12 appeared first on SQLServerCentral.