SSMA dropping connection

by Contributed | Oct 20, 2020 | Technology

This article is contributed. See the original author and article here.

SSMA migration from MySQL to Azure SQL DB repeatedly dropping connection while data migration is in progress. 

Error: ‘The connection has been disabled‘.

Collect relevant logs

In this case the error message is not giving us enough details on the root cause, so further investigation is required. Start initial data collection from SSMA, by following the below steps.

1. On source

   – Collect the version details of the source database and the OS info. This is vital as there were numerous cases where we had started troubleshooting and then realized that we are on an unsupported build.

   – Collect the version details of the providers / drivers used to make connection to the source database.

2. On destination

   – Collect the version details of the SQL Server that we are trying to connect and the OS info.

   – Collect the version details of the providers / drivers used to make connection.

3. Check the Migration Engine used (Client side / Server side)

   – Navigate to Tools menu and go to Project Settings

   – Check the migration engine option from the Migration menu

4. Collect the SSMA logs after reproducing the issue, to get Log file path

   – Navigate to Tools menu and go to Global Settings

   – Select the Logging page and copy the Log file path under Misc category  

You are now ready to check the SSMA logs. Dropping connection with error ‘The connection has been disabled’ is often encountered with a connection timeout.

Mitigation

When migrating big tables, the workaround is to tune the default migration parameters in Project Settings, to avoid the operation timeout and to make sure the migration succeed.

1. Open SSMA for MySql -> Go to Tools -> Project Settings -> Select General from the left menu -> Select Migration -> In the Misc section -> Set data migration timeout to a higher value (i.e. 1000)
   
   
   
   


2. Open SSMA for MySql -> Go to Tools -> Project Settings -> Select General from the left menu -> Select Migration -> In the Parallel data migration section -> Set Parallel data migration mode from Auto to Custom and set Thread Count from 10, which is the default, to a lower value (i.e. 5)
   
   


3. Open SSMA for MySql -> Go to Tools -> Project Settings -> Select General from the left menu -> Select Migration -> Lower the value of BATCH_SIZE (i.e. 1000)
   
   
   

    

   
   


4. Run the data migration.

After the above steps, the migration should complete successfully.

For more details on SSMA tool, check SSMA reference documentation. 

SSIS package fails with 'Communication link failure'

by Contributed | Oct 20, 2020 | Technology

This article is contributed. See the original author and article here.

“Communication Link Failure” is often due to outdated drivers or poor network configuration settings.

In this scenario SSIS packages were used to load data from SQL on-premises to Azure SQL DB. After a while, the bulk insert fails with below errors:

Error: 2020-08-25 10:44:08.16
   Code: 0xC0202009
   Source: Extract Location Hierarchy Data to Datamart OLE DB Command [156]
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: “Microsoft OLE DB Driver for SQL Server”  Hresult: 0x80004005  Description: “Communication link failure”.
An OLE DB record is available.  Source: “Microsoft OLE DB Driver for SQL Server”  Hresult: 0x80004005  Description: “TCP Provider: An existing connection was forcibly closed by the remote host.

Error: 2020-08-25 10:44:08.23
   Code: 0xC0047022
   Source: Extract Location Hierarchy Data to Datamart SSIS.Pipeline
   Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component “OLE DB Command” (156) failed with error code 0xC0202009 while processing input “OLE DB Command Input” (161). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
End Error”

There are several options to be considered to address this issue.

1 – Disable TLS_DHE ciphers on client machine

Unless you have a specific requirement for a legacy application, disable all the TLS_DHE ciphers, as they are outdated and not recommended to be used.  

Option #1 – Follow the steps described in the documentation below:

Applications experience forcibly closed TLS connection errors when connecting SQL Servers in Windows

Option #2 – Alternatively, you can download IIS Crypto Tool from here and configure the best practices on your client and server. 

Steps to configure best practices:

1. Open IIS Crypto


2. Click on Best Practices


3. Go to Cipher Suits > Click on Best Practices Again


4. Click Apply


5. Restart the server (so that the changes are applied in the registries)

2 – Configure client machine 

Step #1 Ensure NIC Drivers are up to date

Step #2 Disable TCP chimney at NIC Level

TCP Chimney Offload is a feature that provides capability to offload TCP/IP packet processing from the processor to the network adapters and some other balancing options.  This has been known to cause issues on the SQL Server side causing network errors.  Further information on this can be found in the following blog post as well.

TCP Chimney Offload – Possible Performance and Concurrency Impacts to SQL Server Workloads 

 Make sure that under Advanced Settings of NIC below options are DISABLED:

• IPv4 Checksum Offload


