DEV Community: ToolGBRMaker

Table Trigger | SQL

ToolGBRMaker — Sun, 21 Aug 2022 15:12:55 +0000

Let’s continue to improve our sample database, now by adding a table trigger on one of our tables.

The triggers can be either INSERT, UPDATE or DELETE clause when we speak in terms of D ata M anipulation L anguage. Its execution part usually contains a store procedure or a batch.

For demonstration purposes, let’s add two new columns to our customer table on our sample Database. (If you want to follow the next step and you missed the table creation, please check the following post).

Current Customer’s Table schema

ALTER TABLE to add columns to Customer’s table

Now that we have two new columns it’s time to add our table trigger, it will be a simple one that will update LastModificationDate column every time an update is performed for this table.

CREATE TRIGGER statement – example

Object Explorer View

I’ll now import a few more Customers to my table by using the CSV importation already learned here in this post. (Please have a look if you missed that one!)

Data belonging to the CSV file to be imported

All the dataset from the Customer’s table

Now that we have these two new columns and our new DML trigger, let’s test it by changing something on one of our rows. I think that nowadays Portuguese president should be annoyed by my mistake on his last name and is looking forward to the correction from “Sousa” to “de Sousa”. ( Sorry Professor :D).

UPDATE customer

After running our column update, if we select all table rows we’ll be able to find that LastModificationDate is now updated for the update execution DateTime.

All the dataset from the Customer’s table

Let me know if any doubt remained and stay safe!

Accessing SQL Database | PowerShell

ToolGBRMaker — Mon, 02 May 2022 18:03:59 +0000

The title couldn’t be more explanatory. During this post, we’ll see how we can access the database, and select data, by PowerShell.

Let me try to guess…

But why!?

Well, besides for pure thirst for knowledge of how we can do fancy things :D, could pass through, by using this way of access, to store data through a PowerShell script, like, log entries storage, metrics from system, display the dataset results on a html page, etc.

First things first, so to be able to communicate with the aimed database we need to establish one connection, and for that, I’ll use the following lines…

Connecting to the aimed SQL database through System.Data.SqlClient DLL (.SqlConnection)

A brief explanation of what has been made so far.

On the first line, I’ve built my connection string, adding my SQL server and the database that I’m trying to establish my connection. The server can be easily found by running Microsoft SQL Server Management Studio and on the view tab by selecting registered servers, or by querying the function @@ServerName.

SELECT @@SERVERNAME AS ‘Server Name’

Afterward, I’m starting to instantiate the DLL that will be used to perform the connection. In the above example, we are using the DLL System.Data.SqlClient, after being instantiated will grab the connection string and open/close the communication.

And that’s it, your connection was established and closed (if you gave it a try to run the script shared so far). Now it’s time to get something from this connection. Let’s select some data from our Database.

The query that I’ll within on my PowerShell script is the following:

SELECT * FROM customer WHERE CountryCode = ‘FR’

Do you remember that I inserted this record in this blog post? Looks like I was guessing that he would be elected for the second term of France’s presidency. I’m feeling like an oracle… Do you want to know anything about your future!?

Time to pass this query to our PowerShell script. For that I’ve used the following lines of code:

Command that will run on SQL database through System.Data.SqlClient DLL (.SqlCommand)

A little bit of context of what has been made above. So, we’re still using the System.Data.SqlClient but this time, rather than be establishing the connection, we’re instancing the command that will run on our pre-established connection and store the result dataset on a PowerShell object named $reader.

As the last step, we need to ensure that we loop our dataset and print the data returned from our SQL Command. For that I’ve used the following lines of code:

Looping through the Data Set

Let’s see what our script returns after being executed:

Our entire script

Now, let’s pursue excellence… Let’s build a PowerShell function with the above lines, and from that point on, you’ll be able to use parameters that, for example, will get different dataset results based on filter switching. If you’ve doubts of how to do it, please jump to the following blog post where you’ll get useful hints of the steps to follow.

Our Get-CustomersFromSampleDB PowerShell function

Let’s invoke the function, once, without parameters, and see what we get.

Get-CustomersFromSampleDB

And now by using a country code as a parameter…

Get-CustomersFromSampleDB -CountryCode ‘PT’

And I’m happy with what has been achieved. A simple example of how you can connect to your database and get data from it.

I hope that helped! Stay safe!

Quick Tip: Establishing PowerShell Remote Session – Docker Container

