SQL Server 2017 : Data Transformation and Interpolation using Python

As a continuation to my previous article, How to use Python in SQL Server 2017 to obtain advanced data analytics, a little bit of curiosity about Deep Learning with Python integration in SQL Server led me to write this latest article.

With Python running within SQL Server, you can bring the existing data and the code together. Data is accessible directly, so there’s no need to extract query data sets, moving data from storage to the application. It’s a useful approach, especially considering issues of data sovereignty and compliance, since the code runs within the SQL Server security boundaries, triggered by a single call from T-SQL stored procedures.

Continue reading…

Data Interpolation and Transformation using Python in SQL Server 2017

See Also,

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Thanks for reading my space!

Advertisements
Posted in SQL 2017, SQL and Python | Tagged , , , , | Leave a comment

SQL Server Log Shipping on Linux

Log shipping is a high-availability configuration that perhaps most of us are familiar with. It’s one of the oldest techniques wherein we ship transaction logs from a Primary database to a Secondary database. Log Shipping is still a vital feature used in case of applications that use warm standby for Disaster Recovery. We can see many articles which discuss the process of configuring Log shipping using T-SQL or SSMS.

On a high level, Log Shipping consists of preparing the secondary database (NORECOVERY or STANDBY) using a backup of the primary instance database and then applying the transaction logs from the primary database onto the secondary database using SQL Server Agent Jobs and a shared file location. In other words, it’s an automated process of restoring transaction log backups from the primary database server on to the secondary database/database server. This way, the database is available for failover.

We’ve seen multiple models of implementation of log shipping: log shipping on the same server, on to a different server, across domains or workgroups, or two different versions of SQL Server. SQL Server, now being available for Linux, makes us think, “Can we set up log shipping across platforms?”

As it turns out, yes, we can. This article discusses the setup. We’ll use a network file share for the demonstration. Let’s jump right in!

Continue reading….

Log shipping on Linux

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Thanks for reading my space!

Posted in Logshipping on Linux, SQL 2017 | Tagged , , , , | Leave a comment

An overview of Python vs PowerShell for SQL Server Database Administration

Today, Microsoft claims that Linux runs like a First-Class citizen on Azure, .NET Core has been open-sourced, and has been ported over to Linux, taking PowerShell along. PowerShell runs really well on Ubuntu, CentOS, RedHat Linux, and even Mac OS X. There are Alpha builds available for a few other platforms as well, all available for exploitation under the MIT License on GitHub. “Manage anything, anywhere” is what Microsoft is offering to its customers. Keeping with that, we now have:

All that is to say, you can perform the same actions or achieve the same level of automation, using either language: PowerShell or Python.

Python is better as a general-purpose programming/scripting language. If you also have Linux or other non-Windows boxes to administer, you may also probably prefer Python, even though Microsoft is pushing and assisting third parties in developing PowerShell modules to cover wide areas of technologies. If you are a Windows admin, PowerShell is something you’d probably like more, simply because of its deep integration with Microsoft products.

In this article, we’ll describe and discuss both technologies and you can see for yourself, which you may choose.

In my opinion, PowerShell or Python should now become PowerShell and Python.

Continue reading…

An overview of Python vs PowerShell for SQL Server Database Administration

What’s your opinion?

What programming language do you recommend to sysadmins and new IT pros? What resources do you think would be most helpful when learning a new coding language? Let’s have a discussion in the comments below!

Thanks for reading my space!

 

Posted in PowerShell, Python, SQL 2017 on Linux | Tagged , , | Leave a comment

Dynamic Data Masking – Tracking all masked columns in your database

dotnetvibes

I have presented on Making Developers lives easier with SQL Server 2016 multiple times over the last couple of months, and I must admit that even though the content is the same – every presentation is different depending upon the attendees and their expertise levels. During theGulf Coast Code Camp, MobileandDevSpace Conference, Huntsvillethere were some interesting questions I was asked on Dynamic Data Masking during/after my presentation. I thought of writing a quick blog post to answer those questions. Honestly this is something which I plan to start doing more often – since every time I give a presentation there are few questions which stand out and and some times I have to come back and do more research to find the right answer. Well it is never too late to make a start!

Dynamic Data Masking in SQL Server 2016 is a new built-in…

View original post 185 more words

Posted in Uncategorized | Leave a comment

Why would a SQL Server DBA be interested in Python?

If we follow blogs and publications on the technological advancement with respect to SQL, we notice the increase in the number of references to Python, of late. Often, that makes us think:

  • Why so much emphasis on Python these days?
  • Isn’t knowing PowerShell scripting sufficient for the automation requirements of today?
  • Is it the time DBAs started learning a programming language such as Python in order to handle their day-to-day tasks more efficiently?
  • Why do so many job postings these days include “knowledge of scripting” as a requirement?
  • Is all of this happening because the paradigm is shifting? Can’t the current Microsoft-specific languages such as PowerShell handle the shift?

