SQL SERVER How to deleting big volume of data from very huge table

Issue

We one of very huge database which running out of server disk space. I was asked to remove millions of historical data from some huge table. When I try to run DELETE with where condition directly, I got two problems. Firstly, whole table blocked for long time (long then 12 hours). Secondly, after run DELETE the ldf Database log file increased exponentially and the script ending with error because disk is full.

Solution

Base on the server’s situation, we may using different way to solve this issue.

1. We just want to Deleting All the data from the table then the best option will be Truncate table directly, as below:

TRUNCATE TABLE huge_history_table


This TRUNCATE TABLE command will remove all rows from a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

2. We want to deleting more than 80-90 Percent of the data from the huge table and this table can be offline for short period. We can using SELECT INTO script to move the data (data need to keep) to another staging table. Then Truncate this Large table and Insert back the staging data. as below:

SELECT * INTO staging_table
FROM huge_table
WHERE create_date>’DateAdd(month, -1, Convert(datetime, GetDate()))’


TRUNCATE TABLE huge_table


INSERT INTO huge_table (column_name)
SELECT column_name
FROM staging_table

3. One last option I can think of is to change your database’s Recovery Mode to SIMPLE and then delete rows in smaller batches using a while loop something like this. We want to delete large historical data from the table, the table need to be accessed by system 24×7 and cannot find a time take if offline. Then we delete rows in smaller batches using a while loop something as below:

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
BEGIN
— Delete some small number of rows at a time
DELETE TOP (10000) LargeTable
WHERE create_date<‘DateAdd(month, -1, Convert(datetime, GetDate()))’

SET @Deleted_Rows = @@ROWCOUNT;
END

RDP unable to connect Azure server

This week I have been having trouble connecting to my Azure server via RDP. I keep getting below errors:

-The number of connections to this computer is limited and all connections are in use right now.

I can connect to server if I changed inbound security rule to allowed my IP address only. But I cannot use white IP list inbound rule as I don’t have static IP.

Then I did some deep investigation and try to tracking failed logon information from system event.

I checked Event Viewer -> Windows Logs -> Security there are lots of Audit Failure event looks like below:

But as you see, it is completely useless. I only can guess their are some attackers tried to logon with a username of Administrator and the Logon Type is set to 3 (generic network logon), and there is no Source Network Address recorded.

But in Event Viewer -> Applications and Services Logs -> Microsoft -> Windows -> RemoteDesktopServices-RDPCoreTS I found lots of below warnings:

In Windows Server 2012 and later version, if an attacker attempts to logon but fails to do so AND uses a username that DOES NOT EXIST on the targeted RDS host or domain that the host is a member of, Event ID 140 is logged, showing you the source IP of the attacker.

I added this IP to Azure blocked IP list then issue is fixed.

IIS Granting folder permissions to Website

Whenever a new website created, IIS will create an application pool that has the same name as website. For example, if you create an website with the name “fictionlib,” an application pool with the name ” fictionlib ” is created. Each application pool has a spcial a security identifier in Windows, system files and folders can be secured by using this identity. However, the identity is not a real user account and will not show up as a user in the Windows User Management Console.

To config, website folder permission, you need to follow below steps:

  1. Open Windows Explorer
  2. Select Website folder
  3. Right click the directory and select Properties
  4. Select the Security tab
  5. Click the Edit button and then Add button
  6. Click the Locations button and make sure that you select your computer.
  7. Enter IIS AppPool\ (eg: IIS AppPool\website name) in the Enter the object names to select: text box
  8. Click the Check Names button and click OK.
  9. Check Modify under the Allow column, and click OK, and OK.

How to install an SSL certificate on Microsoft IIS 10

Before install the SSL certificate, you need to know which kind of certificate you have.

If you certificate file is .cer file then just simply install it via IIS manager. But if have .key file and .crt file then you need to convert the certificate to .pfx file.

Installing certificate via IIS manager

  • go to the Start menu, choose Administrative Tools and select Internet Information Services (IIS) Manager. Otherwise, access it via Win+R >> inetmgr >> OK.
  • Click on the required server name and go to the Server Certificates option in the center menu.

Press the Complete Certificate Request button in the Actions right-side section.

Following the wizard to finish the import step by step. Once imported certificate is shown in the list of Server Certificates. Then you can assign the certificate to the website.

  • pand the Sites menu and choose the website you want to assign the certificate to. After that, click on the Bindings option in the Actions section.
  • In the Site Bindings window, click Add.
  • In the Add Site Binding window, choose the following parameters:

Type – https;

IP address – All Unassigned, or your IP address;

Port – 443;

SSL certificate – friendly name of the imported certificate.

After all details are selected, click OK button.

  • The new binding has been successfully created.

If the site already has https enabled, and if you want to update the SSL certificate, you will need to choose the Edit button in binding for port 443, select a friendly name for the new certificate from the dropdown list and click OK to apply the changes.



Converting to PFX file

If you have the private key in PEM format (.key file), you need to generate the certificate in PKCS#12 format (.pfx).

Use this tool to generate the certificate in PKCS#12. Use your certificate with .crt extension, CA bundle with .ca-bundle extension and the saved key with .key extension.

If there’s an OpenSSL client installed on the server, you can create PFX file out of a certificate in PEM format (.pem, .crt, .cer) or PKCS#7/P7B format (.p7b, .p7c) and the private key using the following commands.

PEM (.pem, .crt, .cer) to PFX

openssl pkcs12 -export -out certificate.pfx -inkey privatekey.key -in certificate.crt -certfile more.crt

*where “more.crt” is the name of the CA Bundle file

How to Setup WordPress Custom Permalinks on Windows IIS

This blog will show how to config WordPress Custom URL Structure under Windows and IIS is how to handle URL Rewrites

Log in to the Admin section of your WordPress

Click on Settings -> Permalinks

Choose URL structure or enter a custom structure

Save below web.config file into WordPress root folder can allow permalinks (or “pretty URLs”) on Windows IIS.

<?xml version="1.0" encoding="UTF-8"?>

<configuration>
  <system.webServer>
    <rewrite>
      <rules>
        <rule name="wordpress" stopProcessing="true">
          <match url="." />
        <conditions>
            <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true"/>
            <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true"/>
        </conditions>
        <action type="Rewrite" url="/index.php"/>
        </rule>
      </rules>
    </rewrite>
  </system.webServer>
</configuration>

Once web.config file created, ‘Pretty URL’ will working properly, but if you have images or other kind of static files in the website, you need to add another rewrite rule to stop above rewrite:


<rule name="Redirect Image to HTTP" stopProcessing="true">
<match url=".*\.(gif|jpg|jpeg|png|css|js)$" ignoreCase="true"/>
<action type="Rewrite" url="{R:0}"/>
</rule>