ToolGBRMaker — Tue, 19 Apr 2022 16:04:38 +0000

The title is quite self-explanatory, so, if you want to establish a remote session from PowerShell, running on your host, to the PowerShell, running on your container, just follow the next steps.

As the first step, we’ll need to get the Container ID, the necessary parameter to use on the cmdlet Enter-PSSession to perform the remote session.

docker ps –no-trunc

Afterward, we need to copy-paste the ID highlighted on the above image and perform the next step to end with the remote session established.

Enter-PSSession -ContainerId $ContainerID -RunAsAdministrator

And that’s it, remote session established… From this point on you’ll be able to run your cmdlets in your container from your host machine.

I hope that helped! Stay safe!

Quick Tip: Importing new license – Docker Container | Business Central

ToolGBRMaker — Fri, 25 Mar 2022 18:41:47 +0000

A few days ago, I’ve faced the following error when I attempted to open my Business Central instance from my Docker Container:

Your program license has expired.

Let’s see what steps I’ve adopted to import the license on my Docker Container.

First, I’ve copied the license, that I want to import, from my host to my Docker Container by doing the following command:

Docker cp “C:\Bruno_s Documents\Licenses\license.flf” bcsandbox17:c:\Run\license.flf

Before moving on, a quick check if the command was well succeeded…

Now that I’m sure that the license file is there, on my container, is time to import it to the aimed instance, by using the following cmdlet:

Import-NAVServerLicense -LicenseFile “C:\Run\license.flf” -ServerInstance bc -Force

By executing the above cmdlet you’ll end with your license imported, but to see reflected the upload in the Business Central you’ll require to restart your instance. If you don’t remind how to do it please jump until the following post and you’ll be able to find how to do it.

I hope that this helped. Stay safe!

Importing data by CSV | SQL

ToolGBRMaker — Mon, 14 Mar 2022 20:01:33 +0000

Let’s continue improving our SampleTest database, today by creating a new table and importing data to it by using a CSV file.

The new table will store the country details that we will use to relate with our customer table, already created on the post.

Schema creation…

Country table creation

Now, that we have our table schema, it’s time to import some data, I’ve created a CSV file with the following rows…

and to perform the data importation the following script:

Above we are invoking a Bulk Insert for our new table Country and the data used for insertion is the one stored on our CSV file. Afterward, on our With clause we are configurating some settings to be considered during the data reading on CSV file, although I’m considering that the settings are quite easy to understand, I would like to highlight the FIRSTROW, where we are saying that due to our column headers on the file, should only considered the data from the 2nd row on.

Time to see if the data ended as expected…

As the last step, let related now this new table with our Customer table, by CountryCode field. For that, we will use the ALTER TABLE statement where we’ll add a Constraint, Foreign Key and we’ll referrer for the field we want on table Country.

Creating Relation

Time to test this new relation. I’ll now add a row with a non-existent country and see if an error is now invoked as supposed.

The new relation is now blocking non-existent countries on Country table for the Customer

Now a test with an existent country…

And that’s it. With the above importation, we can easily add data that can be used to relate with our existent table Customer. Time to try by yourself and let me know the results.

Stay safe!

Automated Testing for your scripts – Pester | PowerShell

ToolGBRMaker — Tue, 15 Feb 2022 18:41:05 +0000

Version: 5.1.x

This post will be fully dedicated to how you can automate tests for your PowerShell scripts. As a scenario, you have written some new code or modified some old code and now you want to test these changes, in an automated way, before putting them in the Production environment.

Introducing Pester

Pester is one testing and mocking framework for PowerShell that’ll provide you with a framework for writing and running tests. Its a open source project that’s bundled with Windows 10 and later.

What we’ll test?

Since we are introducing this automated testing concept and we need a script to test, why not use something already created on this blog!? Therefore, we’ll test the function created during the post “Building a function | Powershell” (If you didn’t read it, now its a good time for it, otherwise will be hard to understand the reason of our tests).

As the first step, we need to load the Pester module and create a new text fixture to allow us test our scripts. For that we will use the following cmdlets.

Import-Module Pester
Mkdir testOurServiceFunction
New-Fixture -Path testOurServiceFunction -Name Set-ServiceRunningStatus

Notice, on the above image, you should end with two PowerShell script files… One for your code and the other where you’ll built your testing conditions.

Let’s focus mainly on the Set-ServiceRunningStatus.Tests.ps1. Here, if you open your file, you should have something similar to the following…

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path) -replace '\.Tests\.', '.'
. "$here\$sut"