• IPv4 Large Send Offload


• Checksum Offload


• Large Send Offload

Steps to disable TCP chimney features at NIC level:

1. Click Start -> Run -> type ncpa.cpl -> click OK


2. Right-click a network adapter object -> click Properties


3. Click Configure -> Advanced tab


4. In the Property list: click receive side scaling -> Disable in the Value list -> OK


5. In the Property list: click large send offload ->Disable in the Value list-> OK


6. In the Property list: click ipv4 checksum offload -> Disable in the Value list -> OK


7. In the Property list: click TCP checksum offload (IPv4) -> Disable in the Value list -> OK

The other properties to look for and disable are:

• Offload Receive IP Checksum


• Offload Receive TCP Checksum


• Offload TCP Segmentation


• Offload Transmit IP Checksum


• Offload Transmit TCP Checksum

Step #3 Configure TCP Chimney Offload, RSS and NetDMA in the operating system.

Open an elevated cmd and type below commands.

To determine the current status of TCP Chimney Offload and RSS:

netsh int tcp show global

To disable TCP Chimney Offload:  

netsh int tcp set global chimney=disabled 

 To disable RSS:

netsh int tcp set global rss=disabled

To disable NetDMA: 

netsh int tcp set global netdma=disabled 

Restart the machine.

 3 – Tune SSIS Package

Another recommendation to alleviate the connection issue is to actually tune your SSIS package to perform more efficiently. There’s lots of articles around this aspect, but the top settings are DefaultBufferMaxRows, DefaultBufferSize, and Rows per batch settings. However, this needs to be performed over many trials, while capturing logging data and adjusted accordingly. We usually see General Network Errors associated with poor performing packages, that degrade over time because of the data/transactions involved. 

This blog gives a quick rundown about tuning your SSIS package for a better performance.

Here’s our documentation on top 10 SSIS Best Practices which will also go over different settings you can test.

Logins in secondary server not mapped correctly from primary server

by Contributed | Oct 20, 2020 | Technology

This article is contributed. See the original author and article here.

Login error received after initiating failover between Azure SQL primary server and its geo-replicated secondary:

“The server principal ‘<username>’ is not able to access the database ‘<db_name>’ under the current security context. Cannot open database ‘<db_name>’ requested by the login. The login failed. Login failed for user ‘<username>'”

This typically means the logins in secondary server are not mapped correctly from primary server. Reasons could be either primary and secondary server SID mismatch or it could be a permissions issue, i.e. did not provide proper permissions to database in primary server.

Steps to handle

Verify and create proper login mapping between primary and secondary. The procedure to map logins is exemplified below. I’ve configured geo-replication for my P1 database Adventureworks and I’ve created testlogin.

Step #1

Run below T-SQL in master of primary server to identify the login and check the SID matching.

SELECT [name], [sid]
FROM [sys].[sql_logins]
WHERE [type_desc] = 'SQL_Login'

name             sid

testuser         0x010600000000006400000000000000004B1D98FA1758A440A6AC481292BFECAE

azure_test      0x0106000000000064000000000000000099514D212C5CC44AB7A

Step #2 

Create testuser for Adventureworks DB in primary server and provide data_reader permission. You can skip this step if you already have the same user with read-only permissions.

CREATE USER testuser 
FOR LOGIN testuser
EXEC sp_addrolemember 'db_datareader', 'testuser'

Run below T-SQL on Adventureworks DB to check SID value: 

SELECT [name], [sid]
FROM [sys].[database_principals]
WHERE [type_desc] = 'SQL_USER'

name             sid

dbo               0x010600000000016400000000000000002B84948B9D83A54182DE3A5602C009E3

testuser         0x010600000000006400000000000000004B1D98FA1758A440A6AC481292BFECAE

We can see testuser SID matches with logical master and primary database.

Step #3

If you have matching SID, execute below T-SQL to map the login in the configured geo-replicated database in secondary server. 

Login to secondary server and create login like below in master db:

CREATE LOGIN testuser1
WITH PASSWORD ='****',
SID = 0x010600000000006400000000000000004B1D98FA1758A440A6AC481292BFECAE

The connection to the database should now be successful.

SSMS test connection 1

For more details on how to configure logins and users for geo-replicated servers, please refer to the following article – Configure and manage Azure SQL Database security for geo-restore or failover .

The recommendation to overcome the incorrect mapping is to use contained users:

• With SQL Database, you can always create this type of user account.


• With SQL Managed Instance supporting Azure AD server principals, you can create user accounts to authenticate to the SQL Managed Instance without requiring database users to be created as a contained database user.

