Synchronize SQL database between two servers
Questions (to make right the synchronize SQL database between two servers) 1. Is required to have SQL Enterprise in both servers, for to be able the synchronization? Or will be enough to have SQL Enterprise in server1. And in server2 with only SQL Express, please? 2. Which requirements are required to fulfil or prepare for to be able synchronize SQL database between two servers, please? 3. Which possibilities (when are more possible solutions) have existed for synchronize SQL database (totally awaiting between four servers), please? 4. Exist a manual for reading an synchronization of SQL server - settings and steps? Thanks in advance.
SQL Server AG Failover - Automatic Failover
Hello, I am looking for a straight and definitive answer that I was hoping someone could answer for me. I want to trust what Copilot says, but I really need to hear it from Microsoft and I can't find any documentation from Microsoft confirming my question. My Environment: 2 replicas in datacenter 1 1 replica in datacenter 2 All three (3) replicas are set to synchronous-commit mode with automatic failover. I tested the failover manually between all three (3) replicas without issue. When I test the automatic failover - I take down both replicas in datacenter 1 at the same time to simulate a datacenter outage. I look at the replica in datacenter 2 and it is just says (Resolving...) next to the replica name. The replica does not come online and the DB is not moved. When I was searching I couldn't find out why. So I turned to Copilot not solve the issue, but to see if it could point me in the right direction. I tell Copilot my setup and what happened. Copilot responded stating that by design from Microsoft you cannot have more than two (2) replicas set to synchronous-commit mode with automatic failover in a SQL Server AG instance. That if more than two (2) are set for automatic failover. The SQL Server AG will use the first two (2) replicas it sees in its metadata and ignore the rest. Copilot went into detail about why this is designed this way, but the amount of information would make this post longer than it already is. If this is true - then when I took down both replicas in datacenter 1, SQL Server AG only saw those two (2) replicas in datacenter 1 as the available replicas to use for an automatic failover and thus why the replica in datacenter 2 did not come online and the DB not being moved So let's do a test. I brought back up the two (2) replicas in datacenter 1. Then I made a change in the AG proprieties. I set the 2nd replica in datacenter 1 to manual. So 1 replica is set to automatic failover and 1 replica is set to manual failover in datacenter 1. The replica in datacenter 2 is set to automatic failover I then take down both replicas in datacenter 1 again to simulate the "outage" and the replica in datacenter 2 comes online and the DB is moved. So is Copilot right? Can there only be two (2) replicas allowed to have/use automatic failover? I cannot find a definitive answer confirming this. Or is my configuration wrong/missing something and if it is, could you please point me in the right direction on how to get this resolved?
SQL Server 2005 (compatibility level 90)
Hello, I’m testing the behavior described in the SQL Server documentation for **compatibility level 90** regarding the special attributes `xsi:nil` and `xsi:type`: > “The special attributes `xsi:nil` and `xsi:type` can't be queried or modified by data manipulation language statements. This means that `/e/@xsi:nil` fails while `/e/@*` ignores the `xsi:nil` and `xsi:type` attributes. However, `/e` returns the `xsi:nil` and `xsi:type` attributes for consistency with `SELECT xmlCol`, even if `xsi:nil = "false"`. ” But on **SQL Server 2005**, I can successfully query `@xsi:nil` and it returns the expected value. I’m trying to reproduce the documented “`/e/@xsi:nil` fails” behavior, but I can’t. ### Environment - Product: **Microsoft SQL Server 2005** - Database compatibility level: **90** --- ## ✅ Repro script ```sql IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MyTestSchema') DROP XML SCHEMA COLLECTION MyTestSchema; GO CREATE XML SCHEMA COLLECTION MyTestSchema AS N' <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="root"> <xsd:complexType> <xsd:sequence> <xsd:element name="element" nillable="true" type="xsd:string" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>'; GO DECLARE @xmlData XML(MyTestSchema) = N' <root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <element xsi:nil="true" /> </root>'; ;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as xsi) SELECT @xmlData.query('<result> { /root/element/@xsi:nil } </result>') AS Typed_Result; ``` ### Actual result `Typed_Result` contains `xsi:nil="true"` under `<result>...`. ### Expected result (based on docs) I expected `/root/element/@xsi:nil` to fail, or not return `xsi:nil`. --- ## Questions 1. In the documentation, does “data manipulation language statements” mean only **XML DML** (i.e., `.modify()`), not XQuery used in `SELECT` with `.query()` / `.value()`? 2. Does the “`/e/@xsi:nil` fails” behavior apply only when the XML is stored in a **table column**, not when using an **XML variable**? 3. Is the behavior different between **typed XML** (with an XML schema collection) vs **untyped XML**? 4. Can someone provide a minimal reproduction in SQL Server 2005 where `/e/@xsi:nil` fails as described? Thank you. ---Compat level 90: XML string-to-datetime UDF
Hello, I’m testing a behavior described in SQL Server documentation for **database compatibility level 90**. The docs state that a user-defined function that converts an XML constant string value to a SQL Server date/time type is marked as **deterministic**. On **SQL Server 2005**, I’m seeing the opposite: the function is marked as **non-deterministic** (`IsDeterministic = 0`). I’m trying to understand whether I’m missing a requirement/constraint or whether this is a doc mismatch / version-specific behavior. ### Environment - Product: **Microsoft SQL Server 2005** - Database compatibility level: **90** --- ## ✅ Repro script ```sql IF OBJECT_ID('dbo.fn_ParamXmlToDatetime', 'FN') IS NOT NULL DROP FUNCTION dbo.fn_ParamXmlToDatetime; GO CREATE FUNCTION dbo.fn_ParamXmlToDatetime (@xml XML) RETURNS DATETIME WITH SCHEMABINDING AS BEGIN DECLARE @y DATETIME; -- Convert an XML value to DATETIME SET @y = CONVERT(DATETIME, @xml.value('(/r)[1]', 'datetime')); RETURN @y; END GO SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fn_ParamXmlToDatetime'), 'IsDeterministic') AS IsDeterministic, OBJECTPROPERTY(OBJECT_ID('dbo.fn_ParamXmlToDatetime'), 'IsPrecise') AS IsPrecise; GO ``` ### Actual result `IsDeterministic = 0` (non-deterministic) ### Expected result (based on docs) `IsDeterministic = 1` (deterministic) for this pattern under compat level 90. --- ## Questions 1. Are there additional conditions required for SQL Server to mark this UDF as deterministic (for example, specific XQuery usage, avoiding `CONVERT`, using `CAST`, using `datetime2` doesn’t exist in 2005, etc.)? 2. Does the determinism rule apply only when converting from an **XML literal constant** inside the function, rather than an XML parameter value? 3. Is this behavior different for **typed XML** (XML schema collections) vs **untyped XML**? 4. Is this a known difference/bug in SQL Server 2005 where the UDF is functionally deterministic but still reported as non-deterministic by `OBJECTPROPERTY`? Thank you for any clarification. ---Assess and upgrade to SQL Server 2025 with SSMS Migration Component
Upgrade to SQL Server 2025 with confidence. Starting with SSMS 22, the Hybrid & Migration component now includes upgrade assessment for SQL Server 2025. This feature enables you to quickly evaluate readiness for upgrade. SSMS also provides a streamlined migration path to the instance of higher version if in-place upgrade is not preferred.Lesson Learned #529: Troubleshooting Application Slowness Using SSMS Copilot
Some days ago, I worked on a support case where a customer reported application slowness affecting multiple users. Instead of jumping into traces or manually writing diagnostic queries, we used SSMS Copilot to investigate the issue. I would like to share with you how we diagnosed and understood the root cause. To illustrate the case, let’s walk through a simplified example: we create a new table, and right after that, we add a new column to it. CREATE TABLE Ejemplo2 (ID INT) BEGIN TRANSACTION ALTER TABLE dbo.eJEMPLO2 ADD NuevoCampo INT NULL Using SQL Server Management Studio and Copilot we executed the following prompt: Please, provide all currently running or suspended sessions. Include session ID, status, command, wait type (if any), application_name, wait time, and current SQL text. We got the following results: I executed multiple times the same prompt and always the session ID 67 is in suspended mode and Wait_type LCK_M_SCH_S, for this reason, I run a new prompt: Please, provide all sessions that are currently blocked by another session. Include session ID, the blocking session ID, wait type, and the blocked SQL text . At the end, I found that the session 51 is blocking the session ID 67 and for this reason, I run a new prompt: do we any have active transaction pending for commit for the session ID 51. So, I understand that the Session ID 51 has a transaction open, so, let's ask the details of the session 51, with a new prompt: Please, show the most recent SQL statement executed by session ID 51, even if the session is currently sleeping or not running any active request. Include the session status and login name as well. Use sys.dm_exec_connections and most_recent_sql_handle to retrieve the query text if necessary. Well, we identified the problem, the session ID 67 is running a SELECT * FROM sys.Ejemplo2 but it's beging blocked by the session 51. Session ID 51 hasn’t finished its transaction, and now we either need to commit, rollback, or kill that session, especially if we don’t have access to the application that owns it. Before resolving the issue, I asked Copilot an additional prompt: Please, explain why session ID 67 is currently waiting. Include the wait type, and explanation of that, the resource being waited on, how long it has been waiting (seconds), and the SQL text. Also identify if another session is blocking it. The name of the object and schema Please, provide recommendations to prevent or reduce this kind of blocking situation in the future, based on the current wait type and blocking scenario observed with session ID 67. Please, summarize all current blocking chains in the system. Include blocking session IDs, blocked session IDs, wait types, wait durations, login names, and SQL statements involved.Lesson Learned #530: Comparing Execution Plans to Expose a Hidden Performance Anti-Pattern
One of the most powerful features of SSMS Copilot is how it lets you compare execution plans and immediately show you performance issues. In this case, I would like to share with you my lesson learned comparing two queries and how they behave very differently inside the engine. We have the following queries, these are using a table _x_y_z_MS_HighCPU that contains 4 millon of rows. The column TextToSearch is a varchar(200) datatype. -- Query 1 SELECT COUNT(*) FROM [MSxyzTest].[_x_y_z_MS_HighCPU] WHERE TextToSearch = N'Value: 9'; -- Query 2 SELECT COUNT(*) FROM [MSxyzTest].[_x_y_z_MS_HighCPU] WHERE TextToSearch = 'Value: 9'; Since the query texts are different, each will have a different query ID in Query Store. By running the following T-SQL, for example, I can identify the query IDs. SELECT qsqt.query_sql_text, qsq.query_id, qsp.plan_id, qsp.query_plan_hash, qsp.last_execution_time FROM sys.query_store_query_text qsqt JOIN sys.query_store_query qsq ON qsqt.query_text_id = qsq.query_text_id JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id WHERE qsqt.query_sql_text LIKE '%SELECT COUNT(*)%' -- FROM [[MSxyzTest]].[[_x_y_z_MS_HighCPU]]%' ORDER BY qsp.last_execution_time DESC; Queries 1 and 2 can be compared directly. Using Copilot, I ran the following prompt: Compare the execution plans for the two queries (query id 1 and query id 2 using Query Store. Highlight any differences in operators, estimated vs actual row counts, or implicit conversions. Running the following prompt : CPU Usage: Please, show the top resource-consuming queries in the current database using Query Store data. Include query text, execution count, duration, CPU time, and logical reads. We could see the impact of using an antipattern:Lesson Learned #525: Tracking Command Timeouts in Azure SQL: Beyond Query Store with Extended Events
A few days ago, we were working on a support case where our customer was intermittently experiencing command timeouts. What made the case interesting was that queries which usually completed in under one second suddenly started taking more than 10 seconds to execute. Since the application — developed in Python using the ODBC Driver 18 for SQL Server — had a command timeout set to 5 seconds, the following error was triggered every time the threshold was exceeded: Error executing command, retrying in 5 seconds. Attempt 1 of 3 with new timeout 5. Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)') The application had built-in retry logic, dynamically increasing the timeout in each of the three retry attempts, to allow time for the query to complete and to log enough data for post-error analysis. Example logs from the retry logic: (RunCommandTimeout) - Thread: 39808 - Error executing command, retrying in 5 seconds. Attempt 1 of 3 with new timeout 5. Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)') INFO:root:Connecting to the DB jmjuradotestdb1 - Thread id 39808 - (Attempt 1/3) INFO:root:Connected to the Database in jmjuradotestdb1 - Thread id 39808 - 0.0445 seconds --- (RunCommandTimeout) - Thread: 39808 - Error executing command, retrying in 9 seconds. Attempt 2 of 3 with new timeout 9. Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)') INFO:root:Connecting to the DB jmjuradotestdb1 - Thread id 39808 - (Attempt 1/3) INFO:root:Connected to the Database in jmjuradotestdb1 - Thread id 39808 - 0.0532 seconds --- (RunCommandTimeout) - Thread: 39808 - Error executing command, retrying in 13 seconds. Attempt 3 of 3 with new timeout 13. Error: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Query timeout expired (0) (SQLExecDirectW)') (RunCommandTimeout) - Thread: 39808 - Loop:2/5 Execution Time: 9.7537 seconds My first prompt using SSMS Copilot was this "Review the queries that experienced a command timeout or were aborted in the last 30 minutes. Include query text, queryid, duration, and the reason and code for the abort if available." and I got the following results. So, all points that the query 216 got command timeouts. My next question, was, for query ID 216, show the number of total executions reporting that is 28 executions. The response showed 28 executions, but this number didn’t match the number of aborted and non-aborted executions observed in the application logs, why this difference? Checking the table sys.query_store_runtime_stats I found 10 rows all having execution_type = 3, and total executions 28. So, that's mean that Query Store aggregates query execution data over a fixed interval. So, the execution_type is an indicator that at least an execution during this runtime interval was aborted. So, at least several of them were aborted and other not. To obtain a more granular and accurate picture, I created an Extended Events session to capture these events using ring_buffer target. CREATE EVENT SESSION [CommandAborted] ON DATABASE ADD EVENT sqlserver.attention( ACTION ( sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username, sqlserver.database_name, sqlserver.sql_text ) ) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS); GO ALTER EVENT SESSION [CommandAborted] ON DATABASE STATE = START; after reproducing the command timeout scenario again, I was able to see only the aborted executions. So, in this case, 28 executions were executed and 7 executions were aborted. WITH RingBufferXML AS ( SELECT CAST(t.target_data AS XML) AS target_data FROM sys.dm_xe_database_session_targets t JOIN sys.dm_xe_database_sessions s ON t.event_session_address = s.address WHERE t.target_name = 'ring_buffer' AND s.name = 'CommandAborted' ) SELECT x.value('@name', 'varchar(50)') AS event_name, x.value('@timestamp', 'datetime2') AS event_time, x.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS client_app_name, x.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text, x.value('(data[@name="duration"]/value)[1]', 'bigint') AS duration_microseconds, CAST(x.value('(data[@name="duration"]/value)[1]', 'bigint') / 1000000.0 AS decimal(10,3)) AS duration_seconds FROM RingBufferXML CROSS APPLY target_data.nodes('//event') AS tab(x) WHERE x.value('@name', 'varchar(50)') = 'attention' and x.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') = 'TEST-DataCon' ORDER BY event_time DESC;
Uncovering Hidden Bottlenecks in SQL Server Execution Plans
As someone learning SQL Server, I'm trying to deepen my understanding of execution plans and how SQL Server processes queries. This seems like a crucial topic for writing efficient and optimized SQL. Here are some points I’m curious about and would love to discuss: 1. Reading Execution Plans: - How do I interpret the graphical execution plans in SQL Server Management Studio (SSMS)? - What are the key operators I should focus on? 2. Query Optimization: - What common issues can I identify in an execution plan that indicate a poorly performing query? - Are there specific cases where SQL Server's query optimizer might make suboptimal decisions? 3. Indexes and Their Impact: - How do indexes influence execution plans? - What are the best practices for creating and maintaining indexes to improve performance? 4. Real-World Examples: - Are there any real-world scenarios or examples of optimizing queries based on execution plans? - What were the before-and-after results? 5. Tools and Resources: - Beyond SSMS, are there other tools or resources (e.g., books, blogs, videos) to better understand execution plans? I’d love to hear from the community about your experiences, tips, and insights regarding execution plans and query processing in SQL Server. All perspectives, whether from beginners or those with more experience, are welcome and can make this discussion valuable for everyone involved! Let’s dive into it!
