Effectively troubleshoot latency in SQL Server Transactional replication: Part 2
Are you struggling with latency issues in SQL Server Transactional replication? Our comprehensive guide provides clear, step-by-step instructions to effectively troubleshoot and resolve these challenges. Dive into proven techniques and best practices that will help you enhance your SQL Server's performance and ensure seamless data replication. Don't let latency slow you down—master the art of SQL Server troubleshooting today! I hope you find this teaser engaging! If you need any adjustments or additional content, feel free to let me know.
[SQL Server on Azure VM] Automated Backups run daily when scheduled to run weekly
When we enable Automated Backup for SQL Server as documented in https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/automated-backup and if we setup manual schedule with Weekly backup, we will continue to see the backup of the databases happen daily. We had few of our customers report this so we wanted to blog about this issue and provide a workaround until the issue is fixed. There are 2 issues with this, as you see, we do not have an option to select which day of the week you wanted the backup to happen and the other one is with the code issue. This is currently known issue and we are working to fix this in near future, but until then we can work around the issue and fix it by running the following T-SQL to modify and make the changes using Managed Backup commands: -- Confirm the days_of_week has all the days selected and also get the information about backup_begin_time, backup_duration and log_backup_freq and update accordingly in below scripts SELECT db_name, is_managed_backup_enabled, scheduling_option, full_backup_freq_type, days_of_week, backup_begin_time, backup_duration, log_backup_freq FROM msdb.managed_backup.fn_backup_db_config(NULL) WHERE is_managed_backup_enabled = 1 AND full_backup_freq_type = 'WEEKLY'; NOTE: You see System databases Master, Model and MSDB because I had selected “Backup system database” option in earlier screen shot to enable backups for those aswell. Things you need to note from about is “backup_begin_time”, “backup_duration” and “log_backup_freq” and parameter we are interested in updating is "@days_of_week". -- Updating the backup config instance wide so that any new databases created, they already get added with the required info. -- We are updating @days_of_week to required day EXEC msdb.managed_backup.sp_backup_config_schedule @database_name = NULL, @scheduling_option = 'CUSTOM', @full_backup_freq_type = 'WEEKLY', @days_of_week = 'Monday', -- needs updated to your required day @backup_begin_time = '00:00', -- needs updated based on above output @backup_duration = '02:00', -- needs updated based on above output @log_backup_freq = '01:00'; -- needs updated based on above output GO -- Remember for existing databases this will get applied when you manually modify the values for each of them. So we have to manually update for each existing database DECLARE @DBNames TABLE (RowID INT IDENTITY PRIMARY KEY, DBName VARCHAR(500) ); DECLARE @rowid INT; DECLARE @dbname VARCHAR(500); DECLARE @SQL VARCHAR(2000); INSERT INTO @DBNames(DBName) SELECT db_name FROM msdb.managed_backup.fn_backup_db_config(NULL) WHERE is_managed_backup_enabled = 1 AND full_backup_freq_type = 'WEEKLY'; SELECT @rowid = MIN(RowID) FROM @DBNames; WHILE @rowID IS NOT NULL BEGIN SET @dbname = ( SELECT DBName FROM @DBNames WHERE RowID = @rowid ); BEGIN SET @SQL = 'EXEC msdb.managed_backup.sp_backup_config_schedule @database_name = ''' + '' + @dbname + '' + ''' ,@scheduling_option = ''CUSTOM'' ,@full_backup_freq_type = ''WEEKLY'' ,@days_of_week = ''Monday'' -- needs updated to your required day ,@backup_begin_time = ''00:00'' -- needs updated based on above output ,@backup_duration = ''02:00'' -- needs updated based on above output ,@log_backup_freq = ''01:00'''; -- needs updated based on above output EXECUTE (@SQL); END; SELECT @rowid = MIN(RowID) FROM @DBNames WHERE RowID > @rowid; END; If we now again run the first query above and should see the days_of_week reflect to the day(s) of your choice. Once done, it should work for any new database created. Point to note, if you disable and re-enable the Automated backup before the fix is released, we will have to go over the same process again. Hope this helps! Regards, Dinesh Babu Munugala Ref: managed_backup.fn_backup_db_config, sp_backup_config_schedule
How does GitHub Copilot in SSMS 22 handle database context collection before generating a response?
Hello, I am trying to better understand the internal workflow of GitHub Copilot in SSMS 22, especially for database-specific questions. From the product descriptions, it seems that Copilot can use the context of the currently connected database, such as schema, tables, columns, and possibly other metadata, when answering questions or generating T-SQL. However, I could not find clear official documentation about the actual sequence of operations. My main questions are: Before generating a response, does Copilot first collect database context/metadata from the active connection and then send that context to the LLM as grounding information? Or does it first use the LLM to interpret the user’s request, decide what information is needed, and then retrieve database metadata before generating the final answer? In some explanations, I have seen the phrase "Core SQL Copilot Infrastructure", but I cannot find any official documentation for that term. Is this an official component name? If so, what does it specifically refer to in the SSMS Copilot architecture? When Copilot answers schema-related or data-related questions, what information is retrieved automatically from the connected database, and is any SQL executed as part of that process? Is there any official architectural documentation that explains: context collection, prompt grounding, LLM invocation order, and whether query execution can occur before the final response is generated? I am asking because I want to understand the feature from both an architecture and data governance/security perspective. Any clarification from the product team or documentation links would be greatly appreciated. Thank you.
Sql Server complexity
For someone who has worked in PostgreSQL env for a very long time, learning the ropes of Sql server looks like some task. I practice with SSMS everyday, creating DB objects - schemas, tables, views, indexes, triggers, sequences, users, roles. And have noticed that Sql server code is more verbose, complex and not as straightforward as PostgreSQL. The use of 'stored procedures' for modifying column/table name and 'severity level' and 'state number' parameters for triggers was really topsy-turvy. I guess, it will take some time before I get used to the 'proprietary' way of doing things.Understanding the Differences Between SWITCHOFFSET and AT TIME ZONE in SQL Server
When working with date and time data in SQL Server, handling different time zones can be a critical aspect, especially for applications with a global user base. SQL Server provides two functions that can be used to handle time zone conversions: SWITCHOFFSET and AT TIME ZONE. Although they might seem similar at first glance, they have distinct differences in functionality and use cases. This article aims to elucidate these differences and help you decide which one to use based on your requirements. SWITCHOFFSET The SWITCHOFFSET function is used to change the time zone offset of a datetimeoffset value without changing the actual point in time that the value represents. Essentially, it shifts the time by the specified offset. Syntax SWITCHOFFSET (DATETIMEOFFSET, time_zone_offset) DATETIMEOFFSET: The date and time value with the time zone offset you want to change. time_zone_offset: The new time zone offset, in the format +HH:MM or -HH:MM. Example DECLARE @dt datetimeoffset = '2023-12-31 23:09:14.4600000 +01:00'; SELECT SWITCHOFFSET(@dt, '+00:00') AS UtcTime; In this example, SWITCHOFFSET converts the time to UTC by applying the +00:00 offset. AT TIME ZONE The AT TIME ZONE function is more advanced and versatile compared to SWITCHOFFSET. It converts a datetime or datetime2 value to a datetimeoffset value by applying the time zone conversion rules of the specified time zone. It can also be used to convert a datetimeoffset value to another time zone. Syntax DATETIME [AT TIME ZONE time_zone] DATETIME: The date and time value to be converted. time_zone: The target time zone name. Example DECLARE @dt datetimeoffset = '2023-12-31 23:09:14.4600000 +01:00'; SELECT @dt AT TIME ZONE 'UTC' AS UtcTime; In this example, AT TIME ZONE converts the datetimeoffset to the UTC time zone. Key Differences Functionality: SWITCHOFFSET only adjusts the time by the specified offset without considering daylight saving rules or historical time zone changes. AT TIME ZONE considers the full time zone conversion rules, including daylight saving changes, making it more accurate for real-world applications. Input and Output: SWITCHOFFSET works with datetimeoffset values and outputs a datetimeoffset value. AT TIME ZONE works with datetime, datetime2, and datetimeoffset values and outputs a datetimeoffset value. Use Cases: Use SWITCHOFFSET when you need a quick offset change without needing full time zone awareness. Use AT TIME ZONE when you need precise and accurate time zone conversions, especially when dealing with historical data and daylight saving time. Performance Considerations When working with large datasets, performance is a crucial aspect to consider. SWITCHOFFSET: Generally faster for simple offset changes as it performs a straightforward arithmetic operation. AT TIME ZONE: May incur additional overhead due to the complexity of applying time zone rules, but it provides accurate results for real-world time zone conversions. Example with a Large Dataset Suppose you have a Users table with 200,000 records, each having a CreatedDate column with datetimeoffset values in various time zones. Converting these to UTC using both methods can illustrate performance differences. -- Using SWITCHOFFSET SELECT COUNT(*) FROM Users WHERE CAST(SWITCHOFFSET(CreatedDate, '+00:00') AS date) = '2024-01-01'; -- Using AT TIME ZONE SELECT COUNT(*) FROM Users WHERE CONVERT(date, CreatedDate AT TIME ZONE 'UTC') = '2024-01-01'; In scenarios like this, benchmarking both methods on your specific dataset and SQL Server environment is advisable to understand the performance implications fully. CPU Times vs Total Duration Let's analyze the efficiency of the two alternatives (SWITCHOFFSET and AT TIME ZONE) when working with a table containing 200,000 records with different time zones in the datetimeoffset field named CreatedDate. Example Table Preparation First, create an example table Users with a CreatedDate field of type datetimeoffset and insert 200,000 records with different time zones. sql -- Create the example table CREATE TABLE Users ( UserID INT IDENTITY(1,1) PRIMARY KEY, CreatedDate DATETIMEOFFSET ); -- Insert 200,000 records with different time zones DECLARE @i INT = 1; WHILE @i <= 200000 BEGIN INSERT INTO Users (CreatedDate) VALUES (DATEADD(MINUTE, @i, SWITCHOFFSET(SYSDATETIMEOFFSET(), CONCAT('+', RIGHT('0' + CAST((@i % 24) AS VARCHAR(2)), 2), ':00')))); SET @i = @i + 1; END; Measuring Efficiency Now, measure the two alternatives for converting the CreatedDate field to UTC and then projecting it as date. Option 1: SWITCHOFFSET sql SET STATISTICS TIME ON; SELECT CAST(SWITCHOFFSET(CreatedDate, '+00:00') AS date) AS UTCDate FROM Users; SET STATISTICS TIME OFF; Option 2: AT TIME ZONE sql SET STATISTICS TIME ON; SELECT CONVERT(date, CreatedDate AT TIME ZONE 'UTC') AS UTCDate FROM Users; SET STATISTICS TIME OFF; Execution Plan and Timing Analysis After running both queries, compare the CPU times and the total duration reported by SET STATISTICS TIME ON to evaluate efficiency. Possible Efficiency Differences SWITCHOFFSET: SWITCHOFFSET is likely more efficient in this scenario because it performs a single operation to adjust the time zone and then projects it as date. This operation is done in a single step, which can reduce overhead. AT TIME ZONE: AT TIME ZONE might introduce a slight overhead because it first changes the time zone and then converts it to date. However, AT TIME ZONE is clearer and can handle multiple time zones more explicitly. Recommendation Although the real efficiency can depend on the specific environment and the detailed execution plan, generally, SWITCHOFFSET is expected to be more efficient for large datasets when only adjusting the time zone and projecting the date is required. Code for Testing in SQL Server sql -- Option 1: SWITCHOFFSET SET STATISTICS TIME ON; SELECT CAST(SWITCHOFFSET(CreatedDate, '+00:00') AS date) AS UTCDate FROM Users; SET STATISTICS TIME OFF; -- Option 2: AT TIME ZONE SET STATISTICS TIME ON; SELECT CONVERT(date, CreatedDate AT TIME ZONE 'UTC') AS UTCDate FROM Users; SET STATISTICS TIME OFF; Comparing Results CPU Times: Compare the CPU times reported by both queries. Total Duration: Compare the total duration of execution of both queries. Evaluating the results from the time statistics will help determine which option is more efficient for your specific case. Additional Considerations Indexes: Ensure that the CreatedDate column is indexed if large volumes of data are expected to be read. Parallelism: SQL Server can handle the query in parallel to improve performance, but parallelism settings might affect the results. Real-World Workload: Conduct tests in an environment as close to production as possible to obtain more accurate results. Conclusion Choosing between SWITCHOFFSET and AT TIME ZONE depends on your specific needs: Use SWITCHOFFSET for simple, quick offset changes where historical accuracy and daylight saving adjustments are not critical. Use AT TIME ZONE for comprehensive and accurate time zone conversions, especially in applications dealing with users across multiple time zones and needing historical accuracy. Understanding these differences will help you make informed decisions in your SQL Server applications, ensuring both performance and accuracy in your date and time data handling.
Migration from Sybase ASE using SSMA CLI
Recently I learned a lesson through multiple testing around SSMA for Sybase. Let’s suppose you need to migrate your data from Sybase ASE to SQL Server. You could use the SSMA GUI tool to connect to both destinations and select schemas or objects to convert and migrate. Now what if you needed to automate this process?
Help creating an Access pass-through query to insert data into an SQL Server table
I have a valid pass-through query that works and creates the record in the SQL server table, but it is one-off and I would have to manually change some field values each time and re-run to add a bunch of records. Sometimes my program has to add 2 or 3 but sometimes a few hundred. Therefore I need to do this in code, cycling through the source table in Access, getting part of the data I need. I have the code running to cycle through the code, testing it with debug.print, and I think I have the SQL string built correctly, but DoCmd.RunSQL "mySQLString" gives the following error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE, 'SELECT', or 'UPDATE'. Here is my SQL string. INSERT INTO Attendance (Attendance, Employee, Work_Date, Regular_Minutes, Attendance_Type, Lock_Times, Source, Last_Updated) VALUES (NewID(), 'PERKR', '12/28/2020',600,2,-1,0,'05/14/2020'); My references are set to ADODB instead of DAO. Thanks, robtperk