When SQL 2017 was released, it made database administrators raise their eyebrows about two things:

  • SQL Server became a cross-platform product
  • SQL Server started supporting the enrichment of Machine Learning capabilities

While TSQL, as well as PowerShell cmdlets, are flexible enough to make database activities smoother, making the platform a versatile one, the growing importance of SQL, and the product opening up to Linux enabled more administrators to start looking into what SQL can offer.

Python is a versatile language, when it comes to working with analytical tools, and is considered one of the best available languages in the context. Python is, in fact, fully capable of interacting with huge volumes of data, handling complex mathematics and data manipulation/cleaning.

“OK, so Python is one of the favorite languages used by Linux admins. But hasn’t PowerShell been open-sourced under the MIT License and made available for Linux as well? Has it not already help with using SQL on Linux? Why add support for Python as well? How are we to get started there?”

As it turns out, Python isn’t difficult to learn. Also, learning Python is another arrow added to the quiver. Why not have the additional capabilities, keeping with the spirit of openness? Let’s get started and see how some of our regular tasks can be implemented using Python

Continue reading…

Why would a SQL Server DBA be interested in Python?

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Thanks for reading my space!

Posted in Python, SQL 2017, SQL 2017 on Linux | Tagged , , , | Leave a comment

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.

Data Flow Diagram

Let’s look at the high-level view of this implementation:

  1. A dedicated SQL Instance has the SQL Server DB Mail configured
  2. In the dedicated instance, a SQL Server agent job is used to schedule SQL Jobs
  3. It contains the stored procedure to perform XML data transformation. Also, it takes care of sending the email to intended recipients, using SMTP
  4. The SQL file that contains the code to be executed on the listed SQL instance is placed in a shared path. The path should be accessible from all the SQL instances.

Further reading

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Thanks for reading my space!

Posted in SQL, T-SQL, XML | Tagged , , , | Leave a comment

The evolution of SQL Server towards Digital Transformation challenges

SQL Server on Linux boosts the database market for Microsoft. Support of PolyBase (a feature to work with Big Data providers), In-Memory Optimized SQL Server, Real-time Operational Analytics, the scaling of Python and R Services for Data analytics, Graph database for NoSQL data, JSON support for transparent data interchange format between traditional and non-traditional database systems, Azure Cosmos DB from Document database to distribution database… All this helps leverage SQL Server to almost every extent, in day-to-day activities.

The shift in technology is being driven by increased expectations. The time-to-market is lower when it comes to applications; the competition is fierce. Also, there’s a lot of unstructured data floating in the ether, such as videos, images, audio, etc., which are more prevalent and problematic for traditional databases. And SQL Server 2017 has emerged, attempting to answer these calls.

It does, though, seem to have the potential to be seen as a powerhouse of a number of desirable features. It is a little early to say whether SQL Server 2017 would become an answer to the myriad of requirements we have; it may also require a lot of fine-tuning and improvisation. But it is perhaps safe to say that Microsoft does seem to be taking it seriously and taking the necessary steps.

Evolution of SQL Server towards Digital Transformation

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Thanks for reading my space!

Posted in SQL 2016, SQL 2017 on Linux | Tagged , | Leave a comment

How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL

In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.

One way of inching towards the more secure transmission of data is to enable Always Encrypted on the database. We’ll look into the various options we have, including enabling this at granular levels; we’ll look at enabling this at the column level.

The Always Encrypted feature was available only in the Enterprise and Developer editions of SQL Server 2016. Later, this feature was made available on all editions, with SQL Server 2016 SP1. Always Encrypted has the ability to encrypt data even at the column level.

There are several ways to configure the Always Encrypted feature:

  1. Using the Always Encrypted wizard
  2. Configuring AE using SSMS
  3. Create Master Key and Encryption Key using T-SQL and enabling encryption
  4. Configuring Always Encrypted using PowerShell

How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Thanks for reading my space!

Posted in SQL 2016 | Tagged , | Leave a comment

Recover Deleted Data from SQL Table Using Transaction Log and LSNs

Introduction

At times a user may perform UPDATE operation or DELETE operation in SQL server database without applying the WHERE condition. This is a very common reason for encountering loss of data from SQL Server Tables. As the SQL Server database is a highly popular relational DBMS among corporate sectors and businesses, the data loss problem magnifies even more. So users should be aware of the methods to recover deleted data from SQL Server Table in case of any mishaps.

