• DB에 Logging 되는 Data를 이용하여 Alarm을 생성하여 Email, Text알람전송. 

How to configure Database Mail in SQL Server

Database Mail, as you would expect from its name, is a solution for sending e-mail messages from the SQL Server Database Engine to users. Using Database Mail, database applications can send e-mail messages that can, for example, contain query results or simply alert a user about an event that occurred in the database.

The process of Database Mail configuration has three main steps. In order to complete this successfully, we need to:

• create a Database Mail account,
• create a Database Mail profile,
• and configure those two to work together

Configuring Database Mail

To create a Database Mail profile, we can use either the Database Mail Configuration Wizard or T-SQL code. Using Configuration Wizard is easier, but bear in mind that Database Mail is turned off in SQL Server Express editions.

Usually, all we need to do is go in Object Explorer, connect to the SQL Server instance we want to configure Database Mail on and expand the server tree. Then expand the Management node and double-click Database Mail or right-click and choose Configure Database Mail to open the Database Mail Configuration Wizard:

Since Microsoft SQL Server 2016 Express edition is used in this article as an example, the Management node does not have Database Mail:

This doesn’t mean we cannot use it because it’s only not available as an interface, but it’s still available in the SQL Server Database Engine itself. We just need to enable it using T-SQL.

To enable Database Mail, run the following code:

In this case, running the code triggered an error:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0]
The configuration option ‘Database Mail XPs’ does not exist, or it may be an advanced option.

This is going to happen from time to time because this is an advanced option. To fix this, we need to change the show advanced options default value from 0 to 1.

To do this run the following code:

This time, the query is executed successfully. Once these two options are changed from ‘0’ to ‘1’, Database Mail is turned on:

Now we can get back to setting up the e-mail profile and adding an e-mail account. To do this we will use some stored procedures in msdb database.

To create a new Database Mail profile named ‘Notifications’ we will use the sysmail_add_profile_sp stored procedure and the following code:

To grant permission for a database user or role to use this Database Mail profile, we will use the sysmail_add_principalprofile_sp stored procedure and the following code:

To create a new Database Mail account holding information about an SMTP account, we will use the sysmail_add_account_sp stored procedure and the following code:

To add the Database Mail account to the Database Mail profile, we will use the sysmail_add_profileaccount_sp stored procedure and the following code:

Execute the code from all stored procedures, and you should get the message that the whole code is executed successfully:

If for some reason, execution of the code above returns an error, use the following code to roll back the changes:

If anything goes wrong, executing the stored procedures individually could help in troubleshooting the issue. Just make sure to execute the ‘sysmail_add_profileaccount_sp’ stored procedure after the Database Account, and a Database Profile are created.

Test Database Mail configuration

Okay, now we’ve set up an email account, so what’s next? Well, let’s send a test e-mail and see if it works.

As we mentioned before, we could send an e-mail to alert a user about an event that occurred in the database and this is exactly what we are going to do later on using a simple DML trigger. For now, let’s just send an e-mail to the specified recipient using the sp_send_dbmail stored procedure.

The Results grid will show the message that the e-mail is queued and ID number:

The code above should send an e-mail using the profile that was previously created to a recipient specified @recipients argument. The subject and body are specified @body and @subject arguments.

Pretty simple, right? Well, not exactly. In this case and in most situations in the real world the mail will not be sent successfully even though every step during the configuration was successful.

Troubleshooting Database Mail

In this case, the e-mail message was successfully queued, but the message was not delivered.

First things first, check if Database Mail is enabled by executing the following code:

In the Results grid, make sure that the ‘run_value’ column for Database Mail XPs is set to 1:

To send e-mails, the user must also be a member of the DatabaseMailUserRole server role. Members of the sysadmin fixed server role and msdb db_owner role are members automatically. This can be easily checked by going to Security > Logins, right-click the current user and select Properties. In the Login Properties dialog click the ‘Server Roles’ page and make sure that the ‘sysadmin’ server role is checked:

The Database Mail system logs e-mail activity in the ‘msdb’ database. To view the error messages returned by Database Mail, execute the following code:

The statement will return logs and in a case like ours when e-mails are not delivered look for the errors under the ‘event_type’ column:

These logs have all sorts of useful information that could help in troubleshooting, and we are in particular interested in the ‘description’ column as it holds detailed information about the error and what went wrong.

The very first error logged says:

2 error 2017-11-13 00:18:27.800 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2017-11-13T00:18:27). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at). ) 12092 1 NULL 2017-11-13 00:18:27.800 sa

However, this information won’t be useful unless there is some documentation e.g. in the TechNet library. There is an article about Troubleshooting Database Mail that provides information for users to quickly isolate and correct common problems with Database Mail, but going through the documentation was not helpful in this case and it took a long time without success.

One other message that was logged of this type was:

28 error 2017-11-14 16:20:01.107 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 6 (2017-11-14T16:20:01). Exception Message: Cannot send mails to mail server. (Failure sending mail.). ) 14452 8 NULL 2017-11-14 16:20:01.107 sa

The problem, in general, is not with the Database Mail itself and its functionality. The error description messages are not predefined in the SQL Server Engine. This explains the missing Microsoft documentation. Those messages are usually authentication responses from SMTP servers that are required before allowing access to mail services.

To troubleshoot this further, we need to approach the problem from another perspective.

So far in troubleshooting, we have verified the following general steps:

• Database Mail is enabled
• The user is properly configured to send Database Mail
• Sending a test e-mail doesn’t work
• No TechNet library documentation could be found for the error messages logged

Another approach could be to setup an e-mail account used for sending outgoing notifications on another e-mail client like Microsoft Outlook. This should be fairly simple as both Gmail and MS Outlook are well-known services that should work together and there’s online documentation available on official websites.