Describe "Set-ServiceRunningStatus" {
    It "does something useful" {
        $true | Should Be $false
    }
}

Explaining a bit of what you have on your test script… The first three commands are to link the test code with your code script. The next two sections are:

Describe – This block is designed to contain a set of tests. Since we’re introducing this automated test concept we’ll only stick with this one, but more complex are your tests, more often will be to have multiple Describe blocks;
It – This block represents a single test where your code will either pass or fail. A Describe block often contains many It blocks.

How we’ll test?

First things first, we’ll start by mocking the principal “actions” from our script and are they, Restart and Start services:

Mock Restart-Service { return 1 }
Mock Start-Service { return 1 }

With this, we will be able to get a counter of actions triggered and we can compare with what we are testing for the moment of our It clause.

Let’s start with a basic test… Our goal will be “accepts and restarts one running service” and the way we will achieve it will be by using the following:

It "accepts and restarts one running service" {
        $result = 0
        Get-Service -Name '*_W1*' | Where-Object { if ($_.Status -eq 'Running') { $result += 1 } }
        Set-ServiceRunningStatus -Name '*_W1*' 
        Assert-MockCalled Restart-Service -Exactly $result -Scope It
}

On the above test, we are looking for a restart on running services with Name that includes _W1 string and counting the ones that will be impacted. After that, we will invoke our function and check with the Assert-MockCalled if the number of restart are coincident with the number of Running Services with name that includes _W1.

Let’s execute and see the results so far…

Well, so far so good, 1 out of 1 passed. But how much, quantitively, I’ve covered in my script with the last test!?

Let’s see by using the parameter -CodeCoverage.

invoke-pester -CodeCoverage .\Set-ServiceRunningStatus.ps1

We have half of the script, tested. As you can see, besides having the covered percentage we have also the missed commands so far. Let’s give it a try to add more tests and try to cover more amount of the script.

    It "accepts and restarts multiple running services" {
        $result = 0
        Get-Service -Name '*Pharma*' | Where-Object { if ($_.Status -eq 'Running') { $result += 1 } }
        Set-ServiceRunningStatus -Name '*Pharma*' 
        Assert-MockCalled Restart-Service -Exactly $result -Scope It
    }

    It "accepts and restarts one running service" {
        $result = 0
        Get-Service -Name '*_W1*' | Where-Object { if ($_.Status -eq 'Running') { $result += 1 } }
        Set-ServiceRunningStatus -Name '*_W1*' -Force
        Assert-MockCalled Restart-Service -Exactly $result -Scope It
    } 

    It "accepts and restarts one running service on a Computer" {
        $result = 0
        Get-Service -ComputerName 'PTPOPF247203' -Name '*_W1*' | Where-Object { if ($_.Status -eq 'Running') { $result += 1 } }
        Set-ServiceRunningStatus -ComputerName 'PTPOPF247203' -Name '*_W1*'
        Assert-MockCalled Restart-Service -Exactly $result -Scope It
    }

With a few more, like the above ones, we have been able to achieve the following coverage:

And that’s it. Lightly we have been able to see how to test our PowerShell scripts and automate this annoying task… At least for me, it is an annoying one.

We have also been able to see a metric of the code covered by our tests and have a guide of how to achieve better tests and inherently, better solutions with this testing framework.

Note: Besides internet references, I’ve also used the learning concepts acquired in the following book. I advise it as good reading.

Stay safe!

Quick Tip: OUTPUT clause – Good Policy | SQL

ToolGBRMaker — Wed, 02 Feb 2022 19:20:06 +0000

Let’s check how the OUTPUT clause can save the day if we, after an update, may want to revert what has been updated.

Imagine that we have the following output from a customer table on the SQL:

And we decided to switch all the customers with the first name “Antonio” to “António”. A simple update query like the following could achieve what we’re looking for…

Now, happens something completely unexpected in the TI world (or not!!!)… Well, customers are not pleased with their name change and want to get them back on our database. But which ones have been changed?

Time to use the OUTPUT clause on our update queries. If you use it, you’ll get an output of all the entries that have been changed and back up that information to be reused after, if needed. See the next example of how can be used on the above example:

Now, you can copy, save, extract, etc., your result to backup file and use it whenever needed.

Stay safe!

How to foresee potential lockings on your tables | Business Central

