Configure SQL Server 2017 on Linux with mssql-conf, the Linux way

The configuration of any system plays a vital role in its working efficiently. However, configuring a system needs not necessarily be a daunting task. There are several tools that help with the process.

SQL Server on Linux provides a wide range of options for configuration, management, and administration.

  1. mssql-conf
  2. Transact-SQL
  3. SQL Server Management Studio
  4. PowerShell

This article outlines the use of mssql-conf in conjunction with other available tools, to manage SQL Server.

On Windows, we are familiar with SQL Server Configuration Manager, the configuration tool used to manage and administer SQL Server services, configure the network protocols, and to manage network connectivity between the SQL Server and the client computers.

On Linux, we have a configuration tool known as mssql-conf. The primary function of this tool remains the same as that of the SQL Server Configuration Manager, but makes it easy for Oracle and Linux admins to work with SQL Server 2017 on Linux. I’d like to visit some of the key concepts of the Oracle parameter file (PFILE and SPFILE) here, with which the server configuration can be set/unset by adding/modifying entries in the configuration file.

Further reading…

mssql-conf

Happy learning!

 

 

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

An introduction to a SQL Server 2017 Graph database

What is a graph database?

A graph is composed of two elements: a NODE (vertices) and an EDGE (relationship). Each node represents entities, and the nodes are connected to one another with edges; these provide details on the relationship between two nodes with their own set of attributes and properties.

The graph database can be defined as the data structure representation of an entity modeled as graphs. It is derived from the graph theory. The data structures are the Node and the Edge. The attributes are the properties of the node or the edge. The relationship defines the interconnection between the nodes.

Relationships are prioritized in graph databases, unlike other databases. Therefore, no data inference using foreign keys or out-of-band processing is needed. We can build sophisticated data models simply by assembling abstractions of nodes and edges into a structure. Given the priority for relationships over data, the development stack receives the biggest value here.

In today’s world, relationship modeling requires more sophisticated techniques. SQL Server 2017 offers graph database capabilities to model relationships. Graph DB has nodes and edges—two new table types NODE and EDGE. And a new TSQL function called MATCH(). The Node and Edge (relationships) represent entities of the graph database. And since this capability is built into SQL Server 2017, already-existing databases don’t have to be ported to another system, so to speak.

Further reading…

Graph database

Stay tuned for more updates on this topic….

Posted in Graph database, SQL 2017, Uncategorized | Tagged , , , | Leave a comment

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!

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