To use the Gmail account with MS Outlook, we have to configure it to work with POP3 or IMAP.

Log onto your Gmail account at www.gmail.com and click the Settings in the top right corner:

Choose the Forwarding and POP/IMAP tab at the top of the page:

In the IMAP Access section, make sure that Enable IMAP option is selected and click the Configuration instructions link at the bottom:

This will open an instruction webpage on how to set up IMAP. Skip the 1st step as we already checked and verified that IMAP is turned on.

Fire up Outlook client to add the Gmail email account to it. Once Outlook is opened, click the File tab, and select Add Account:

In the Add Account dialog, select the Manual setup or additional server types option and click the Next button:

In the next step, choose the POP or IMAP option and click the Next button:

Use the information from the table below to set up Outlook client with the correct settings:

Under User Information section, make sure to enter a valid e-mail address. The e-mail address entered here will be automatically set as User Name for the Logon Information once the Account Type is selected from the drop-down box. Once finished, click the More Settings button on the right to configure additional settings before continuing further:

This will open the Internet e-mail settings dialog. Leave everything as it is under the General tab. Next, click the Outgoing Server tab, check the My outgoing server (SMTP) requires authentication check-box and select the Use same settings as my incoming mail server option:

Move on to the Advanced tab. Once again, use the information from the table above to set server port numbers. Set the incoming server (IMAP) to 993, and the outgoing server (SMTP) to 465. From the drop-down boxes select the SSL/TLS type of encrypted connection for both IMAP and SMTP servers. Leave the rest as it is and click the OK button to confirm settings:

Now it’s the time to test e-mail account settings to ensure that the entries are correct. Click the Next button and this will pop-up two additional dialogs. In the first dialog, Outlook will perform two tasks:

1. Log onto incoming mail server (IMAP)
2. Send test e-mail message

This step is crucial as this task previously failed for Database Mail. In the second dialog, Outlook will ask for the username and password. This was already configured, optionally check the Save this password in your password lint option if your Windows user account is password-protected, and no other person has access to it:

Note: The credential dialog can pop-up more than one time. Just click the OK button each time, until the server stops asking for login information. This usually happens when something is not configured right e.g. username or password, port numbers, etc.

In this case, the first task failed and sending test e-mail message failed as well right afterward:

Some applications and devices use less secure sign-in technology, which makes their respective accounts more vulnerable and Gmail will block these sign-in requests. By default, access for these applications is turned off, but we can also turn access on to use them despite the risks. In this case, we also have to.

This can be solved by logging into Gmail account and making sure that Less secure apps option is enabled for the desired account. Go to the Less secure apps section of the Gmail account and enable access for less secure applications.

Note: This setting is not available for accounts with 2-Step Verification enabled. Such accounts require an application-specific password for less secure apps access.

Since this account has 2-Step Verification enabled, we have to turn it off, so we can continue further.

Go to ’Sign in & security’ section of My Account, select 2-Step Verification, sign in using your credentials, and enter verification code if asked received via text message with a 6-digit verification code or phone call, and click the Turn off button:

A pop-up dialog will appear informing the user about security and vulnerability. We are aware of this, so just click the Turn off button to continue:

Now we can go back to the Less secure apps section and allow access for less secure applications:

Back to Outlook, click the Next button once again to test e-mail account settings and finally a message without errors. Both tasks completed successfully:

The test e-mail message is also delivered successfully which means that sending another test e-mail from Database Mail should work this time:

There is only one way to find out. Go back to SSMS and execute the code for sending the test e-mail:

Unfortunately, even after all this trouble, the test e-mail is not getting through. A quick look at the log files shows another error message of another type:

51 error 2017-11-18 16:21:22.803 The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 10 (2017-11-18T16:21:22). Exception Message: Cannot send mails to the mail server. (Failure sending mail.). ) 3564 14 NULL 2017-11-18 16:21:22.803 sa

At this point, let’s do a quick look up, just to make sure that the e-mail profile is configured the same as it is for Outlook. Execute the code from below:

This will return settings for account name, server type, server address, and port number:

Everything looks good, but the e-mail is still not getting out. A quick online lookup of the error message leads to a Stack Overflow thread in which the user has the exact same error message. In the comment section, another user says that changing the port number to 587 instead of 465, even though information from Gmail says otherwise, fixed the issue for him. As we are running out of options here, let’s try that and see what happens.

We will use the sysmail_update_account_sp stored procedure to change the port number in the existing Database Mail account.

Execute the following code:

Execute the code from the previous step to verify that the change is applied. The port number will change leaving all other account information intact:

For some reason configuration instructions for SSL provided by Google does not work on port 465 for Database Mail system, but port 587 that is specified for TLS/STARTTLS worked like a charm.

Finally, after giving it another try there is a new e-mail in the inbox sent from the Database Mail system:

Send e-mail from a trigger

To demonstrate how to send an e-mail notification to a user when a specific event occurred in the database, we can create a simple trigger.

Use the code below to create a trigger named iProductNotification on the Product table which is contained in the Production schema:

This trigger will fire when an Insert statement is run against the Product table. The idea behind it is to collect basic information about a new product that is available like name and price and to wrap that information up in a simple e-mail message that will be sent to desired addresses using the Database Mail profile previously configured:

Once the trigger is created, refresh the ‘Triggers’ node under the ‘Product’ table just to make sure that the trigger is created on the right table:

Rather than typing the code for the Insert statement and because it’s easier, use the ‘Edit Top 200 Rows’ option from the right-click context menu on the table with the trigger. Fill in some information like working with Excel sheets. Once done, hit the Enter key to continue:

This will issue an Insert statement in the background and fire the trigger. As a result, the trigger will collect some information about the Insert statement, invoke the Database Mail profile, and use it send e-mail to the desired recipient:

Sending query results e-mail message

Another example could be sending an e-mail message that contains query results. Execute the following code:

This SELECT statement returns a list of products with a specific condition in mind:

But more importantly, it sends an e-mail with results of that Select statement as an attachment to the e-mail recipients:

And if we open the attachment, there it is, the result of our query:

산업 모니터링에 관한 다른 Contents도 확인 하세요. 

• 미리 만든 Procedure를 일정한 시간으로 실행 하고자 할때 사용한다. 
• 일정 시간 마다 Remote Server에 Data를 Move하거나 update할 경우 사용한다. 
• 성공하거나 실패할 경우, 정해진 동작(다음 Step을 진행 하거나 Alarm을 줄 수 도 있다.)을 실행 한다. 

Introduction to the SQL Server Agent

In this article, I am going to explain in detail about the SQL Server Agent service. This is a windows service that enables database developers and database administrators to schedule jobs on the SQL Server machine. The jobs can be simple T-SQL scripts, stored procedures, SSIS packages or SSAS databases. This service is available on all the editions of SQL Server except the Express edition.

Overview

In this era of automation, it is often required that we execute multiple scripts repeatedly in a timely fashion. These scripts can be used to back up an existing database, delete extra log files, process data from a table, drop and rebuild indexes on a table, or running an ETL job etc. All these tasks are repetitive and can be automated using the SQL Server Agent. This gives us the flexibility that the job will be executed as per the rules defined in the schedule and there is minimal human intervention required in order to execute these jobs. Once the jobs are executed, you can view the history if the execution was successful or failed. In case of a failure, there is an option to restart the job manually.

There are a few components of the SQL Server Agent service that you must be aware of before proceeding forward.

1. Jobs – This is a program that defines the rules about the repetitive execution of one or more scripts or other utilities within the SQL Server environment
2. Steps – These can be considered as the building blocks of the jobs. Your job can contain one or multiple steps. Each step executes a specific set of instructions. The next step can be executed based on the success or failure of a previous step
3. Schedules – These are periodic rules set on the job to execute automatically based on a pre-defined time slot. These jobs can be scheduled hourly, daily, weekly, monthly, or even on specific days of the week
4. Alerts – SQL Server generates events that are stored in the Microsoft Windows Application Log. Whenever the agent finds a match, it fires an alert which is a response to the event
5. Notifications – You can set up email notifications to update about the result of the job execution. This is mostly done in case of job failures so that the person responsible for the jobs can take appropriate actions

How to find the windows service

Now that we have some idea about the SQL Server Agent, let us now go ahead and start the service if not already done. In my machine, the service is currently not running, and I am going to start it from scratch. You can follow the steps below to start the agent service on your machine.

Head over to Run and type the command services.msc. Click OK once done.

Figure 1 – Opening the Windows Services

Once you hit OK, the Services window will show up. You can scroll below to find the service with the name “SQL Server Agent (<<INSTANCE NAME>>)”. As you can see in the figure below, the status of the service is not running. Let us go and start the service.

Figure 2 – Starting the SQL Server Agent Service

Right-click on the service and select Start from the context menu.

Figure 3 – Starting the SQL Server Agent Service

It might take a while to start the service. Once the service has started, the status will change to Running.

Figure 4 – SQL Server Agent Service Running

Now, you can verify the status of the SQL Server Agent service using the SQL Server Management Studio as well. Head over to SSMS and navigate to the SQL Server Agent on the bottom of the Object Explorer.

Figure 5 – SQL Agent running in SSMS

Preparing the database

Now that our agent service is up and running, let us create our first job using the graphical user interface. We can also create jobs using T-SQL scripts; however, it is out of scope for this article and will be covered in some later articles. To demonstrate the execution of the job, we will create a small table in a SQL Server database that will store the date and time of the execution along with a random number. This is a dummy table just to demonstrate the feature. You can use the following script to create the table.

In this table, we are going to use the following script to insert records.

Creating the first SQL Server Agent job

As we have prepared our database, let us now create the job using the GUI. Right-click on Jobs and select New Job from the context menu.

Figure 6 – Selecting New Job from the context menu

As you click on New Job, the window appears on which you can define the properties of the job that you want to create.

Figure 7 – Creating the New Job

If you look at the figure above, under the General tab, I have provided a valid Name to the job. The Owner of the job should be a defined user in the SQL Server environment. I am logged on using my Windows Authentication mode. There is a Description field, where you can provide details regarding the tasks performed by the job. This is purely for documentation purposes. Finally, make sure that the Enabled is checked, otherwise, the job will not trigger. Click OK once done and click on Steps.

Figure 8 – Defining Steps

Navigate to the Steps section and click on New. Here we will define the steps in the job. For this article, we will consider a single step, but there can be multiple steps as well.

Figure 9 – Defining the Job Step

Provide a name to the step and select the step type. Since we are going to execute a T-SQL statement, please select Transact-SQL script (T-SQL) from the dropdown. Select the Database on which the script is to be executed and provide the SQL command as mentioned. Click OK once done.

Click on Schedules and select New.

Figure 10 – New Schedule

Figure 11 – Defining the new Job Schedule

On the New Job Schedule window, provide a name for the schedule. Most often, we keep a descriptive name so that it is easier to understand the nature of the schedule. Select the Schedule Type as Recurring since we are going to execute the job repeatedly. Select the Frequency as Daily and Daily Frequency as every 10 seconds. In real applications, it is usually scheduled every hour or every day depending on the requirements. Click OK once done.

Finally, click OK on the Job Properties page. Your job has been successfully scheduled now.