ToolGBRMaker — Tue, 01 Feb 2022 17:31:04 +0000

What a beautiful code I just finish to create… Let’s skip the tests… Put it in a Prod environment and hope for the best. Booommmmmmmmm!

Well, not so frequently nowadays, but, in a rush, is still happening, right!? You don’t need to answer… Even though you tend to say that it’s a lie, it still happens. I know!

To get even worst, the above, it’s quite complex to anticipate the process concurrency on the SQL side when our queries are being triggered from Business Central, but let’s give you an example of how to mitigate these potential locking issues, pandemic is affecting us in so many ways :), and help you with some examples where you can avoid, or at least investigate if your code is the one increasing the locks on the Prod Environment.

Business Central is a friendly environment for the developers, if you look at the queries that it generates and request to the SQL side, you’ll be able to see frequently on data consults/reads, the (WITH READUNCOMMITTED) that allows dirty reads of data, even that you are with any locking transaction affecting the table that you’re reading. The NOLOCK, with argument, is the equivalent to the READUNCOMMITTED.

Let’s see an example of it…

Imagine that I want to log some integrations executions on my business central solution and I’ve created the following table structure to store my data.

Table Structure

Attached to this table object, I’ve created a listing page to show the data on it, and a codeunit with some functions to manipulate the data and “imitate” common data processing on this sort of table/entity.

Page View – with some actions

I have now my scenario prepared and I have added, through the functions attached to my page, a big combo of entries to increase my available dataset.

Potential scenario:

I have this dataset and I have set a job to process these pending entries. This job will look for datasets based on Direction, Message Type, and Status fields. To simulate this potential scenario I have created the following procedure.

Set All Processed – Procedure

If you look carefully at my procedure, you’ll be able to see that I’ve used the Findset method with the boolean ForUpdate parameter set as true. It’s a fact that I’m intended to update my data, so should be harmless to use, isn’t it?

In all the deals, we should always pay attention to all the small letters, to avoid surprises, and even that in here they are not in small size, the letters of course, if you look carefully on the Microsoft documentation for Findset parameters you’ll find that if the ForUpdate parameter is set to true, promising us that will reach the optimal performance for our loop, but in parallel, the parameter will immediately run the LOCKTABLE method.

Now, let’s see this in action… I’ve chosen an entry and I’ve triggered for the first time the SetAllProcessed action. Let’s see the results:

Since the LOCKTABLE method was active on our loop what will happen if we query our table on the SQL side in the meantime?

SELECT * FROM [CRONUS$IntegrationLog$a84606c8-7725-4ecb-9237-4fb9bd1bf179]

The above code execution will be waiting until the Business Central SetAllProcessed action be completed because is blocked by the lock established for this table/entity. As proof of it, see the following result from sp_who2 SQL procedure.

Here is where Business Central is a friendly environment to the developer, let’s perform the same query, during SetAllProcessed action, but this time invoked by the Business Central, or more precisely from PowerShell… I’ve used this approach to have other session requesting processes actions on the table IntegrationLog. For that, I’ve created the following procedure and the following PowerShell script.

Get All Entries On Integration Log – Procedure

If you look for the SQL query created for the above invocation you’ll find that no lock occurred because…

The READUNCOMMITTED is part of your business central query.

The read is tested and it is “protected” against locks, which could potentially lead to other problems, like updating different data images, of the same entries on different transactions, but there is where Optimistic Concurrency will help the day… Another subject for another post.

Potential lock:

Now, what will happen if, instead of reading data we try to update entries during SetAllProcessed action execution? Let’s give it a new test, this time using the following two procedures…

Both, are quite intuitive, the first is setting all the entries, with the same Direction and Message Type of the Record passed through a parameter, as these entries are in error. The second one, getting the entry to use as a model/parameter on my SetAllError.

Due to my default settings, that are remaining with the query timeout on 10 seconds, I’ll get the following results after the PowerShell invocation waited my defaulted 10 seconds…

The loop request made on SetAllProcessed is blocking any other update attempt.

Exploring Solutions on this hypothetical scenario…

The first thing that occurs me right now is… Well, if the ForUpdate parameter on the Findset is locking the table, let’s get rid of it on my procedure. So, my new approach for the procedure can be found on the next image (if you remember earlier we saw that with the LOCKTABLE enabled, we should expect the optimal approach, therefore, with this new way of doing the things we must expect worst efficiency)…

Set All Processed – Procedure – new approach