Deleted rows can be recovered if the time of their deletion is known. This can be done through the use of Log Sequence Numbers(LSNs). LSN is a unique identifier given to every record present in the SQL Server transaction log. The upcoming section will discuss the process to recover deleted SQL Server data and tables with the help of Transaction log and LSNs.

Recommended: For avoiding the long and erroneous manual LSN approach to recover deleted records from SQL table make use of an advanced third-party software such as the SQL Database Repair.

Recover Deleted Data from SQL Server Table By Transaction Logs

There are some prerequisites to be fulfilled before we start the process to recover deleted data from SQL table. To easily recover deleted rows from a table in SQL Server database, it must have the BULK-LOGGED or FULL Recovery Model at the time when the deletion first occurred. Some quick actions are required so that the logs are still available to perform the recovery of data.

Follow the steps given below to recover deleted data from SQL Server 2005, 2008, 2012, 2014, and 2016 by the use of Transaction logs

Step 1: Check the number of rows present in the Table from which the data has been accidentally deleted using the below-mentioned query

SELECT * FROM Table_name

Step 2: Take the transaction log backup of the database using the query given below

USE Databasename
GO
BACKUP LOG [Databasename]
TO DISK = N'D:\Databasename\RDDTrLog.trn'
WITH NOFORMAT, NOINIT,
NAME = N'Databasename-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 3: In order to recover deleted data from SQL Server Table, we need to collect some information about the deleted rows. Run the query given below to achieve this purpose

USE Databasename
GO
Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation  = 'LOP_DELETE_ROWS'

From the query given above, we will obtain Transaction ID(Let’s say 000:000001f3) of the deleted rows. Now the time when these rows were deleted is to be determined using this ID.

Step 4: In this step, we will find the specific time at which rows were deleted using the transaction ID 000:000001f3. This is done by executing the query given as follows

USE Databasename
GO
SELECT
[Current LSN],  Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f3'
AND
[Operation] = 'LOP_BEGIN_XACT'

On executing this query we will get the value of current Log Sequence Number(LSN) (Let’s say 00000020:000001d0:0001)

Step 5: Now we will start the restore process to recover deleted data from SQL Server table rows that was lost. This is done using the below query

USE Databasename
GO
RESTORE DATABASE Databasename_COPY FROM
DISK = 'D:\Databasename\RDDFull.bak'
WITH
MOVE 'Databasename' TO 'D:\RecoverDB\Databasename.mdf',
MOVE 'Databasename_log' TO 'D:\RecoverDB\Databasename_log.ldf',
REPLACE, NORECOVERY;
GO

Step 6: Now apply transaction log to restore deleted rows by using LSN “00000020:000001d0:0001”

USE  Databasename
GO
RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\RDOTrLog.trn' WITH STOPBEFOREMARK = ‘lsn:0x00000020:000001d0:0001'  Note: Since LSN values are in Hexadecimal form and for restoring tables using this LSN, we need to convert it into decimal form. For this purpose, we add 0x just before the LSN as shown above.

Step 7: The process to recover deleted records from SQL table will get completed successfully. Now check whether the deleted records are back in the database named ‘Databasename_Copy’.

USE  Databasename_Copy
GO
Select * from Table_name

Disadvantages of Transaction Log Approach

  • Highly time-consuming method to recover deleted data from SQL Server table as it involves several lengthy queries to be executed
  • Extremely complex to implement for users, not possessing adequate technical knowledge
  • Greater chances of losing data due to errors while application and execution of queries
Conclusion

Although the Log Sequence Number method can restore deleted records from SQL Tables, it is not a recommended option for users due to its complexity and tediousness. Instead, it is advised to use an automated solution to recover deleted data from SQL Server table.

 

Posted in SQL, SQL 2016, SQL Server 2017 | Tagged , , , | 1 Comment

SQL Server DB Migration – Cloning a database to another collation

Database migration is a vital task in any environment, complex or otherwise. Seamless migrations are the goal but the efforts required to ensure it are tremendous.

A global SQL Server Collation Sequence update is the answer to the puzzle, but it is a multi-step operation. The first step is to identify the case sensitivity of the collation assigned to the repository database. If you find that the repository database is not case sensitive, you must create a new empty (blank) case-sensitive database to migrate the data to. You must then import the data from the old case-insensitive repository database to the new empty case-sensitive database, and then establish the new case-sensitive database as the repository database.

further reading…

SQL-Server-db-migration-cloning-a-database-to-another-collation

Please share your thoughts in the comments section. I would love to hear and learn from you as well.

Thanks for reading my space!

 

Posted in SQL, T-SQL | Tagged , , | Leave a comment