Viewing the data

Let us now execute the following command to view the data.

Figure 12 – Viewing the automated data

Since the job is scheduled to execute every 10 seconds, we can view the data from the table easily and verify the execution of the job. A new record will be inserted every 10 seconds from now on.

Viewing Execution History

Alternatively, you can also view the execution history of the job by right-click and selecting View History from the context menu.

Figure 13 – View Job History

This will open the Log File Viewer window, where you can see the status of all the executions so far. All successful executions are highlighted in green while failed executions will be highlighted in red.

Figure 14 – Log Viewer Window

Conclusion

In this article, I have explained in detail about how to schedule jobs in SQL Server using the agent service. This is a very powerful scheduling agent that can do a lot of the heavy lifting in terms of database maintenance and running other business jobs. Using the agent service and the monitoring service, you can monitor the active jobs that are currently running, the jobs that have failed or yet to start execution. You can also script the creating of the jobs totally, however that requires a bit of experience with the system stored procedures and various commands associated with it. If you are starting new with scheduling jobs in SQL Server, I would rather advise using the graphical user interface to create and manage the jobs on the server.

• 자주 조회사는 Query를 View로 만들어 두면 사용하기 편리하다. 
• 만들어진 View는 Table조회 하는것과 같이 사용할 수 있다. 
• 보안, 간략함, 일관성을 유지 할 수 있다. 
• Join과 Group을 이용하여 여러Table의 정보를 하나의 View로 만들수 있다. 

SQL Server Views

Summary: in this tutorial, you will learn about views and how to manage views such as creating a new view, removing a view, and updating data of the underlying tables through a view.

When you use the SELECT statement to query data from one or more tables, you get a result set.

For example, the following statement returns the product name, brand, and list price of all products from the products and brands tables:

SELECT product_name, brand_name, list_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id;

Code language: SQL (Structured Query Language) (sql)

Next time, if you want to get the same result set, you can save this query into a text file, open it, and execute it again.

SQL Server provides a better way to save this query in the database catalog through a view.

A view is a named query stored in the database catalog that allows you to refer to it later.

So the query above can be stored as a view using the CREATE VIEW statement as follows:

CREATE VIEW sales.product_info AS SELECT product_name, brand_name, list_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id;

Code language: SQL (Structured Query Language) (sql)

Later, you can reference to the view in the SELECT statement like a table as follows:

SELECT * FROM sales.product_info;

Code language: SQL (Structured Query Language) (sql)

When receiving this query, SQL Server executes the following query:

SELECT * FROM ( SELECT product_name, brand_name, list_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id; );

Code language: SQL (Structured Query Language) (sql)

By definition, views do not store data except for indexed views.

A view may consist of columns from multiple tables using joins or just a subset of columns of a single table. This makes views useful for abstracting or hiding complex queries.

The following picture illustrates a view that includes columns from multiple tables:

Advantages of views

Generally speaking, views provide the following advantages:

Security

You can restrict users to access directly to a table and allow them to access a subset of data via views.

For example, you can allow users to access customer name, phone, email via a view but restrict them to access the bank account and other sensitive information.

Simplicity

A relational database may have many tables with complex relationships e.g., one-to-one and one-to-many that make it difficult to navigate.

However, you can simplify the complex queries with joins and conditions using a set of views.

Consistency

Sometimes, you need to write a complex formula or logic in every query.

To make it consistent, you can hide the complex queries logic and calculations in views.

Once views are defined, you can reference the logic from the views rather than rewriting it in separate queries.

산업 모니터링에 관한 다른 Contents도 확인 하세요. 

• 아래 보기와 같이 Remote서버에 연결하여 Proto Type의 결과물을 빨리 만들고자 할때 Linked server가 유용하다. 
• 하지만, 속도에 문제가 있다. 
• 그래서, 실제 Remote서버의 Data를 사용할때에는 OPENQUERY를 추천한다. 

Linked Server in SQL Server: the good, the bad, and the way to make it perfect!

Introduction (the good)

If you are not familiar with a linked server concept in SQL Server, don’t worry, you probably haven’t needed it yet. You are fortunate to source all your data needs from a single database server, and that is fine. Or maybe you are consuming your SQL Server needs from Azure Single Database or AWS RDS (both public cloud solutions don’t support linked server out of the box).

Most likely this is going to change (Azure VM and AWS EC2 have full support) and you will have to join data between multiple database servers, or even between different RDBMS systems. For example: All the transactional sales data is stored in SQL Server, but all the analytical sales data is stored on another SQL Server server (this could even be MySQL).

This is where Linked Server comes in handy, especially at the data discovery phase. When building a prototype needs to happen quickly and there is no time to adhere to the best practices, Linked Server can be the answer.

Linked Servers Basics

According to Microsoft, linked servers enable the SQL Server Database Engine and Azure SQL Database Managed Instance to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of SQL Server. 

Linked Server is a pure convenience feature. With very little to no code changes, you can suddenly join multiple tables between local and remote/cloud servers. For example, let’s use WideWorldImporters transactional database

Figure 1