Let’s see the execution time results for this new approach…

More than 3 times slower, if we compare with when we locked the table. But wait, I can perfectly set this routine to be performed on a background session and live with this slowness, if this resolves my locking issue. Let’s give a new trial on the above execution and also execute another update transaction from the PowerShell invocation.

Wait, wait, wait… What the heck!? Facing the same locking issue as before!? But I’m not with LOCKTABLE on… How is this possible?

Since my scenario is operating a quite heavy dataset, the SQL tends to escalate the LockMode, when it is operating massive records, and if it reaches the X of exclusive it’ll behave exactly as if we set the LOCKTABLE. With the help of the following query lets check if this is the case:

--#############################################
--# Transaction With LockMode Exclusive #
--#############################################
SELECT DISTINCT(SessionData.session_id) as spid,RIGHT(obj.name,LEN(obj.name) - charindex('$',obj.name)) as object,tranLocks.request_mode as LockMode, RequestsData.logical_reads as HowManyRows, RequestsData.command as XclusiveLockForCommand,
    RequestsData.transaction_id, SessionData.[host_name],RequestsData.text
    FROM sys.dm_tran_locks tranLocks
        INNER JOIN (SELECT session_id, [program_name],login_name,[host_name],login_time, last_request_end_time
                        FROM sys.dm_exec_sessions) SessionData ON SessionData.session_id = tranLocks.request_session_id
        LEFT JOIN (SELECT session_id,command,[status],blocking_session_id,objectid,transaction_id,logical_reads,text
                    FROM sys.dm_exec_requests
                    CROSS APPLY sys.dm_exec_sql_text(sql_handle)) RequestsData ON RequestsData.[session_id] = tranLocks.request_session_id AND RequestsData.transaction_id = tranLocks.request_owner_id
        LEFT JOIN (SELECT name,object_id FROM sys.objects) obj ON obj.object_id = tranLocks.resource_associated_entity_id
    WHERE RequestsData.command IS NOT NULL AND tranLocks.resource_type = 'OBJECT' AND tranLocks.request_mode = 'X'

And my guess was right, the SQL server was escalating the lock until be exclusive for my first update query that was triggered by my Business Central procedure SetAllProcessed.

What we should look for, as the next step!? Well, we should pursue two things here… Increasing performance and a way to the SQL Server manage concurrency and be able to process more than one request for the same table.

Increase Performance

Here we can switch a little bit of the code by isolating our ExtraProcessing effort from the update, avoiding a few calls to the SQL side… See one possible suggestion:

Even with this approach, we are processing everything in one transaction and only committing it at the end, ensuring that the data integrity will remain even if an error occur on one of the steps – an error and all data changes will be rollbacked.

Also batching better the number of entries that are being processed, is a good way of avoiding lock escalation.

Helping SQL Server manage the concurrency

You should be guessing by now how we can do this… by using indexes! Are we missing any!? Well, when I’ve created my table on Business Central, I didn’t concern with the creation of other than the index automatically generated for the primary key (if you missed the post about indexes, it is a good time to jump into it and give it a look).

Let’s check, by using the following code, if the SQL execution plan suggests any missing index.

SELECT mid.equality_columns,
       mid.inequality_columns,
       mid.included_columns,
       RIGHT(mid.statement,LEN(mid.statement) - charindex('$',mid.statement)) AS table_name,
       (SELECT Max(rowcnt)
        FROM sysindexes
        WHERE id = mid.object_id
               AND status IN ( 0, 2066 ))
       AS [rowcount],
       migs.user_seeks,
       migs.avg_total_user_cost,
       migs.avg_user_impact
FROM sys.dm_db_missing_index_details mid WITH (nolock)
       INNER JOIN sys.dm_db_missing_index_groups mig WITH (nolock)
               ON mid.index_handle = mig.index_handle
       INNER JOIN sys.dm_db_missing_index_group_stats migs WITH (nolock)
               ON mig.index_group_handle = migs.group_handle
WHERE database_id = Db_id()
       AND user_seeks > 5
ORDER BY avg_user_impact DESC,
          user_seeks DESC,
          user_scans DESC,
          last_user_seek DESC

Missing Indexes Query – Results

Looking at the query results, we have one suggestion to be added. Let’s use the index creation suggested and add a new key for my table.

My new key – as suggested by SQL query.

If we give it a look to the SQL side we’ll be able to find the index that was automatically created for this new key, as a non-clustered unique.