With this approach, the user authentication information is stored in each database, and replicated to geo-replicated databases automatically. However, if the same account exists in multiple databases and you are using Azure SQL Authentication, you must keep the passwords synchronized manually. Additionally, if a user has an account in different databases with different passwords, remembering those passwords can become a problem.

Note: To create contained users mapped to Azure AD identities, you must be logged in using an Azure AD account that is an administrator in the database in Azure SQL Database. In SQL Managed Instance, a SQL login with sysadmin permissions can also create an Azure AD login or user.

For more details on contained users, please check Authorize database access to SQL Database and Contained Database Users – Making Your Database Portable .

[Guest Blog] All Journeys Are Not The Same: Don’t Compare Yours

by Contributed | Oct 19, 2020 | Technology

This article is contributed. See the original author and article here.

This article is written by Business Applications MVP, Megan Walker, who shares challenges from her personal story that has impacted her career path today. 

Have you ever compared yourself with someone else? I mean, we all have, right? And we know it’s kind of pointless too. You see someone the same age as you, in the same industry, and perhaps doing the same job, and you compare. You wonder, how have they achieved all they appear to have accomplished? How do they fit in so much blogging/presenting/whatever? Unless you are close friends with someone, you often will not ever know their story.  Here is a small portion of mine.

Like so many women (although certainly not all), I assumed my life would go something like this. Meet someone, fall in love, get married, have some kids. It was not something I dreamed about or pined over, it was just there in the back of my mind as ‘the plan’. I had no time frame, no expectations about when these things would happen, but just figured they would. I was 23 when I met my now-husband, and I knew pretty much after a month that he was the one for me. Heck, I even moved to his homeland of the United States of America to start a new life with him. I was young and in love and it was exciting (note, I am still very much in love, just not so young!).

So, I was well ahead in this thing called life, but we wanted to have a marriage first, just be the two of us and were in no rush to have any mini versions of us for a while. For the first 6 or 7 years of that time, I worked in restaurants, which meant really long hours, early starts and late finishes. Realising it was not exactly great for a ‘family’ life, I worked my way OUT of hospitality and started working for a software company instead, which joyfully led me to the world of Microsoft Dynamics CRM.

Sadly, the joy of starting a family never happened. We had several miscarriages and many rounds of IVF. In the USA, it is also not typically covered by insurance and costs tens of thousands of dollars, which just kind of adds insult to injury. For anyone who has gone through this themselves, you will know and understand the true pain and heartbreak each time it does not work and are left with an emptiness that replaces the anticipation and hope felt previously. And to top it off, you paid for this too! It’s *%@*… Nothing else to say!

After many, many years of this, we made the difficult decision to stop the IVF treatments and decided to move to the UK. Back to my homeland, but a massive move for my American husband. After a few years back in England, I started a blog. I wanted to share things I was learning and still figuring out, and hoped that it might help the odd person. I was working for a Microsoft Partner, learning more and more, meeting people within the community, and starting to get a name out there. I loved it!

I have had many people comment, and exclaim, how do you find time to blog so much, and create so many videos, or travel (pre-COVID) to so many places. Honestly? When I realized I could not and would not be having any kids, I felt like I had lost my place in the world. I couldn’t see what value I could bring to others, plus, I could not see where I fit in. I don’t mean that having kids is the only purpose for someone, not at all. But that had been the path I thought I was on for so long. And when that path ends…. you feel kind of lost. Finding the Microsoft Dynamics & Power Platform Community felt like it gave me some kind of goal, to help others.

One perspective I have is that if I had children, I am not sure I would be here…. Being a Microsoft Business Applications MVP, travelling over the world to do presentations. Perhaps I would not be able to sit on a Saturday afternoon and write blog posts or record a video as I would be spending that time with them. I would be with my family and make sure they knew they were loved. I am happy for others who have managed to start their own families with kids, but having said that, the life that I now have is good and I’m grateful for it – I am married to my best friend (for over 20 years), and we are finally happy and at peace with what we have.

So yeah, I do a lot of community stuff. I create a bunch of content, and I really love doing it. But do not compare what I do with what you do when our lives are likely not the same. We all have our own journey and unique (sometimes unspoken) challenges – some are more painful and difficult to navigate than others, but we can all achieve our goals and find fulfillment when we focus. Sometimes those goals, objectives or the purpose we thought we once had can shift drastically – it’s important that we re-evaluate our lives to stay afloat, and find comfort in the things that we can be grateful for.

I hope this encourages you in your own journey as well, regardless of what struggles you are going through in your life. Your journey is your own – don’t compare yourself to others; you’ve got this!

#CareerJourneys

#HumansofIT