Linked Server effectively removes the need for a solution that will move and synchronize the data. You just setup a Linked Server (see Fig #2) and add a Linked Server reference in front of the three-part naming convention (see Fig #3). That’s all. A small price to pay for lots of convenience.

Figure 2

Figure 3

Problem (the bad)

While the Linked Server feature makes it easy to join tables between two or more different servers, it’s not free and it comes with a price tag: performance overhead. While joining a few small tables might not add noticeable pressure to the server, joining 3-5 fairly large remote tables might introduce locking and blocking and increase the run-time from seconds to minutes.

The main problem occurs when you run queries against a remote server, which is not healthy. When SQL Server runs a query with Linked Server, it will use the least optimal execution plan due to lack of knowledge of those remote tables. Meaning, your local SQL Server is clueless on remote table indexes and statistics, so it might use an incorrect joining mechanism and might be grossly inefficient.

For example, if you had to join all January 2013 orders between OLTP and OLAP tables and compare revenue per product while showing the five top contributors, we might build the following query (see Fig #4) to achieve that goal:

Figure 4

Reviewing Statistics IO (see Fig #5) and execution plan details (see Fig #6), we see:

Figure 5

Figure 6

As you might notice (far right), 93% of the query execution cost goes to a “mysterious” remote query.

Solution (a way to make it perfect)

One of the easiest ways to fix performance problems with a linked server is to run it via OPENQUERY.

What is OPENQUERY? According to Microsoft, OPENQUERY executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. We can reference OPENQUERY in the FROM clause of a query as if it were a table name. We can also reference OPENQUERY as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one. 

One of the main advantages of OPENQUERY is remote execution. This  means the local server sends the query to the remote server with knowledge of those remote tables that are now local to the query. By the way, remote execution also enables the use of native syntax of the remote server, so you can take advantage of other RDBMS system performance tricks.

Here is how our original query will look with an OPENQUERY (see Fig #7). It’s still using the same linked server, but it happens indirectly now with an  OPENQUERY.

Figure 7

When reviewing Statistics IO (see Fig #8) and execution plan details (see Fig #9), we now see: 

Figure 8

Figure 9

Let’s compare Statistics IO and execution plans to see the differences between direct query linked server usage vs. an OPENQUERY linked server.

The most noticeable differences are:

1. WorkFile reads in Statistics IO (see Fig #10)
2. Remote Query 93% cost vs. most distributed cost in Execution Plan (see Fig #11) and partially replaced with Remote Scans.

Figure 10

Why Does It Work?

The main reason OPENQUERY will usually perform better is knowledge of the table in terms of indexes and stats, knowledge that a direct linked server doesn’t have.

As you can see, we have managed to cut the run-time from 22 seconds (using direct link server) down to 2 seconds (using OPENQUERY). Additionally, we can add indexes specific to the query to make OPENQUERY even faster.

Disclaimer

Since nothing is certain in this life other than taxes and death, your mileage may vary (“YMMV”). Please test it (preferably using DBCC FREEPROCCACHE) prior to deployment. In addition the query above that reduced run-time by 11 times, I also scored a drastic performance gain by using OPENQUERY in one of the clients. In that example, replacing all three direct linked server references reduced run-time by 10 times, to just a few seconds.

산업 모니터링에 관한 다른 Contents도 확인 하세요. 

Sensor Connections: PNP versus NPN and Sourcing versus Sinking

산업자동화에는 24V용 센서가 주로 사용되며, NPN, PNP 두가지 타입이 있습니다. 두가지 타입의 센서는 PLC의 DI(Discrete Input) 모듈의 두가지 타입인 소스방식과 싱크방식에 맞춰서 사용해야 합니다. 

Transistor Effects

ON-OFF 형태의 신호를 다루는 전자장치(예를 들어, 입력센서)는 트랜지스터를 내장하고 있습니다. 트랜지스터는 반도체 재질의 소형 릴레이이며, 전류를 흐르거나 차단하는 스위치와 같은 역할을 합니다. 트랜지스터는 약한 신호를 감지후 신호를 증폭하는 역할도 하게 됩니다. 증폭된 신호는 PLC의 입력모듈 또는 기타 장치들로 전달될 수 있습니다. 트랜지스터는 NPN(sourcing), PNP(sinking) 두가지 타입이 있습니다.

"P", "N"은 반도체 물질의 종류를 의미하며, "PNP", "NPN"은 이 물질들의 배열순서를 의미합니다. 트랜지스터는 base, collector, emitter로 구성된 3개의 핀을 가지고 있습니다. 

PNP versus NPN Switching

산업용 3선식 센서는 대부분 아래와 같은 선으로 구성되어 있습니다.

* +24 Vdc

* 0 Vdc

* 신호선

+24 Vdc, 0 Vdc 선은 센서의 구동에 필요한 전원공급용이며, NPN, PNP타입은 신호선이 어떻게 구동될 것인지 결정하게 됩니다.

PNP 타입의 센서는 emitter에 +24 Vdc가 인가되고, collector에 sinking 타입 PLC 모듈과 연결됩니다. 센서가 특정한 물체를 감지하면 base에 전류를 흐르게 하여 emitter-collector간 전류가 흐르게 됩니다. 즉, 신호선쪽으로 +24 Vdc가 인가되고, PLC의 sink 타입 입력모듈 단자를 통해 포토커플러(빛을 이용하여 전류를 흐르게 하는 스위치. 입력모듈이 입력을 확인하는 용도로 사용)를 거친후 0 Vdc로 이어지게 됩니다.

NPN 타입의 센서는 emitter에 0 Vdc가 인가되고, collector에 source 타입 PLC 모듈과 연결됩니다. 센서가 특정한 물체를 감지하면 base에 전류가 흐르게 하여 collector-emitter간 전류가 흐르게 됩니다. 즉, 신호선쪽으로 0 Vdc가 인가되고, PLC의 source 타입 입력모듈 단자를 통해 포토커플러를 거친후 +24 Vdc로 이어지게 됩니다.

Three-Wire Devices and Leakage Current

3선식 센서는 트랜지스터 특성상 누설전류가 발생합니다. 센서가 신호선 스위치를 OFF하고 있음에도 신호선을 통하여 미세하게 전류가 흐르는 현상입니다. 비정상적으로 높은 누설전류는 의도치 않게 PLC의 입력모듈내 포토커플러를 동작시켜 마치 센서가 무언가를 감지한 것처럼 인식하게 만들수 있습니다.

Sourcing versus Sinking Circuits

sinking(싱크방식) 입력모듈은 PNP 센서와 사용되며, 입력모듈은 센서 감지시 신호선에 +24 Vdc가 인가되길 기다리고 있습니다.

sourcing(소스방식) 입력모듈은 NPN 센서와 사용되며, 입력모듈은 센서 감지시 신호선에 0 Vdc가 인가되길 기다리고 있습니다.

PLC 기종의 따라 sinking/sourcing를 반대로 표기하는 경우도 있으니, 입력모듈의 결선도는 꼭 확인해 보는 것이 좋습니다.

Benefits of PNP versus NPN

PNP 타입의 센서는 케이블 손상시 신호선과 0 Vdc선이 단락될 경우가 있을 수 있습니다. 그 결과로 센서가 손상될 것입니다.

NPN 타입의 센서는 케이블 손상시 신호선과 0 Vdc이 단락될 경우가 있을 수 있습니다. 그 결과로 입력모듈에 계속 신호가 들어오는 형태가 되지만 센서가 손상되지는 않습니다.

• PLC에서 올라온 Sensor의 값이 어떻게 연결 되고 변환 되었는지 알고 있어야 한다. 
• 현장의 값과 다른 경우, 무엇이 문제인지 알려면 기본적인 지식이 있어야 한다. 
• PLC Engineer와 원활한 소통을 위해서 Sensor의 이해가 필요하다. 

PLC 프로그램을 하다보면

아날로그 입력을 받아 처리하는 경우가 있을 것입니다.

아날로그 입력은 디지털(ON,OFF) 입력과는 달리 연속적인 값을 받아야 하는데요.

대표적으로 압력, 높이, 무게, 온도등이 해당됩니다.

이러한 값을 받아 PLC에서 정확하게 프로그램하기 위해서는

두가지 개념을 이해해야 합니다.

​

첫째는 스케일(scale) 입니다.

​

스케일은 범위를 말하는데요.

공학을 공부하거나 현장에서 기계설비를 다루는 사람들은

바늘계기를 가리키며 "풀스케일(full scale)이 얼마인가?"라고 묻곤 합니다.

풀 스케일은 그림에서 보듯이 최대로 표시할 수 있는 범위를 의미합니다.

이처럼 각종 센서들이 유효하게 측정할 수 있는 범위가 어디까지인지

제품사양서를 통해 반드시 확인해야 하는 것입니다.

​

둘째는 분해능(resolution) 입니다.

센서로부터 입력되는 연속적인 신호(아날로그 신호)를

PLC의 아날로그 입력모듈에서 얼마만큼 작게 쪼개서 표시할 수 있는가를 확인해야 합니다.

분해능이 높을수록 정밀한 값을 표시할 수 있을것입니다.

​

예를 들어

10bar의 압력을

PLC에서

10개로 쪼갤수 있다면 1bar, 2bar, 3bar와 같이 표시될 것이고,

100개로 쪼갤수 있다면 0.1bar, 0.2bar, 0.3bar와 같이 더 정밀한 값으로 표시할 수 있을 것입니다.

​

자, 스케일(scale)과 분해능(resolution)을 이해하셨다면

SMC 압력센서와 미쯔비시 PLC 아날로그 입력모듈을 가지고

실제 프로그램까지 작성해보도록 하겠습니다.

​

압력센서는

PSE564-01 로 선정하였습니다.

아래 사양서를 확인해 보도록 하겠습니다.

위 사양서에서 빨간색 부분을 보시면

센서의 스케일(범위)이 0kPa~500kPa까지이며,

출력이 1~5V 범위라는 것을 알 수 있습니다.

이와같이 센서가 가지는 측정 스케일과 출력신호 스케일을 정확히 파악 하셔아 합니다.

​

PLC는 FX2N-8AD로 선정하였습니다.

위 사양서에서 빨간색 부분을 보시면

입력범위는 -10V ~ 10V(전체 범위는 20V)이고

분해능은 2가지가 있습니다.

​

1) 20V를 8000개로 쪼갤수 있는 경우(일반분해능)

2) 20V를 32000개로 쪼갤수 있는 경우(고분해능)

​

여기서는 일반분해능을 선택하겠습니다

20V를 8000개로 쪼갤경우 2.5mV단위로 표시되게 됩니다.

그런데 센서출력은 1~5V이므로

PLC에서 사용되는 값의 범위는 400~2000이 될 것입니다.

이와같이 PLC의 분해능과 입력범위에 대해서 정확히 파악 하셔야 합니다.

​

그러면 센서의 스케일과 PLC의 분해능에 따른 입력범위를 가지고 새로운 그래프를 그려보면

PLC 아날로그 입력모듈을 통해 읽혀지는 값이 400이면 현재 압력이 0kPa이고,

2000이라면 500kPa이라는 것을 알수 있습니다.

​

이 그래프를 계산식으로 표현하면

​

y = a(x-400) (a는 기울기 또는 gain값으로 500/1600 = 0.3125가 됩니다.)

​

예제)

x가 1200일때

y = 0.3125 x (1200-400)

= 250

즉, PLC 아날로그 입력값이 1200일때 250kPa이라는 압력값을 얻을수 있습니다.

​

PLC프로그램은 아래와 같습니다.

빨간색 원을 보면 아날로그 입력값이 1200일때 250으로 계산됩니다.

빨간색 네모를 보면 기울기 0.3125를 곱하지 않고

3125를 곱한 후 10000으로 나누어 주었습니다.

PLC에서 실수형 연산보다는 정수형 연산이 편하기 때문입니다.

​

센서와 변환기의 중요한 차이점 중 하나는 센서가 물리적 변화를 감지 함 주변에서 발생하는 반면 트랜스 듀서는 물리량 또는 비 전기를 다른 신호로 변환합니다. 또는 전기 신호. 센서와 변환기 사이의 다른 차이점은 아래의 비교 차트에서 설명합니다.

트랜스 듀서와 센서는 모두 물리적입니다.물리적 양을 측정하기 위해 전기 및 전자 기기에 사용되는 장치. 센서는 에너지 레벨을 감지하여 디지털 미터로 쉽게 측정 할 수있는 전기 신호로 변경합니다. 트랜스 듀서는 동일한 형태 또는 다른 형태로 에너지를 전달합니다.

내용 : 센서 대 트랜스 듀서

1. 비교 차트
2. 정의
3. 주요 차이점
4. 결론

비교 차트

비교 근거감지기변환기

센서의 정의

센서는 물리량 (예 : 열, 빛, 소리 등)을 쉽게 읽을 수있는 신호 (전압, 전류 등)로 측정하는 장치입니다. 교정 후 정확한 판독 값을 제공합니다.

예제들 - 온도계에 사용 된 수은은측정 된 온도는 보정 된 유리 튜브의 도움으로 쉽게 측정 할 수있는 액체의 팽창과 수축으로 이어진다. 열전쌍은 또한 온도를 온도계로 측정 한 출력 전압으로 변환합니다.

센서는 전자 장비에 많은 응용 분야를 가지고 있습니다. 그 중 몇 가지가 아래에 설명되어 있습니다.

1. 모션 센서는 가정 보안 시스템 및 자동화 도어 시스템에 사용됩니다.
2. 포토 센서가 적외선 또는 자외선을 감지합니다.
3. 가속도계 센서는 화면 회전을 감지하기 위해 모바일에 사용됩니다.

변환기의 정의

트랜스 듀서는비 전기 신호의 물리적 속성을 쉽게 측정 할 수있는 전기 신호로 변환합니다. 트랜스 듀서에서의 에너지 변환 과정은 트랜스 덕션 (transduction)으로 알려져 있습니다. 형질 도입은 두 단계로 완료됩니다. 먼저 신호를 감지하고 추가 처리를 위해 신호를 강화합니다.

변환기는 세 가지 주요 구성 요소를 가지고 있습니다. 그것들은 입력 장치, 신호 조절 또는 처리 장치 및 출력 장치입니다.

입력 장치는 측정 량을 수신합니다.비례 아날로그 신호를 컨디셔닝 디바이스로 전송할 수있다. 컨디셔닝 디바이스는 출력 디바이스에 의해 쉽게 받아 들여지는 신호를 수정, 필터링 또는 감쇠합니다.

센서와 변환기의 주요 차이점

다음은 센서와 변환기의 주요 차이점입니다.

1. 센서는 주변의 물리적 변화를 감지하는 반면 변환기는 한 형태의 에너지를 다른 형태로 변환합니다.
2. 센서 자체는 센서의 주요 구성 요소이며 센서와 신호 조정은 센서의 주요 구성 요소입니다.
3. 센서의 주요 기능은 물리적 변화를 감지하는 반면 변환기는 물리적 양을 전기 신호로 변환합니다.
4. 가속도계, 기압계, 자이로 스코프가 센서의 예이며 서미스터와 열전대는 변환기의 예입니다.

결론

센서와 변환기는 둘 다 측정하기 어려운 온도, 변위, 열 등과 같은 물리적 양을 측정하는 데 사용되는 물리적 장치입니다.

산업 모니터링에 관한 다른 Contents도 확인 하세요. 

Different Types of Sensors – Analog and Digital

Sensors have become an integral part of the embedded system. Right from your mobile to security systems installed at home. They are also becoming important for meteorological stations to predict weather parameters like temperature, pressure, humidity, and many more.

To interface any sensor to the microcontroller, you have to know the function of the sensor and different types of sensors used in remote sensing, weather systems, security devices, health equipment etc. But, before going to know about sensors and its types you must know the basic definition of the sensor and its use.

What is a Sensor and How does it Work?

Sensor – Block Diagram

Sensor is a module or chip that observes the changes happening in the physical world and sends the feedback to the microcontroller or microprocessor. Excitation (Power supply) and Grounding must be provided to the sensor for the proper working.

Classification of Sensors

Microcontroller accepts two types of inputs depending up on the type of sensor i.e. analog or digital.

Analog sensor senses the external parameters (wind speed, solar radiation, light intensity etc.) and gives analog voltage as an output. The output voltage may be in the range of 0 to 5V. Logic High is treated as “1” (3.5 to 5V) and Logic Low is indicated by “0” (0 to 3.5 V).

Analog Sensor – Block Diagram

Unlike analog sensor, Digital Sensor produce discrete values (0 and 1’s). Discrete values often called digital (binary) signals in digital communication.

Digital Sensor – Block Diagram

Sensor Selection [Analog or Digital]

To select a sensor it’s important to know about analog and digital circuits. Analog circuits are made up of analog components like resistor, capacitor, Operational amplifiers, diodes and transistors.

Whereas digital circuits consist of logic gates and microcontroller chips. Analog signals are much affected by external noise and create errors in the output signal. But digital signals are susceptible to noisy environments and hence digital sensors are preferred over analog ones.

Note: If your application needs better accuracy and throughput go for digital sensors.

Problem with digital sensors:

Digital sensors have low calculation range. For example, digital temperature sensors such as HYT 271 and SHT series have lower temperature range.

But analog temperature sensors (RTD) have higher resolution (positive and negative temperature). This feature makes analog sensors suitable for wide temperature range and stability. The analog output from the sensor is processed by the ADC (Analog to Digital Converter) of the microcontroller.

As discussed above, how sensors are classified and how to select a sensor, now it’s time to know about different sensors in nature and how they are used in the industrial applications.

Types of Sensors

1. Analog Pressure Sensor

Analog Pressure sensors work on the input voltage and against the known pressure value. The output of pressure sensor is analog output voltage (normalized). The units of pressure are psi (pounds per square inch).

Analog pressure Sensors (Barometric)

2. Digital Pressure Sensor

Digital pressure sensor has inbuilt signal processing unit such as ADC that converts the analog input to digital pressure output. Generally, in most of the digital sensors I2c based digital signals are taken out.

Some of the applications of the Barometric pressure sensor are:

• Leak detection in gas pipes and cables
• Measuring pressure for environmental purpose
• Radiosonde
• Tyre pressure monitoring
• Respiration analytics
• Industrial and process control
• Medical devices
• Airflow monitoring
• Drones
• Inhalers
• Water level measurement

3. Analog Hall effect/Magnetic Sensor (Position Sensor)

The Hall Effect sensor works on the magnetic field. It senses the magnetic field and gives the output voltage. When the magnetic field is positive output voltage increases above the null voltage (no magnetic field and zero current).

Applications of Hall sensor are:

• GPS positioning
• Current sensing
• Speed movement detection
• Magnetic code decoder
• Metal detector
• Controlling motors

4. Load cell (weighing sensors)

Load cells measures and process the weight. There are different types of load cell sensors based on usage of the application. Some of them are:

1. Beam Load cells (Bending Load cell)

These are suited for normal weight measurement applications and industrial-weight measurement. Applications of beam load cell are

• To fill the machinery
• Tank weighing
• Vehicle Onboard weight
• Medical equipment
• Bed weighing
• To package machinery

1. Single point Load cell

Single point load cells are used for low weight systems.

Applications:

• Bench and retail scaling
• Waste collection
• Package and filling machinery

1. Compression Load cell

These are suitable for high weight scaling systems.

Applications:

• Medical devices
• Measuring equipment
• Pump controlling devices
• Railway weight checkers

5. Temperature Sensor

Analog temperature sensor measures the temperature of the current location based on the resistance in variation. The value of resistance changes with temperature.

RTD sensors are best suitable for stability, accuracy and reliability. They comes with wide temperature range.

Applications:

• To measure water temperature
• Air temperature measurement
• Air conditioning
• Plastics
• Food processing

6. Reflex Sensor

A simple reflex sensor has transmitter and receiver sections. The transmitter sensor emits a light beam and when an object is detected the receiver captures and process this as changeover signal.

Applications:

• Monitors flow of material process
• Level measurement
• Print and Color detection
• Inspection of Assembly
• Sealing cap monitoring
• Recording level of liquid quantities

7. Fork Sensor

Fork sensors are quite close to the reflex sensor but efficient in picking and detecting very small objects with a size of micrometers (µm).

Applications:

• Bonding control
• Placing and sorting small components
• Power supply control
• Gap monitoring and control
• Recognize holes and drills

8. Wind speed / Wind direction Sensor

Wind speed/wind direction sensor commonly called anemometer uses the ultrasonic technique to get wind speed and direction.

Applications:

• Environmental weather stations
• Drifter Buoys
• Oceanographic and meteorological stations
• Harbours and seaports
• Road tunnels

9. Radar Sensor

Radar transmit short microwave signals that travel at the light speed. Radar sensors are used for measurement of liquid levels. The output analog current (4-20 mA). This current is converted to the voltage by placing a resistor and read by the ADC of a microcontroller.

10. Solar Radiation Sensor

Global radiation sensor (solar radiation) uses photo voltaic diode for measuring solar radiation.

Applications:

• Meteorological stations
• Hydrological and agriculture
• Energy applications
• Irrigation

11. Humidity Sensor

Humidity sensor calculates the humidity of the present location. Humidity is an important parameter for environmental observance, consumer electronics and medical diagnostics.

Applications:

• Humidity transmitter
• Handheld devices
• Refrigerators
• Air compressors
• Weather stations
• Industrial incubators
• Medical systems
• Dew measurement

12. Air Quality Sensor [Gas sensor]

This sensor monitors the number of gases in the air like CO2 (carbon dioxide), SO2 (sulfur dioxide), CO (carbon monoxide) etc.

Applications:

• Meteorological institutes
• Health agencies
• Homes and Hospitals
• Industrial applications
• Ventilation systems
• Air cleaning

13. Light Sensor

Light sensor captures the ambient light (dark/bright) and converts it to current.

Applications:

• Display backlight in Mobiles and LCDs
• PDA (Personal digital assistant)
• TVs, Cameras
• Toys
• Automatic street light control
• CCTVs

14. Rainfall Sensor

Meteorological agencies use rain gauge sensor to measure the amount of rainfall falls in a particular location. Rainfall is measured in mm. The most used device for measuring rainfall is Tipping bucket Rain gauge.

15. Soil Moisture Sensor

Soil moisture measures the amount of salt and moisture in the soils. It also measures the temperature in the soil. It is based on SDI-12 protocol.

16. Water Level Sensor

Water level sensor calculates the depth of water in lakes, dams and rivers. There are various analog and digital based water level sensors.

Applications:

• Water level monitoring
• Environmental applications
• Groundwater tanks
• Surface water applications

Conclusion

Sensors have become a vital part of consumer electronics, industrial electronics, Robotics and Internet of things. I hope this article gives you an overview of different types of sensors used in the industry.

산업 모니터링에 관한 다른 Contents도 확인 하세요.