Now, what do you think will happen if I put the two concurrent processes running at the same time with this new key?

Our locking event is gone… And at same time I was able to set with status Processed all my Outbound-Orders entries and in error all my Inbound-Documents entries.

Conclusion

We, developers, should be careful with our code solutions and try to foresee some of the behaviors on Prod environment, to avoid a few surprises when our code is deployed. I’ve exemplified through a potential scenario of something that could escape from our radar during a development, but even if your challenge is not similar to the above one, you already have on this post some tools/ways to investigate the reason of why you’re getting some of the issues and solve them.

At last but not the least…

On this quite big post, I know that became extensive, you’ve been able to see why I’ve grown my passion on studying SQL Server better and also PowerShell scripting. With all together I was able to create a route map of how to test my Integration Log approach in a way to simulate concurrency transactions context and investigate the reason of errors. I hope that was helpful!

Stay safe!

Quick Tip: Setting new values at NAV/BC server configuration – Docker Container | Business Central

ToolGBRMaker — Thu, 20 Jan 2022 18:24:19 +0000

The title is quite self-explanatory, so, if you want to set a new value for one of the settings on your NAV server configuration at your Docker Container you must use a PowerShell cmdlet execution to perform it.

For this PowerShell setting approach, you need to know two things – which NAV server you want to modify and which of the settings will you change the value.

To know which NAV Server Instances are running on your Docker Container you can run the following command on your docker PowerShell prompt:

Get-NAVServerInstance | Select *

The result from Get-NAVServerInstance cmdlet

Now that you have the Server Instance available on our container, let’s use this output to get the in vigor settings for the NAV Server Configuration…

Get-NAVServerConfiguration -ServerInstance <YourNAVserverInstance>

The result from Get-NAVServerConfiguration cmdlet

Time to chose which one you want to switch/change… Let’s imagine that we want to enable our API services. To achieve it, we must perform the following cmdlet with the following parameters:

Set-NAVServerConfiguration <YourNAVserverInstance> -KeyName ApiServicesEnabled -KeyValue $true

As you may notice on the warning message, you must finish all your changes with a service restart. If you have doubts about how to do it, time to jump for the following post where you can grab what you are looking for.

Stay safe!

Quick Tip: DNS lookup issue – Docker Container | Business Central

ToolGBRMaker — Mon, 17 Jan 2022 17:03:09 +0000

Recently, due to some Integration REST API tests, I faced an error during my request HTTP attempt. The error was:

An error occurred while sending the request. The remote name could not be resolved:

After a search on some blogs I was able to correlate the error with a DNS issue and gave it a try the following commands on Container Command Prompt:

ping google.com

With the result of…

Ping request could not find host google.com. Please check the name and try again.

ping 8.8.8.8

With the result of…

Success

After been identified the issue I was able to fix it by adding the following lines on Docker Settings – Daemon configuration file:

{
  "registry-mirrors": [],
  "insecure-registries": [],
  "debug": true,
  "experimental": true,
  "dns": [
    "8.8.8.8"
  ]
}

The above solution was taken from the following blog post. Thank you Robert Bird for sharing it!

Stay safe!

Indexes | SQL

ToolGBRMaker — Wed, 12 Jan 2022 17:23:34 +0000

Let’s talk a little bit about indexes, their relation with data pages, and their importance in querying data from databases.

Database indexes are used to provide faster access to relational data. An index is a separated physical data structure that enables faster access to one or multiple rows to an entity or table.

Analogous, we can think of a book index or the yellow pages (phone numbers) as examples of indexes.

So, what it means to have or not have an index?

Well, let’s imagine that we have a table, with a primary key, and no index on it. To find the row(s) we want on the data pages, we’ll blindly start our search with no clue of where to find them. Thus, to ensure that we end on the aimed row(s) successfully, we must start reading sequentially from beginning to end until we find what we want. The higher is the number of rows on the table the higher will be the time consumed on this task. Image what would be to try to find a row in the middle of 1000000.

Here is where indexes are useful… Since they are built in a separated physical data structure, more exactly on a B+-tree structure, will build a sort of “route map” that will perform faster access to data. On average, the operation time complexities will be faster than without indexes.

We can have two types of indexes on our tables:

Clustered – The B+-tree of a clustered index will be fulfilled with root, intermediary and leaves nodes. The root and intermediary nodes will be in index pages structures, containing a key value and a pointer to the data row on the leaf level, seeking through them in cascading way. This type of index is unique by table and it is created taking in basis the table primary key.

Took from docs.microsoft.com

Nonclustered – They have the same structure as clustered indexes, having a difference that the leaves are also index pages instead of data pages. For this type of indexes, we have no maximum number for them, but should be taken into consideration that the SQL server will need to balance these B+-tree structures on any insert or delete operation, and these comport and effort. This effort will increase with the number of indexes, therefore these indexes should be added wisely after deeper analysis.

Took from docs.microsoft.com

Friendliest representation of Clustered indexes…

Friendliest representation of Nonclustered indexes…

Now, that you know that index pages are a “route map” to faster data page access and how they work, you might end with a final doubt…

Are we able to see how it looks like a data page?

To exemplify what we have inside of a data page, I’ll use the structure created on this post (if you didn’t read it, now it is a good time to jump into it).

Let’s add some dummy data to it…

INSERT INTO dbo.customer (CustomerNo,CustomerFirstName,CustomerLastName,CountryCode)
    VALUES (1,'Bruno','Guedes','PT'),
           (2,'Marcelo','Sousa','PT'),
           (3,'Antonio','Costa','PT');

Now let’s get a look at our table’s page structure by using the following query:

DBCC IND('SampleTest',customer,-1)
GO

Here we are seeking the rows with PageType 1 that is the type of data page. ( PageType 2 – index pages and PageType 10 – IAM page that maintains the index itself).

Now that we know the PagePID from our data page, let’s use the following query to check the answer to our final doubt…

DBCC TRACEON(3604)
DBCC PAGE('SampleTest',1,400,3) WITH TABLERESULTS
GO

If you scroll until the end you can see that the data rows are stored in slots that start with a zero offset.

And that’s it, the complex monster (aka Indexes), and its herculean relation with data pages ended, in this post, by being diminished for the world. Well, at least hopefully!

What’s next?

Above you can find a small explanation for the index behavior on SQL server, but if you are looking to dive deeper into the concept I can suggest you the reading of this post from Brent Ozar that is light reading and very explanatory of how indexes act. I can also suggest the reading of the following book that has one chapter fully dedicated to this subject. Good readings!

Stay safe!

Building a function | Powershell

ToolGBRMaker — Thu, 06 Jan 2022 17:33:24 +0000

Version: 5.1.x

During this post, I’ll share some concepts that I’ve used to build a function from a piece of code already shared on my blog in an earlier post. If you want to give it a look, please use the following link.

The piece of code that I’ll reuse and change to fit my function needs is:

#starting the stopped services or restarting the running ones
Get-Service -ComputerName 'PTPOPF247203' -Name '*Pharma*' |
Where-Object { 
    if ($_.Status -eq 'Running') {
        Write-Host 'Attempting to restart' $_.DisplayName 'with status' $_.Status -BackgroundColor DarkRed
        Restart-Service $_
    }
    elseif ($_.Status -eq 'Stopped') {
        Write-Host 'Attempting to start' $_.DisplayName 'with status' $_.Status -BackgroundColor DarkGreen
        Start-Service $_
    }      
}

The above code has the goal to restart executing services and start stopped ones.

Our new function…

Our function will have the name of Set-ServiceRunningStatus and will need as Parameters the ComputerName , Name , and Force. Regarding Name I’ll set this one as mandatory, to avoid a restart of all running services or the start of all stopped services, by mistake. Furthermore, the types used for these parameters are, by order, a string array, string, and a switch type, if the user wants a service restart no matter what’s.

function Set-ServiceRunningStatus{
    [CmdletBinding()]
    Param(
        [Parameter(ValueFromPipeline=$true)]
        [string[]]$ComputerName,
        [Parameter(Mandatory=$true,
            HelpMessage = "Enter a service name (or filter for multiple) to set Running Status")]
        [string]$Name,
        [switch]$Force
    )
    BEGIN { } #BEGIN
    PROCESS { } #PROCESS
    END { } #END  
}

So far so good, we have already our function structure. If you give a close look at our preliminary function structure you’ll be able to see that we have the BEGIN , PROCESS and END blocks. Let’s explain these input processing methods one by one:

BEGIN – Contains all the code that is needed to execute at the beginning of the function;
PROCESS – Contains the main functionality of the function;
END – Contains all the code that is needed to execute at the end of the function.

These methods can be read as follows – Begin to Process to end.

In our example won’t be strictly necessary to use all these blocks, but why not start to get used to them!?

Begin:

Let’s start with our BEGIN block, firstly I’ll enforce our coding rules in expressions, scripts, and script blocks (Set-StrictMode), afterward I’ll write a verbose to indicate in which step is our function and start to handle some of the service parameters to use on my cmdlets. These parameters will be dynamically set, because of the non-mandatory ones.

BEGIN {
  Set-StrictMode -Version 2.0

  Write-Verbose "[BEGIN] Starting: $($MyInvocation.Mycommand)"

  $serviceParam = @{ Name = $Name }
} #BEGIN

PROCESS:

Now, time to get into our PROCESS block, where the action will happens… By first, I’ll need to ensure that Parameter $ComputerName has been used and if so, I’ll add the $ComputerName to my $serviceParam. To accomplish that I’m using the automatic variable $PSBoundParameters. Also, take note that I’m switching the $ComputerName parameter in case of being called more than once when our function is being invoked.

if ($PSBoundParameters.ContainsKey('ComputerName')) {
  if($serviceParam.ContainsKey('ComputerName')){
    $serviceParam.ComputerName = $ComputerName
  } else {
    $serviceParam.add('Computername',$ComputerName)
  } #if already have
  Write-Verbose "[PROCESS] Getting Services for $($ComputerName)"
} else {
  Write-Verbose "[PROCESS] Getting Services"    
}#if param ComputerName

After, I’ve built my list of parameters to be used on my Services cmdlets, I’ll start by getting the services result list, using Get-Service cmdlet, and pipe it to my Where-Object, where I’ll check if it’s a service to restart or to start.

    PROCESS {
        if ($PSBoundParameters.ContainsKey('ComputerName')) {
            if($serviceParam.ContainsKey('ComputerName')){
                $serviceParam.ComputerName = $ComputerName
            } else {
                $serviceParam.add('Computername',$ComputerName)
            } #if already have
            Write-Verbose "[PROCESS] Getting Services for $($ComputerName)"
        } else {
            Write-Verbose "[PROCESS] Getting Services"    
        }#if param ComputerName

        Get-Service @serviceParam |
        Where-Object {

            Try {
                If ($_.status -eq 'Running') {
                    if ($Force) {
                        Write-Verbose "[PROCESS] Restarting Service $($_.Name) with -Force"
                        Restart-Service $_ -Force    
                    } else {
                        Write-Verbose "[PROCESS] Restarting Service $($_.Name)"
                        Restart-Service $_       
                    }#if force
                } elseif ($_.status -eq 'Stopped') {
                    Write-Verbose "[PROCESS] Starting Service $($_.Name)"
                    Start-Service $_    
                } #if status 
            } Catch {
                Write-Warning "[PROCESS] Attemp Failed for Service $($_.Name). Reason: $($_.Exception.message)"
            } #try

        } #Where-Object
    } #PROCESS

All PROCESS block

You should notice, by reading the code above, that besides normal Service cmdlet invocation, I’m also writing some verbose to keep the user aware of what is being done during PROCESS execution. Parallelly to that, I’m also checking if our function was been invoked with Force option, and if so, combined if it’s a restart, I’m forcing the cmdlet execution with force option.

END:

By last, on my end block, I’m only writing as a verbose mentioning the ending of my function execution.

    END {
        Write-Verbose "[END] Ending: $($MyInvocation.Mycommand)"
    } #END

And that’s it. Now I have a function, that will be available to reuse in multiple scenarios, like importing a new license (that demands a restart for the impacted services). Another example could be a start/restart service, at the end of the day, to ensure that the services get the last released code.

Examples of Execution:

This function will, by definition, execute the Get-Service cmdlet on your local machine and it’s optional the use of parameter $ComputerName , so only if you want to play it on a list of Computer Names, you’ll need to use it. A possible call would be…

'localhost' | Set-ServiceRunningStatus -Name '*Pharma*' -Verbose

I’ll use this function in future references to show you how we can use it in different tasks and also introduce the Automated testing on PowerShell. Keep aware of future posts.

Note: Besides internet references, I’ve also used the learning concepts acquired in the following book. I advise it as a good lecture.

Hint: On any doubts about cmdlets, always look for help on official internet pages, about those cmdlets, and from PowerShell helper.

Ps: I didn’t handle all possible function calls, so by now, this function is not bulletproof. If you detected the missing approach on it, please comment on this post. Good hunt!

Stay safe!