Making predictions

Steve Jones (@way0utwest), Grant Fritchey (@GFritchey), and I are often asked to predict trends in technology, especially at the end of the year. This year we were asked about the kinds of challenges and trends, especially for monitoring and DevOps, that will be seen for database professionals in the near future.

Just like the answer to everything is 42 or “it depends,” the answer to these questions, in my opinion, all involve the word “cloud.” Many organizations have taken advantage of cloud services for a decade, and more will follow.

The use cases for cloud computing are endless. For example, virtual machines in the cloud could be used for development, CI/CD pipelines, “lift and shift” of complete solutions, training, legacy database support (SQL Server 2008 R2!), and much more. While many VM solutions resemble those on-premises, there are also state-of-the-art serverless components for web applications, powerful machine learning and IoT (internet of things) offerings, DevOps platforms, and a plethora of database systems, to name a few.

The available technology choices are vast, and some offerings are especially useful for migrations or hybrid architectures. There are, however, many questions that must be asked when a company is thinking about moving production systems or sensitive data to the cloud. For example, how does the security infrastructure work for the chosen provider? Consider Azure which has Azure Active Directory that can extend to your on-premises directory, granular resource permissions with Role Based Access Control, and the ability to add guest accounts to give rights to someone outside your organization temporarily. A few other areas to research are high availability and disaster recovery options, scalability, and, of course, how to control costs.

Migrating databases to the cloud has its own set of challenges. Say that you want to stay with the SQL Server family of databases. You first must decide how much control you need (infrastructure as a service vs. platform as a service). Then scalability, performance, availability, and costs must be considered to choose the best SQL offering. You might want to check out the Azure Database Administrator Associate certification. Even if you are not interested in the certification, several comprehensive, free online classes associated with it cover all the Azure SQL options.

You also need to figure out which tools and services the providers offer to help you migrate. Microsoft Azure has the SQL Server Migration Assistant and Azure Database Migration Service to find compatibility issues and minimize service disruptions during the moves.

Once some or all databases are migrated, they still must be monitored. A solution that shows you both your on-premises and cloud databases in a “single pane of glass” like Redgate’s SQL Monitor can help simplify this complex situation and ease the transition. You can see the status of your entire estate at a glance, drill down to find the root cause of performance issues, and stay proactive with customizable alerts.

I predict that more database workloads will move to cloud platforms like Azure SQL over the next few years. Database professionals should prepare by learning about the options today because you never know when your job will change.

Commentary Competition

Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.

The post Making predictions appeared first on Simple Talk.

from Simple Talk https://ift.tt/3EBaCwL
via

Azure and MFA Secrets

MFA and conditional access policies are powerful tools for our cloud security, but they are full of tricks.

I don’t pretend to cover the basics here. You know you can create conditional access policies to request MFA authentication from the users. You also know the fact the default configuration (which you should avoid) will request MFA from everyone.

However, for the user to fulfill the requirement, the user needs to be registered for the MFA. There are some different ways to control the user registration for MFA.

It’s recommended to ensure the registration of the users for MFA before the conditional access policies jumps in and start requesting MFA from the users.

Direct Registration Requirement

On Active Directory -> Security -> Identity Protection -> MFA Registration Policy you can define the users who will be required to register a 2nd authentication method for MFA.

MFA give the  user 14 days for the registration before making it required. However, this will be a registrations of a single alternate method which by default is the Authenticator App.

The user has the option to choose a different method instead of the authenticator app, but not many users do so. A request for a new registration will never appear for the user again, or that’s what most people believe.

Self-Service Password Reset

It’s undeniable that SSPR is a very interesting feature, but what’s its relation to MFA registration?

SSPR requires MFA authentication methods. You can check the authentication methods required by SSPR on Active Directory -> Password Reset -> Authentication Methods

The enabled authenticator methods don’t includ the authenticator app  by default. You can include it, but it’s not by default.

On the item below, Active Directory -> Password Reset -> Registration the default configuration is to require every user registered for SSPR to register an MFA method on their next login and by default will be a different one than the default Authenticator App.

This becomes the second option the user has to register a secondary MFA method.

The Problem

It’s not very good the idea to depend on SSPR for the registration of a secondary MFA method. What happens if the user is in the middle of a trip and don’t have access to the main MFA methods registered? How could the user authenticate with alternate methods?

It’s not a good idea to have a single MFA method registered, how could you configure additional ones?

The Solution

There are two addresses the user can access directly to register additional MFA methods:

• First Option: https://aka.ms/mfasetup
• Second Option: https://aka.ms/setupsecurityinfo 

 

Conclusion

It may be a good idea for the IT team to make these addresses available to the users so they can control their own MFA registration methods and ensure that, when in trouble, they still have alternate options to authenticate on the systems.

The post Azure and MFA Secrets appeared first on Simple Talk.

from Simple Talk https://ift.tt/3sD9JRG
via

Quantifier predicates

SQL is based on set theory and logic. The only bad news is that many programmers have never had a class on either of those topics. They muddle through using the Boolean operators in their programming language and think that’s all there is to formal logic.

Let’s flashback to the early days of logic and play catch up. We need to start with syllogisms. Syllogisms are logical forms made up of combinations of two statements about classes of things that lead to a conclusion. They were invented by the Greeks and written up by Aristotle in Prior Analytics. You might have run into them, If you had a philosophy class that included lectures on formal fallacies. The three forms of statements allowed are:

1) ALL x ARE y

2) SOME x ARE y

3) NO x ARE y

These simple tools let us take two statements and come to a valid conclusion:

All men are mortal

All Greeks are Men

therefore: All Greeks are mortal

The Ancient Greeks seem to like to use a minimal set of tools which we could expect from a culture that gave us only circles and straight edges in plane geometry. We know that our logic was valid, so we are confident he will die if we know that Aristotle is a Greek. A polysyllogism, or a sorites, is a set of three or more such constructs that can be chained together to come to a conclusion.

You can figure out syllogisms or sorites using Euler circles, Venn diagrams, or Lewis Carroll’s diagrams. And before you ask, yes, it is that Lewis Carroll. People tend to forget that he was one of the pioneers in the evolution of logic. You don’t hear more about him when you’re studying early logic because he was on the losing side of a philosophical argument called “existential import” in the literature. Briefly, this has to do with what “ALL x ARE y” means. Does it imply that “SOME x ARE y” and that “NO x ARE non-y”; that is, there are x’s in the universe. Or does it mean only that “NO x ARE non-y”? I will get back to this point shortly.

George Boole and his book “Laws of Thought” (available in reprint additions to this very day) gave us the Boolean algebra that dominates programming languages with the basic AND, OR, and NOT operators we’ve come to know and love over the decades. Our syllogisms mutated into quantifiers which could be applied to the Boolean algebra expressions. The two forms are For all x, P(x), and For some x, P(x). If you want to look up formulas in a textbook, the symbol for the universal quantifier is ∀, an inverted letter ‘A’, and the symbol for the existential quantifier is ∃, a rotated letter ‘E’.

Existential import lost the battle, and the modern convention is that “All men are mortal” has the same meaning as “There are no men who are immortal” but does not imply that any men exist at all.

The reasoning is that if I were to walk into a bar and announce that I can beat any pink elephant in the bar, that would be a true statement. The fact that there are no pink elephants in the bar merely shows that the problem is reduced to the minimum case. If this still seems unnatural, then consider the formal mathematical properties of these quantifiers:

1) (∀x)P(x) = ¬(∃x) ¬P(x)

2) (∃x)P(x) = ¬(∀x) ¬P(x)

In Boolean terms, you can think of the (∀x) quantifier as being a generalized AND which connects an infinite string of propositions. Likewise, you can think of the (∃x) quantifier as a generalized OR, which connects an infinite string of propositions. These generalizations are actually taken from DeMorgan’s Laws.

The EXISTS() predicate

SQL has had the EXISTS() predicate since the beginning. It’s interesting within SQL because it has to return either TRUE or FALSE but can never return UNKNOWN. This is an exception to most logical operations inside SQL’s three-valued logic. It’s also not quite the same as the syllogisms we have been talking about.

Consider the statement “Some salesmen are liars,” and one way we would write it with the EXISTS() predicate in SQL:

...
WHERE EXISTS (SELECT *
 FROM Personnel AS P, Liars AS L
 WHERE P.job_title = 'Salesman'
 AND P.emp_name = L.emp_name);

If we are more cynical about salesmen, we might want to formulate the predicate “All salesmen are liars” with the EXISTS predicate in SQL, using the transform rule just discussed:

...
WHERE NOT EXISTS
 (SELECT *
 FROM Personnel AS P
 WHERE P.job_title = 'Salesman'
 AND P.emp_name
  NOT IN
  (SELECT L.emp_name
  FROM Liars AS L));

This informally says “There are no salesmen who are not liars” in English. In this case, the IN() predicate can be changed into a JOIN, which might improve performance and be a bit easier to read.

As an aside, the subquery doesn’t need to evaluate the SELECT clause for the EXISTS() predicate to work. In one very early version of Oracle, however, the compiler would work out the expressions in the SELECT clause and then ignore the results. Today, the preferred form is SELECT * because the star represents a nonspecific row in several places in SQL. It is easy to find in the text, and the compiler will not waste any time with expressions in the SELECT list (in fairness, this optimization should not be a problem anymore).

Now, onto the fancy stuff!

The [SOME | ANY] <subquery> predicate

The predicate <value expression> <comparison op> [ANY | SOME] <table expression> is equivalent to taking each row of the table expression, si, (assume that they are numbered from 1 to n) of <table expression> and testing <value expression> <comparison op> si with ORs between the expanded expressions:

((<value expression> <comparison op> s1)
OR (<value expression> <comparison op> s2)
 ...
OR (<value expression> <comparison op> sn))

When you get a single TRUE result, the whole predicate is TRUE. As long as <table expression> has cardinality greater than zero and one non-NULL value, you will get a result of TRUE or FALSE. The keyword SOME is the same as ANY, and the choice is just a matter of style and readability.

If you think about it, the IN() predicate is equivalent to a simple = ANY predicate. In fact, that is how it is defined in the ANSI/ISO Standard. But the <comparison op> can be any of =, <, >, <>, <=, >=, and (in theory, but not always in practice) any other comparison operator (such as IS [NOT] DISTINCT FROM).

The ALL <subquery> predicate

The <value expression> <comp op> ALL <table expression> takes each row of <table expression>, call them s1, s2,.. sn, builds a search condition for each such row expression, tests <value expression> <comp op> si with ANDs between the search conditions, thus:

((<value expression> <comp op> s1)
AND (<value expression> <comp op> s2)
 ...
AND (<value expression> <comp op> sn))

When you get a single FALSE result, the whole predicate is FALSE. As long as <table expression> has cardinality greater than zero and all non-NULL values, you will get a result of TRUE or FALSE.

That sounds reasonable so far. Now let Empty_Table be an empty table (no rows, cardinality zero) and Null_Table be a table with only NULLs in its rows and a cardinality greater than zero. The rules for SQL say that <value expression> <comp op> ALL Null_Table always returns UNKNOWN, and likewise <value expression> <comp op> ANY Null_Table always returns UNKNOWN. This makes sense because every row comparison test in the expansion would return UNKNOWN, so the series of OR and AND operators would behave in the usual way.

However, <value expression> <comp op> ALL Empty_Set always returns TRUE and <value expression> <comp op> ANY Empty_Set always returns FALSE. Most people have no trouble seeing why the ANY predicate works that way; you cannot find a match, so the result is FALSE. But most people have trouble seeing why the ALL predicate is TRUE. We are trying to preserve those generalized DeMorgan’s Laws.

The Foobar.x <comp op> ALL (SELECT y FROM Table2 WHERE <search condition>) predicate converts to:

... NOT EXISTS
 (SELECT *
 FROM Foobar, Table2
 WHERE Foobar.x <comp op> Table2.y
 AND NOT <search condition>)...

The Foobar.x <comp op> ANY (SELECT y FROM Table2 WHERE <search condition>) predicate converts to

... EXISTS
 (SELECT *
 FROM Foobar, Table2
 WHERE Foobar.x <comp op> Table2.y
 AND <search condition>) ...

Of the two quantified predicates, the <comp op> ALL predicate is probably the more useful of the two since it cannot be written in terms of an IN() predicate. The trick with it is to make sure that its subquery defines the set of values in which you are interested. For example, to find the authors whose books all sell for $49.95 or less, you could write:

SELECT *
 FROM Authors AS A1
 WHERE 49.95
 <= ALL (SELECT book_price
 FROM Books AS B1
 WHERE A1.author_name = B1.author_name);

The best way to think of this is to reverse the usual English sentence “Show me all x that are y” in your mind so that it says “y is the value of all x” instead.

The ALL predicate and Extrema functions

At first, it is counter-intuitive that these two predicates are not the same in SQL:

x >= (SELECT MAX(y) FROM Foobar)
 x >= ALL (SELECT y FROM Foobar)

You have to remember the rules for the extrema functions – they drop out all the NULLs before returning the greatest, least, or computed value the aggregate function wants. But the ALL operator does not drop NULLs so that you can get them in the results. (Editor’s note: If any NULLs are returned from the subquery, no rows are returned from the main query with ALL. While the ALL operator is not dropping NULL, NULL doesn’t show up in the results because the expression >= NULL will return UNKNOWN.)

The first expression uses an aggregate function, so it returns a numeric value. In effect, it works like this:

0) Create the data

CREATE TABLE Foobar
(foo_id VARCHAR(10) NOT NULL PRIMARY KEY,
 y INTEGER);
INSERT INTO Foobar VALUES ('Albert', 42), ('Bob', NULL), ('Chuck', 43);

1) We can now apply the aggregate function. Remove the rows with NULLs.

(‘Albert’, 42)

(‘Chuck’, 43)

2) Find the maximum value in the column of y in the remaining rows.

{(42), (43)} => (43) and finally x>= (43)

But look at the second expression. That ALL() predicate makes this a logical expression. Bob’s NULL is not removed as it would be with an aggregation. Instead, we build a chain of AND-ed predicates:

((‘Albert’, 42)

AND (‘Bob’, NULL)

AND (‘Chuck’, 43))

Which becomes in effect:

((42 >= x)
AND (NULL >= x) --- UNKNOWN
AND (43 >= x))

SQL’s three-valued logic comes into play here. We know that

TRUE AND UNKNOWN = UNKNOWN

FALSE AND UNKNOWN = FALSE

UNKNOWN AND UNKNOWN = UNKNOWN

As you can see, once you’ve got an UNKNOWN value in a chain of AND-ed predicates, you really can’t get it to resolve to a TRUE value. You would have to cast them to a known value, using COALESCE() or some similar function.

Conclusion

It’s always a good idea to take a little time out and learn some of the more obscure features in this very large language we call SQL. But when you do, have mercy on the poor guys that have to maintain your code after you’re gone. This is what comments are for.

The post Quantifier predicates appeared first on Simple Talk.

from Simple Talk https://ift.tt/3euAxeI
via

Case Study: Troubleshooting Site Recovery

A silly mistake, a site recovery error and a troubleshooting case study, let’s check how it happened.

I was demonstrating Site Recovery in a training. Site recovery is a slow task, so I make the demonstration among other explanations, put the demonstration in the middle of other subjects.

This also doesn’t leave much room to research about problems. On this blog, I will mention a mistake i did and how I solved it.

The silly mistake

I started the Site Recovery demonstration but the virtual machines were deallocated when I started – or at least I believe this was the root cause.

Result: The Site Recovery jobs failed.

 

First Solution Attempt

Well, the jobs were still there, but the replication failed. Now with the machines turned on, let’s try the jobs again. It’s only a matter of selecting the jobs and asking to Restart.

Result: Failed again.

 

Second Solution Attempt

Since repeating doesn’t work, let’s remove and add again. I used “Replicated Items” menu on the recovery services vault and removed the failed virtual machines.

Result: When trying to enable site recovery again, the virtual machines where not available anymore. It was not possible to select them.

Third Solution Attempt

Something was left behind and preventing the virtual machines to appear again for the site recovery.

I checked the extensions on each virtual machine and there it was: The site recovery extension, present on both machines. I uninstalled it and tried again.

 

 

Result: No virtual machine visible

Fourth Solution Attempt

I discovered the replication process also leaves a relation between the two virtual networks registered inside the recovery services vault. Even if the remote virtual network was already dropped, the relation is there and prevents the virtual machines to appear.

Inside the recovery services vault, we use Site Recovery Infrastructure=> For Azure Virtual Machines => Network Mapping and we can remove the link between the virtual networks.

 

Result: No virtual machine visible

Final Solution

After digging a lot inside troubleshooting articles, I discovered on GitHub a powershell script capable to remove all the remaining site recovery configuration from the virtual machine.

I saved the script inside a cloud shell file share, executed it for each virtual machine and all what had left from the site recovery was gone.

Result: Finally the virtual machines were available for the site recovery.

Conclusion

Removing a replication leaves a lot of garbage behind. Unfortunately this was not the first time I saw a process like this leaving garbage behind, but this time I was able to track it down.

It’s not only about the final solution, probably you will need to execute all or many of the steps here to reach this goal.

The post Case Study: Troubleshooting Site Recovery appeared first on Simple Talk.

from Simple Talk https://ift.tt/32bgwas
via

How to filter DAX for paginated reports

The series so far:

1. Introduction to DAX for paginated reports
2. How to filter DAX for paginated reports

    

In the previous article of this short series, you learned the fundamentals of creating datasets using DAX to populate paginated reports delivered using the Power BI Premium service. The next step is to appreciate the practicalities – and subtleties – of how data can be filtered using DAX for paginated report output.

As most, if not all, report developers come from an SQL background, it may seem overkill to devote an entire article to filtering data. However, DAX is very unlike SQL as far as filtering output data is concerned. Something as simple as classic OR logic needs to be handled differently from the techniques you may be used to – either as a SQL or as a Power BI developer. To ensure that you can deliver the report data that you need to populate paginated reports, take a detailed look at how to filter data in DAX datasets using the core SUMMARIZECOLUMNS() function.

Like all the articles in this series, this article uses a sample dataset named CarsDataWarehouse.Pbix that you can download and use as the basis for practicing and testing DAX data output techniques. If you wish to understand the dataset structure, please refer to the first article in the series.

Basic Data Output to SSRS Using SUMMARIZECOLUMNS()

One positive thing that I can say about the Power BI Report Builder Query Designer is that it introduces you to the core DAX function that you are likely to use when developing DAX queries for paginated reports: SUMMARIZECOLUMNS().

SUMMARIZECOLUMNS() is probably the DAX function that most people generally use to produce tables of data – which makes it an ideal function to query Power BI dataset data for SSRS reports. By a happy coincidence, Report Builder also uses the function to generate DAX. Consequently, it is the default option for report developers who need to use DAX data sources.

SUMMARIZECOLUMNS() is so powerful because it allows you to assemble, filter, and aggregate data from a Power BI dataset into a single tabular output for paginated reports. Indeed, it requires that you apply these elements in a specific order inside the SUMMARIZECOLUMNS() function:

1. A list of fields from the data model
2. Any required Filters
3. Any required Aggregations

It is worth noting that parts (2) & (3) in this list are optional. You do not have to filter data or add aggregations and can simply use SUMMARIZECOLUMNS() to output the list of data you need. This means that any of the following approaches will work.

Column List Only

Suppose all you want to do is return a list of columns to a paginated report. In that case, you can manually write code like the following DAX snippet developed in DAX Studio that you then test and subsequently copy and paste into Power BI Report Builder. Of course, when I say “manually” write code, I mean search for the fields, drag and drop them into the query pane, and use autocomplete to enter any DAX keywords.

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]
,DimVehicle[Model]
)

The output from this short piece of DAX is (in a shortened form):

While looking at core DAX for report output, it is perhaps worth a quick detour to explain the EVALUATE function. This function is the DAX statement needed to execute a query and return data. In simple DAX queries, all that you need to do is to start the query with the keyword EVALUATE. As queries become more intricate (and as you will discover in the course of these four articles), this initial approach can be extended to handle more complex requirements.

There are a couple of points that it is worth noting at this juncture:

• SUMMARIZECOLUMNS() can return data from all the tables in the underlying dataset.
• A well thought out data model is a fundamental prerequisite.
• All field references must be fully qualified (table and column name).

Column list and Expressions

If you need to output not only a set of columnar data but also add some DAX calculations (which includes any measures in the underlying dataset), you will need to write code like this:

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]
,DimVehicle[Model]
,"Total Additional Costs"
, SUM(FactSales[Total Additional Costs])
,"Gross Profit", [Gross Profit]
,"Average Cost", AVERAGE(FactSales[CostPrice])
,"Sales", SUM(FactSales[SalePrice])
)

The shortened output from this piece of DAX is ideally suited to a paginated report table:

It is important in this case to respect the following constraints:

• Always place DAX calculations (which includes measures already in the dataset) after “plain” data columns.
• Calculated columns in the source data (such as the field “Total Additional Costs” in this example) are considered to be data columns. As such, they do not need a specific column name added (as is the case for measures). However, they do need an aggregation function applied.
• Expressions can be measures in the underlying dataset (like the measure “Gross Profit” in this example) or specific DAX calculations – such as the measure “Average Cost” in this example.
• You have to add a name for each calculation or measure before the measure or calculation itself. This name will become the basis for the name used in the output for the respective column.
• Measure/calculation names must be entered in double quotes and separated from the actual measure or calculation by a comma.
• Measures (such as [Gross Profit] in the previous code snippet) do not need to be fully qualified. Nor – as they are measures – do they need an aggregation function applied.
• You can mix measures from the dataset with calculations in any order provided that they follow the definition of standard data columns. That is, they must appear after the columns that are extracted.

The calculations can, of course, get much more complex than these simple examples.

Note:

The output shown is from DAX Studio. If you use Power BI Report Builder to test the DAX you will notice that the column names are changed in the output and any calculations are preceded by ID_ – as shown in the following figure:

Column list and Filter

SUMMARIZECOLUMNS() also lets you add filters to reduce the number of records output to SSRS. You can see a simple example in the following code snippet:

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]
,DimVehicle[Model]
,DimGeography[CountryName]
,FILTER(
        VALUES(DimGeography[CountryName])
        ,DimGeography[CountryName] = "France")
,"Sales", SUM(FactSales[SalePrice])
)

The shortened output from this piece of DAX is:

The points of note in this simple example are:

• Any filters have to be added after columns of data and before the measures or calculations.
• Simple filters use the FILTER() function and specify:
• A table to filter (VALUES() is used to return a unique list of elements)
• The expression to be evaluated for each row of the table must equate to True or False for each row.

You do not need to output any columns used to filter data. I added the CountryName column in this code snippet only to show that the filter was working.

Column List, Filter and Aggregation

Finally, columns, filters and expressions can be combined in a SUMMARIZECOLUMNS() expression like this:

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]     // Data (Column)
,DimVehicle[Model]    // Data (Column)
,DimGeography[CountryName]
,FILTER(
        VALUES(DimGeography[CountryName])
        ,DimGeography[CountryName] = "France")  // Filter
,"Sales", SUM(FactSales[SalePrice])   // Calculation
,"Average Cost", AVERAGE(FactSales[CostPrice]) // Calculation
,"Profit", [Gross Profit]   //  Measure
)

The shortened output from this piece of DAX is:

The key takeaway here is to respect the order of the three parts of the full SUMMARIZECOLUMNS() expression:

• Firstly – the columns of data you wish to return to SSRS.
• Secondly – Any filters
• Thirdly – Any expressions (measures from the dataset or calculations)

Filtering Approaches

Assembling output – directly from columns, existing metrics, or DAX calculations – is nearly always the easy part. Filtering the source data so that you only see exactly what you want and need is frequently where the challenges lie. This is often the case if you are new to DAX.

Consequently, the next thing to look at in greater depth is how to filter data in more complex ways inside the SUMMARIZECOLUMNS() function.

Elementary AND logic

It is worth noting that, inside the SUMMARIZECOLUMNS() function, all filters are logical AND filters – so the following DAX query returns cars sold in France that are black:

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]
,DimVehicle[Model]
,DimVehicle[Color]
,DimGeography[CountryName]
,FILTER(
        VALUES(DimGeography[CountryName])
        ,DimGeography[CountryName] = "France")
,FILTER(
        VALUES(DimVehicle[Color])
        ,DimVehicle[Color] = "Black")
,"Sales", SUM(FactSales[SalePrice])
)

The shortened output from this piece of DAX is:

You do not need to add an AND keyword anywhere inside the DAX. Merely having two filters creates a “logical AND” clause. It is also worth remembering that you can add any number of filters in this way. It is also worth noting that you do not have to add the fields used in the FILTER() function to the output – I am doing this here simply to show that the filter works.

Note:

I am including the filter columns in the output so that it is clear that the filters work. Obviously, this affects the aggregated output. So I want to make it clear that this is not necessary (rather like you do not have to place any fields used in a SQL WHERE clause in the SELECT clause) and is only done here to show that the filter works.

Elementary OR logic

OR logic can be slightly more challenging in DAX, depending on the complexity of the query requirement. If all you want to do is to filter between only two alternatives for the same field, then there is a simple solution – the DAX OR() clause, applied like this:

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]
,DimVehicle[Model]
,DimVehicle[Color]

,FILTER(
        VALUES(DimVehicle[Color])
        ,OR(DimVehicle[Color] = "Black"
            ,DimVehicle[Color] = "Blue"))
,"Sales", SUM(FactSales[SalePrice])
)

The shortened output from this piece of DAX is:

If you want to apply multiple alternatives, you can nest OR() clauses (although this can get a little clunky). A cleaner option is to use the || operator in this way:

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]
,DimVehicle[Model]
,DimVehicle[Color]
,FILTER(
        VALUES(DimVehicle[Color])
        ,DimVehicle[Color] = "Black"
         || DimVehicle[Color] = "Blue" 
         || DimVehicle[Color] = "Green")
,"Sales", SUM(FactSales[SalePrice])
)

The shortened output from this piece of DAX is:

A more elegant solution to multiple alternatives (still for the same field) is to use the DAX row constructor (IN) using IN {“”, “”, “”} rather than the OR operator, as you can see from the following code snippet.

EVALUATE
SUMMARIZECOLUMNS
(       
 DimVehicle[Make]
,DimVehicle[Model]
,DimGeography[CountryName]
,FILTER(
        VALUES(DimGeography[CountryName])
        ,DimGeography[CountryName] IN {"France", "United States",
                                       "Belgium"})
,"Sales", SUM(FactSales[SalePrice])
)

The shortened output from this piece of DAX is:

All you have to do here is add the comma-separated list of elements to filter on inside curly braces after the IN keyword.

Date Filters

Dates can be used simply and easily in filters. The main points to remember are that:

• Dates must be entered in a recognised date format.
• As dates are entered as text, they have to be wrapped in the VALUE() function to convert them into a date datatype that can be understood by DAX.

The following code snippet shows a simple date filter:

EVALUATE
SUMMARIZECOLUMNS(
 DimCLient[ClientName]
,DimVehicle[Make]
,DimVehicle[Model]
,DimDate[DateKey]
,FILTER(
        VALUES(DimDate[DateKey])
        ,DimDate[DateKey] >= VALUE("01/01/2020"))
,"Sales", SUM(FactSales[SalePrice])
)

The shortened output from this piece of DAX is:

Date ranges merely extend the approach that you just saw, using the AND function:

EVALUATE
SUMMARIZECOLUMNS(
 DimCLient[ClientName]
,DimVehicle[Make]
,DimVehicle[Model]
,DimDate[DateKey]
,FILTER(
        VALUES(DimDate[DateKey])
        ,AND(DimDate[DateKey] >= VALUE("01/01/2020")
        ,DimDate[DateKey]<= VALUE("12/30/2022"))
       )
,"Sales", SUM(FactSales[SalePrice])
)

In this case the output is like this:

Or, if you prefer, the AND operator (&&) (which gives the same result so I will not repeat the display of the output):

EVALUATE
SUMMARIZECOLUMNS(
 DimCLient[ClientName]
,DimVehicle[Make]
,DimVehicle[Model]
,DimDate[DateKey]
,FILTER(
        VALUES(DimDate[DateKey])
        ,DimDate[DateKey] >= VALUE("01/01/2020")
         && 
         DimDate[DateKey]<= VALUE("12/30/2022")
       )
,"Sales", SUM(FactSales[SalePrice])
)

SQL “LIKE” in DAX

If you are an SSRS developer looking for the SQL LIKE operator in DAX queries, then it is worth noting that DAX has two functions that you will undoubtedly find useful. They are:

CONTAINSSTRING

CONTAINSSTRINGEXACT

The former can be applied to filter data where a string is found inside a field (regardless of the capitalisation). The latter is a case-sensitive text filter.

You can filter data to return records containing a given string using code like that shown below:

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]
,DimVehicle[Model]
,DimCLient[ClientName]
,FILTER(DimCLient, CONTAINSSTRING(DimCLient[ClientName], "Bling"))
,"Total Sales", SUM(FactSales[SalePrice])
)

The output from this short piece of DAX is:

It is worth noting that you simply replace CONTAINSSTRING with CONTAINSSTRINGEXACT to apply case-sensitivity in the filter.

NULL handling

Power BI datasets, like most data sources, can contain NULL (or empty) elements. You need to be able to filter out records where certain fields contain NULLS. This is done using the DAX ISBLANK() function, like this:

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]
,DimVehicle[Model]
, DimVehicle[ModelVariant]
,FILTER(DimVehicle, ISBLANK(DimVehicle[ModelVariant]))
,"Total Sales", SUM(FactSales[SalePrice])
)

The shortened output from this short piece of DAX is:

Boolean Filters

If your Power BI dataset contains Boolean fields, you can apply a Boolean filter to exclude records like this:

EVALUATE
SUMMARIZECOLUMNS
(
DimCLient[ClientName]
,DimCLient[IsReseller]
,FILTER(VALUES(DimCLient[IsReseller]), NOT DimCLient[IsReseller])
,"Total Sales", SUM(FactSales[SalePrice])
)

The shortened output from this short piece of DAX is:

To display clients where the IsReseller field is True, you would use DAX like this:

FILTER(VALUES(DimCLient[IsReseller]), DimCLient[IsReseller])

Sorting Data

To conclude the whirlwind tour of basic data retrieval (developed further in the next article), you need to know how to sort the output data. This is as simple as adding ORDER BY at the end of the DAX, like this.

EVALUATE
SUMMARIZECOLUMNS
(
 DimVehicle[Make]
,DimVehicle[Model]
,DimGeography[CountryName]
,FILTER(
        VALUES(DimGeography[CountryName])
        ,DimGeography[CountryName] IN {"France", "United States",
                                       "Belgium"})
,"Total Sales", SUM(FactSales[SalePrice])
)
ORDER BY DimVehicle[Make]

The partial output from this short piece of DAX is:

It is worth noting that:

You can define the sort order using the ASC and DESC keywords. So, to sort the previous output in descending order, the ORDER BY clause would read: ORDER BY DimVehicle[Make] DESC

You cannot sort on a column that is not returned by the SUMMARIZECOLUMNS()function. In other words, you have to include the column to sort on in the output. However, in SSRS reports, the column used for sorting lists is virtually always displayed, in my experience. In any case, you do not have to display a column that you return to an SSRS dataset, so this is not usually a problem. It merely increases the size of the dataset returned to the report.

Conclusion

In this article, you have seen many of the core techniques that you can apply when filtering source datasets that feed into paginated reports. This ranged from simple AND filters through OR filters to NULL handling – and, of course, sorting data. The third article in this series will look in-depth at using the various DAX table functions to help you create paginated reports from a Power BI dataset.

The post How to filter DAX for paginated reports appeared first on Simple Talk.

from Simple Talk https://ift.tt/3IVNSuH
via

A momentous year for Redgate Software

December is often a time of reflection, and this year has been more unusual than most. It’s been another year of Zoom calls, still, there have also been signs of recovery and getting back to normal with increased travel, family reunions, the reopening of pubs and restaurants, and some in-person events. This year will also be known as the year of the “Great Resignation” as employees expect more from their jobs or rethink their careers. The world isn’t back to normal and, possibly, will never completely go back to the way things were.

This year has also been a big year for Redgate. Early in the year, Redgate announced some exciting news: the organization had acquired the recordings, URLs, and trademarks from the PASS organization, including SQLSaturday. PASS ceased operations in January 2021 after hosting the yearly premier data platform conference and being the home of the community for over 20 years. By buying the assets, Redgate ensured that the community would continue to “connect, share, and learn.” When I heard about the purchase, I knew that no other company could be trusted more than Redgate to care for these assets properly.

Soon after the acquisitions, Redgate set to work organizing the recordings and making them available for anyone to view, including those previously hosted behind a paywall. They also donated the SQL Saturday assets to the newly formed non-profit foundation to continue this popular local event. These were both significant, but there was more news on the way.

In April, Redgate’s Annabel Bradford announced that the 2021 PASS Data Community Summit would be held virtually in November. The event turned out to be a fantastic success with over 18,000 registrations, more than 400 sessions, $18,000 donated to charity from Redgate and AWS pre-cons, and a more diverse speaker lineup than the Summit has ever seen. However, the big star of the week was the Spatial.chat software used to provide an almost in-person feel. Attendees could join rooms based on interest, such as DE&I, karaoke, and more. Many said that the experience had that “hallway conversation” feel, and we heard from several folks on Twitter that it was the “best Summit ever!” Plans are underway for a hybrid Summit in 2022, and you can sign up for news about the event now.

There was more big news from Redgate in 2021. Simon Galbraith, co-founder and CEO, has decided to step back, and Jakub Lamik has taken over as the new CEO. Jakub was the Chief Product Officer for Redgate for several years and will guide the future of Redgate as it continues to grow.

Like any other year, Redgate invests in its software offerings. I’m especially excited about the new capabilities in SQL Monitor’s Estate Pages. On the DevOps front, Redgate combined Flyway deployment technology with Redgate version control and automation in the new Flyway Desktop. The product is available for Oracle and in preview for SQL Server with plans to support even more RDBMS in the future.

This year has been a momentous one for Redgate, and I look forward to what’s next.

Commentary Competition

Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.

The post A momentous year for Redgate Software appeared first on Simple Talk.

from Simple Talk https://ift.tt/3GQMkQR
via

Azure Policies and Management Groups

When I first started speaking about cloud technologies over a decade ago, the most common question I heard was around just how secure the platform was. Security was top of mind for people when all of their data resided in data centers that they owned and maintained up to that point. Almost everyone that had access to their data worked directly for the company. They controlled where the data lived, who had access to it, and even who was running the networking cables the data traveled across. As cloud technologies advanced and became more mainstream, fewer questions came up about security. That doesn’t mean that people aren’t still concerned about security; it just means that there are many more answers to the questions and a good deal of options on how to secure your data. This article explains how Azure Policies and Management Groups can simplify Azure SQL security.

Features like Always Encrypted, Transparent Data Encryption (TDE) with optional customer-managed keys, private endpoints, and auditing are just a few of the many options available to secure data stored in Azure SQL Database and Azure SQL Managed Instance. Having the options is one thing, but ensuring they are used is another, especially when many databases are scattered across multiple subscriptions. Each organization must decide which options they are going to implement and then not only put them in place but also ensure that they aren’t circumvented or modified later. This is where tools in Azure such as Azure Policy and Management Groups come in handy.

What is Azure Policy?

At its core, Azure Policies are defined rules that you create to enforce certain settings or configurations in your Azure deployed resources. You create a policy and then apply that policy to some scope, such as a subscription or resource group. The Azure Platform will enforce that policy against all resources within the assigned scope. Enforcement could mean that the Azure platform flags a resource that is not meeting the policy as non-compliant so that you can remediate the issue, or it could mean that the platform simply refuses to deploy a resource that doesn’t meet the policy. You have a lot of control over how the platform enforces your selected policies.

For Azure SQL Database and Azure SQL Managed Instance, there are a ton of scenarios where having policies to enforce your security approach will be extremely helpful. Some ideas are:

• Restricting the physical locations that databases are stored in order to meet data sovereignty requirements or simply to limit where your databases are located. You can restrict your databases to only be created in specific Azure regions, for example.
• Ensure that Private Endpoint connections are configured so that you don’t have a database accessible directly through the public endpoint.
• Enforcing customer-managed keys for encrypting your data at rest if you choose to use that option.
• Enforcing auditing to be turned on.
• Enforcing that an Azure Active Directory administrator is assigned at the server level.

Essentially, almost anything that you can define as part of your deployment can be placed into a policy for the platform to enforce, or at least report when you have non-compliant databases.

Here is an example of a policy that ensures that an Azure Active Directory administrator is provisioned for Azure SQL Database servers. This particular policy is one that Microsoft provides as a built-in policy to choose from, which is why you see the policyType set to BuiltIn.

{
  "properties": {
    "displayName": "An Azure Active Directory administrator should be provisioned for SQL servers",
    "policyType": "BuiltIn",
    "mode": "Indexed",
    "description": "Audit provisioning of an Azure Active Directory administrator for your SQL server to enable Azure AD authentication. Azure AD authentication enables simplified permission management and centralized identity management of database users and other Microsoft services",
    "metadata": {
      "version": "1.0.0",
      "category": "SQL"
    },
    "parameters": {
      "effect": {
        "type": "String",
        "metadata": {
          "displayName": "Effect",
          "description": "Enable or disable the execution of the policy"
        },
        "allowedValues": [
          "AuditIfNotExists",
          "Disabled"
        ],
        "defaultValue": "AuditIfNotExists"
      }
    },
    "policyRule": {
      "if": {
        "field": "type",
        "equals": "Microsoft.Sql/servers"
      },
      "then": {
        "effect": "[parameters('effect')]",
        "details": {
          "type": "Microsoft.Sql/servers/administrators"
        }
      }
    }
  },
  "id": "/providers/Microsoft.Authorization/policyDefinitions/1f314764-cb73-4fc9-b863-8eca98ac36e9",
  "type": "Microsoft.Authorization/policyDefinitions",
  "name": "1f314764-cb73-4fc9-b863-8eca98ac36e9"
}

A policy is made up of some metadata, parameters, and then the actual rule. In this example, the rule is checking to see if the settings for Microsoft.Sql/servers/administrators are set on any resource that is of type Microsoft.Sql/servers. The policy provides a parameter named effect. When you apply the policy to a scope, you can supply a value for the parameter to indicate how the system should respond if the administrator settings are provided or not. The options for the parameter in this policy are constrained by allowedValues and are defined as either Disabled (which means no action is taken) or AuditIfNotExists (which means the resource will be flagged as non-compliant). By providing you the ability to set the parameter in this way, you can toggle the action of this policy to be enforced or not without having to remove the policy entirely from a scope if you need to turn it off for a short time.

Here is a screenshot from the Azure Portal showing a database that is non-compliant with the defined policy above. Note that the deployed system has two Azure SQL Servers, but only one of them has Azure Active Directory Administrators assigned, which is why you see a 50% compliance. The screen is currently filtered to only show you the non-compliant resources.

Image: Azure Portal showing non-compliant resources for defined policies

To learn more about Azure Policy, including how to create your own, check out the documentation provided by Microsoft. The documentation details how to create policies, deploy them, and monitor the compliance status.

You can end up with many different policies defined to create the rules you want to be enforced in your organization. To help organize these policies, you can create a collection of policies into what is known as an Initiative (also called a policySet). By grouping policies into an initiative, you can simplify the assignment and administration of your chosen rules. Like with a single policy, you can assign an initiative to a scope, and then all rules assigned to that initiative will be enforced across that scope.

What are Management Groups?

A management group is a mechanism for organizing your Azure subscriptions. Each management group can contain zero or more Azure Subscriptions, and the management groups can be hierarchical so that you can nest management groups within other management groups. Management Groups allow you to create a collection of subscriptions that can then be managed in a like manner without having to go to each subscription. For example, you can assign role-based access control permissions at a management group level, and all subscriptions beneath that group will inherit those permissions.

By default, there is an implicit root management group on all Azure Active Directory tenants. You can then add new management groups, including nesting them, under the root in order to organize your subscriptions in a manner that makes sense for your company. This capability lets you group subscriptions by deployment environment (like dev, test, or production), region, department, or something completely bespoke to meet your needs. A subscription can only belong to one management group at a time. However, remember that you can nest the management groups.

Management Groups can be a selected scope for Azure Policies and Initiatives which means you can assign a policy in one place, such as the root management group, and ensure that it is implemented across all your subscriptions. You can also have policies assigned at specific levels in your management group hierarchy so that the rules are only applied to subscriptions within that management group and down the hierarchy from there.

There is a lot of power in using management groups. If you are not familiar with them, you can read more in the Microsoft documentation. You can find tutorials and more in the docs, as well as some guidance on organizing your management groups effectively in the Cloud Adoption Framework articles.

Putting Azure Policy into action

Much like any project you work on, the first step in getting a handle on your security and governance is to gather requirements. If your organization does not currently have any rules around securing your data, then now is the time to sit down and get them defined. If you already have rules but are doing manual enforcement, now is the time to look at what you can automate using Azure Policy and Management Groups. Since you can have a policy around almost any aspect of a deployment, the possibilities are nearly endless, and it may take some time to determine what all makes sense for you to put into place.

In addition to defining the policies and initiatives you will enforce, you will also need to decide how they will be applied to your subscriptions. If you weren’t already using Management Groups, you’d want to spend some time thinking through the organization of your subscriptions. This task can potentially be trickier than it sounds and not always straightforward. You may find that some subscriptions are easily grouped while others are outliers that need special consideration.

As you are gathering your plans, here are some suggestions that you may wish to take into consideration:

• Different environments, such as development, test, preview, or production, may have other policy requirements, so use subscriptions to help mitigate this. You could also use tags if you need to mix environments within a subscription; however, if you do this, you need to enforce the use of tags of specific values. It is also suggested to enforce the use of a default tag to have the most stringent policy for fail-safe so that any resources deployed without specific tags defined end up being locked down the hardest until someone makes a conscious decision on what should be applied to it.
• Make sure to source control your policies and automate them as much as possible. Look at tools such as scripts using the PowerShell Az module, Azure CLI, terraform, or ARM.
• Document your policies to detail why each one is required and at what scopes. Documentation is beneficial if you are a Software-as-a-Service (SaaS) provider to answer security questions from your customers who will want to know how their data is being handled.
• While Azure Policy is powerful, not all things can be completely enforced via policy. For example, Azure SQL Firewall rules or SQL logins are defined within the databases themselves and not as metadata about the resources. Therefore, they can’t be monitored as part of a policy. For these scenarios, you may find that a script that does routine checks on these types of items will need to be put in place.
• Define a procedure to perform periodic reviews on all non-compliant databases and remediate as necessary as part of your regular operations. Set aside time to do this routine so that it doesn’t get put off.
• Management Groups can define a specific group as the default management group for new subscriptions. By default, it is the Root Management Group; however, that may not be the best place for new subscriptions depending on what policies you want in place at that level. Check out the documentation on how to set your default management group. When choosing where a new subscription will be placed, think about failing safe as it applies to what security you want on your databases automatically without additional effort.
• Don’t wait until you have all possible policies you want to enforce defined before getting them applied. Once you have one or two policies that you know you’ll want to enforce, go ahead and get them implemented. This will allow you to work through your process of managing your policies and initiatives while the number of policies is small.
• Microsoft has created a lot of Azure Policy definitions for specific compliance standards policies, which can easily be pulled into your own initiatives. You can find a list of specific compliance standards (such as FedRAMP, HIPAA, NIST, and more ) in their documentation, along with links to Azure Policies defined on GitHub to use. They also have an Azure Security Benchmark built-in initiative you can use as a baseline if you wish. These built-in initiatives would be a great place to start, especially if there is a specific compliance standard you need to follow and there is already a policy defined.
  
  Image: Azure Portal showing the configuration for a built-in policy for ensuring Azure SQL Servers have disabled public access

Azure Policy and Management Groups

Using Azure Policies and Management Groups can help you get a good handle on the security of your data. You may still need to provide additional measures, but these tools will help you automate the enforcement of rules you define and provide a baseline level of security and compliance for all of your Azure SQL Databases and Azure SQL Managed Instances.

If you liked this article, you might also like Performance of querying blob storage with SQL – Simple Talk (red-gate.com).

The post Azure Policies and Management Groups appeared first on Simple Talk.

from Simple Talk https://ift.tt/3q0hJto
via

Linking a Virtual Machine with Azure Active Directory

Microsoft included in the provisioning process of a Virtual Machine the possibility to create a link between a virtual machine and the Azure Active Directory. This happened some time ago.

This was a great improvement in security and management. Instead of having an isolated user management for each virtual machine, the login on the virtual machines would be controlled by Azure Active Directory.

But what if, for some reason we miss the opportunity to join the virtual machine with active directory when it’s being provisioned? How could we join it to Azure AD after it’s already created?

There is a set of configurations needed to use Azure Ad login in a Virtual Machine:

• Install the Azure Ad Login Extension
• Enable a Managed Identity
• Define the RBAC permissions
• Register the source machine with Azure Ad

Install the Azure Ad Login Extension

On the portal, you can use the Extensions left menu item to install this extension. It’s very straightforward, no special configuration needed during the installation.

1. Open the virtual machine page in Azure Portal
2. Click Extensions + Applications on the left menu

 

1. Click the Add button

1. Select Azure Ad based Windows Login

1. Click the Review + Create button
2. Click the Create button

Enable a Managed Identity

The Virtual Machine needs a Managed Identity. You need to enable it

1. Open the virtual machine page in Azure Portal
2. On the left menu, under Settings, select Identity
3. Turn the System assigned identity On

Define RBAC permissions

The Virtual Machine has RBAC roles to define the administrators and regular users for it.

You need to add the users to these roles before trying the login. I recommend to use Azure Ad groups. Because we may be talking about many users and many VMs to manage.

 

Register the source machine with Azure Ad

The login only works if the source machine is a registered device on Azure Ad.

You can register the source machine by using Accounts in the source machine. Once you add a work account from your Azure Ad, the machine will be registered in it.

 

 

Once you made a login you will receive a successful registration message.

 

 

You can open Azure Active Directory in the portal and take a look on devices. Your machine should be there.

 

 

Login

The login on the virtual machine requires a special syntax. You need to use AzureAD\<UserUPN> for the login. It will only work with native accounts from the Azure tenant. It will not work with external/guest accounts.

 

 

Summary

Integrating the Virtual Machines with Azure AD is a great way to manage virtual machine users in a large scale.

The post Linking a Virtual Machine with Azure Active Directory appeared first on Simple Talk.

from Simple Talk https://ift.tt/3IP4CUb
via

Azure SQL: Enforcing SQL Authentication type

Some time ago I wrote a blog about the just released feature to allow only Azure Authentication in Azure SQL Databases.

 

On that blog, I explained not only about the feature, but how to use Azure Policies, a great feature for governance, to control which Azure SQL are correctly configured and which is not, even in a world-wide scenario.

From that time to today, a lot of things happened. The feature was in preview. The structure of the ARM template for this feature changed, making the policies I create not work anymore. After a while, Microsoft reverted the changes or something similar and the policies started work again. Now I can publish this new blog post.

The policy from the previous blog post can only to check if the Azure SQL is using Azure Authentication only or if it is accepting Azure SQL Authentication as well.

This time, let’s analyse a policy not only to check, but to change this configuration.

The policy is this one:

{
   "parameters":{
      
   },
   "policyRule":{
      "if":{
         "allOf":[
            {
               "field":"type",
               "equals":"Microsoft.Sql/servers"
            }
         ]
      },
      "then":{
         "effect":"deployIfNotExists",
         "details":{
            "type":"Microsoft.Sql/servers/azureADOnlyAuthentications",
            "roleDefinitionIds":[
               "/providers/Microsoft.Authorization/roleDefinitions/056cd41c-7e88-42e1-933e-88ba6a50c9c3",
               "/providers/Microsoft.Authorization/roleDefinitions/8e3af657-a8ff-443c-a75c-2fe8c4bcb635"
            ],
            "existenceCondition":{
               "allOf":[
                  {
                     "field":"Microsoft.Sql/servers/azureADOnlyAuthentications/azureADOnlyAuthentication",
                     "equals":true
                  }
               ]
            },
            "deployment":{
               "properties":{
                  "mode":"incremental",
                  "name":"Default",
                  "template":{
                     "$schema":"https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
                     "contentVersion":"1.0.0.0",
                     "parameters":{
                        "fullServerName":{
                           "type":"string"
                        }
                     },
                     "resources":[
                        {
                           "name":"[concat(parameters('fullServerName'), '/Default')]",
                           "apiVersion":"2021-02-01-preview",
                           "type":"Microsoft.Sql/servers/azureADOnlyAuthentications",
                           "properties":{
                              "azureADOnlyAuthentication":true
                           }
                        }
                     ]
                  },
                  "parameters":{
                     "fullServerName":{
                        "value":"[field('name')]"
                     }
                  }
               }
            }
         }
      }
   }
}

There are some interesting points to analyse about this policy.

The Policy Effect: DeployIfNotExist

It’s using the effect called deployIfNotExist, it will generate a deployment in case the condition is not met

There is an existence criteria in the effect and it needs to be met. In this policy most of the checks are in the effect, not in the main If of the policy. The main If is only checking for the type of the object. The additional conditionals are under existenceCondition, a property of this effect.

The ExistenceCondition

The condition checks if Azure SQL Server accepts Azure AD Authentication only and if not, the effect executes a deployment.

Deployment

The deployment property,  another property of the deployIfNotExist effect, contains an ARM template. The deployment is incremental, this means the policy effect will only deploy the features or properties in the template, nothing else.

This policy is possible because the property to allow only Azure AD Authentication is in an isolated object, making it easy for a policy to deploy this object.

The object needs to be related to the server, that’s why the deployment has a parameter called fullServerName and we use this parameter as the name of the deployed object.

The parameter is filled with the expression [field(‘name’)] . This expression retrieves the name of the object the policy is evaluating, in this case, the Azure SQL Server.

Role Definitions

The policy will execute the deployment of the template. Any change in Azure objects, in our scenario, Azure SQL Servers, will trigger the policy check.

It’s not so simple. This triggers many questions.

• Which identity will be used to make the deployment?
• What permissions this identity has?
• Will it be the same identity used everywhere?

We create and save the policy in an Azure hierarchy and we can assign it at any level below this hierarchy. Only the policy assignment can trigger the policy execution, if we have no assignment, we have no execution.

During the assignment, we create a link between the policy and one object, which can be a Resource Group, a Subscription or a Management Group. This assignment can, in some cases, also creates a managed identity. The created managed identity will need permission to execute the deployment required by the policy.

The permissions and the management identity creation are related: The management identity will be created when the permissions needed by the managed identity are included in the policy.

 

The roleDefinitionIds property has the permissions for the managed identity on the level of the object assigned to the policy (Resource Group, Subscription or Management Group).

Remediation Task

You can force the policy check at any moment using powershell. However, the fix of the problem will only be executed in two situations: Either when the object is created or if we create a Remediation Task.

Using the policy assignment we can create and execute a remediation task to fix the existing problems, executing the remediation already defined in the policy.

 

 

 

 

Summary

The possibility to enable only Azure AD Authentication in Azure SQL Databases is already great. Adding to this  the possibility to enforce this rule in many different levels using the policies is even better.

This policy is not perfect. It will fail if the Azure SQL Server doesn’t have an administrator configured. But this blog gives you the overall idea about what’s possible to achieve.

The post Azure SQL: Enforcing SQL Authentication type appeared first on Simple Talk.

from Simple Talk https://ift.tt/3IsJfYD
via

How to return multiple sequence numbers with sp_sequence_get_range

The series so far:

1. Introduction to SQL Server sequence objects
2. Using SQL Server sequence objects
3. How to return multiple sequence numbers with sp_sequence_get_range

Each time an application requests a sequence number using the NEXT VALUE FOR function, they get a new sequence number. The last used sequence number gets updated in the database metadata. If an application requires a sequence number series to be sequential, the NEXT VALUE FOR function cannot guarantee that all sequence numbers returned will be sequential. This behavior is caused because multiple sessions could be requesting sequence numbers at relatively the same time. If an application requires multiple sequence numbers, and all the sequence numbers need to be sequential, then the sp_sequence_get_range stored procedure should be used. This article will explore how an application can use this stored procedure to generate a range of sequential sequence numbers.

Sp_sequence_get_range stored procedure

The sp_sequence_get_range stored procedure is a system stored procedure that comes with SQL Server. It supports returning a range of sequence numbers for a sequence object. In reality, this stored procedure doesn’t really return a range of values but instead returns a series of output parameter values. The output parameters can then be used to generate a range of sequence number values programmatically. The stored procedure will also support cycling sequence numbers when the minimum or maximum values of the sequence object are reached. When this stored procedure is called, in addition to returning the output values, it also updates the last sequence number used in the Database metadata as if the entire range of sequence numbers was returned.

Below is syntax for the sp_sequence_get_range stored procedure, as found in the Microsoft documentation:

sp_sequence_get_range [ @sequence_name = ] N'<sequence>'   
     , [ @range_size = ] range_size  
     , [ @range_first_value = ] range_first_value OUTPUT   
    [, [ @range_last_value = ] range_last_value OUTPUT ]  
    [, [ @range_cycle_count = ] range_cycle_count OUTPUT ]  
    [, [ @sequence_increment = ] sequence_increment OUTPUT ]  
    [, [ @sequence_min_value = ] sequence_min_value OUTPUT ]  
    [, [ @sequence_max_value = ] sequence_max_value OUTPUT ]  
    [ ; ]

For a complete explanation of each of these parameters, please refer to the Microsoft documentation.

To understand how this store procedure can be used to generate a range of sequence numbers, take a look at a few examples.

Sequence object for examples

All the examples in this article will use a sequence object name CountTo7 that can be created by running the code in Listing 1.

Listing 1: Create CountTo7 sequence object

USE tempdb;
GO
CREATE SEQUENCE CountTo7
   AS int
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   MAXVALUE 7
   NO CYCLE; 
GO

This sequence object is defined as an integer. It can be used to generate the following series of sequence numbers: 1, 2, 3, 4, 5, 6, and 7. When a sequence number value of 7 is reached, the sequence number will not cycle because the NO CYCLE option has been specified.

Generating a range of three values

For the first example, the sp_sequence_get_range stored procedure will be called to return a range of three values from the CountTo7 sequence object. This procedure will not return the range of sequence numbers. Instead, it returns only output variables that can be used to generate the range of three values programmatically. Additionally, when this stored procedure is called, it will update the last sequence number as if all three different sequence numbers have been generated.

The code in Listing 2 will call the sp_sequence_get_range stored procedure requesting three values from the CountTo7 sequence objects. It will then use the output from the stored procedures to print out the next three sequence numbers.

Listing 2: Return a range of three values

USE tempdb;
GO
-- declare variable 
DECLARE   
  @RangeSize int = 3,   
  @FirstSeqNum sql_variant,  
  @LastSeqNum sql_variant,
  @CycleCount sql_variant,
  @SequenceIncrement sql_variant,
  @MinValue sql_variant, 
  @MaxValue sql_variant, 
  @CurrentSeqNum int,
  @I int = 0; 
--Get sequence object values to support range
EXEC sys.sp_sequence_get_range  
  @sequence_name = N'CountTo7', 
  @range_size = @RangeSize,
  @range_first_value = @FirstSeqNum OUTPUT,
  @range_last_value = @LastSeqNum OUTPUT,
  @sequence_increment = @SequenceIncrement OUTPUT;
-- Cycle Through Range
SET @CurrentSeqNum = CAST(@FirstSeqNum AS int);
WHILE @CurrentSeqNum <= CAST(@LastSeqNum as int)
BEGIN
    PRINT @CurrentSeqNum  
    SET @CurrentSeqNum = @CurrentSeqNum + 
         CAST(@SequenceIncrement AS int);
END

When the code in Listing 2 is run the output in Report 1 is produced.

Report 1: Output of PRINT statement in Listing 2

The code in Listing 2 first declares some variables to capture the output of the sp_sequence_get_range stored procedure. The code then calls the sp_sequence_get_range stored procedure, which returns several output variables. The output variable @FirstSeqNum contains the first variable in the range, the @LastSeqNum contains the last sequence number in the range, and the @SequenceIncrement variable contains the increment value for the @CountTo7 sequence objects. These variables are then used to process through a WHILE loop until all values in the requested range are displayed using a PRINT statement.

When the sp_sequence_get_range stored procedure was called, it returned output values and updated the database metadata for the last value used by the CountTo7 sequence object. This can be verified by running the code in Listing 3.

Listing 3: What is the current sequence number value

USE tempdb;
GO
SELECT name, current_value FROM sys.sequences
WHERE name = 'CountTo7';

Report 2 shows the current sequence number value for the CountTo7 sequence object.

Report 2: Current value of CountTo7 sequence object

The last value stored in metadata will be used to determine the next sequence number to generate if the NEXT VALUE FOR function or when the sp_sequence_get_range stored procedure is called. If Listing 2 is run a second time, it will print sequence values 4, 5, and 6. However, if it is run a third time, the following error will occur:

This message occurs because the CountTo7 sequence object is set up to not cycle, and there is only one more sequence number available before reaching the limit. To avoid this error, the code needs to be modified to reach the maximum for this non-cycling sequence object.

Dealing with maximum values for non-cycling sequence object

Some additional code will need to be written to programmatically retrieve the last range of sequence numbers for a non-cycling sequence object. In Listing 4, the code determines whether or not the maximum sequence value has been reached. If it has been reached, the message “No more values left” will be displayed. If not, the code adjusts the range size setting based on the number of values left.

Listing 4: Code to handle maximum values

USE tempdb;
GO
-- declare variable 
DECLARE   
  @RangeSize int = 3,   
  @FirstSeqNum sql_variant,  
  @LastSeqNum sql_variant,
  @CycleCount sql_variant,
  @SequenceIncrement sql_variant,
  @MinValue sql_variant, 
  @MaxValue sql_variant, 
  @CurrentSeqNum int,
  @I int = 0,
  @maximum_value int, 
  @increment int,
  @current_value int,
  @is_cycling bit; 
-- Get current values for sequence objecty
SELECT @maximum_value = CAST(maximum_value as int), 
       @increment = CAST(increment as int),
           @current_value = CAST(current_value as int),
           @is_cycling = Cast(is_cycling AS bit)
FROM sys.sequences WHERE name = 'CountTo7'
-- Are any values left
IF @current_value = @maximum_value 
BEGIN
   PRINT 'No more values left'
   RETURN
END
-- Adjust range if not enough values left
IF  (@current_value + (@increment * @RangeSize)) > @maximum_value
        AND @is_cycling = 0
   SET @RangeSize = @maximum_value - @current_value;
--Get range of sequence number
EXEC sys.sp_sequence_get_range  
  @sequence_name = N'CountTo7', 
  @range_size = @RangeSize,
  @range_first_value = @FirstSeqNum OUTPUT,
  @range_last_value = @LastSeqNum OUTPUT,
  @sequence_increment = @SequenceIncrement OUTPUT;
-- Cycle Through Range
SET @CurrentSeqNum = CAST(@FirstSeqNum AS int);
WHILE @CurrentSeqNum <= CAST(@LastSeqNum as int)
BEGIN
   PRINT @CurrentSeqNum  
   SET @CurrentSeqNum = @CurrentSeqNum + 
          CAST(@SequenceIncrement AS int);
END

I’ll leave it up to you to run the code in Listing 4. The first time you run it, it should adjust the range and return the last value left for this sequence object, which would be “7”. For every execution after the first one, the message “No more values left” will be displayed. It is worth noting that this code only works for sequence objects that have a positive increment value and don’t cycle. If you need to handle sequence objects that cycle or count down, different code will be needed.

Handling cycling of sequence numbers

When a sequence object supports cycling, the code to return ranges of sequence numbers gets a little more complicated. The output parameter @range_cycle_count can be used to determine if the range requested by the sp_sequence_get_range stored procedure has cycled. This output parameter indicates the number of times a range has cycled, where 0 indicates the range has not cycled through all the available values of a sequence object. A positive number tells the number of times a sequence object has cycled, based on its minimum or maximum value.

When a range of numbers is cycled, the first sequence number used after cycling depends on the increment value. If the increment value is positive, the minimum value is used as the first sequence number after cycling. If the increment value is negative, the maximum value is used as the first sequence number after cycling.

Run the code in Listing 5 to cycle through a range of numbers. This code was built by refactoring the code in Listing 4 and adding additional code to cycle up or down through the sequence numbers depending on whether the increment value is negative or positive.

Listing 5: Code to cycle through sequence numbers

USE tempdb;
GO
DECLARE   
-- Parameters for code
  @RangeSize int = 3,   
  @SequenceName varchar(100) = 'CountTo7', 
  -- Variables returned from sys.sp_sequence_get_range  
  @RangeFirstValue sql_variant,  
  @RangeLastValue sql_variant,
  @RangeCycleCount int,
  -- values returned from sys.sequences 
  @SequenceMinValue int, 
  @SequenceMaxValue int,
  @SequenceIncrement int,
  @SequenceCurrentValue int,
  @SequenceIsCycling bit,
  -- temp variables
  @I int = 0,
  @CurrentSeqNum INT;
  
-- Get current values for sequence settings
SELECT @SequenceMinValue = CAST(minimum_value as int), 
       @SequenceMaxValue = CAST(maximum_value as int), 
       @SequenceIncrement= CAST(increment as int),
           @SequenceCurrentValue = CAST(current_value as int),
           @SequenceIsCycling = Cast(is_cycling AS bit)
FROM sys.sequences where name = @SequenceName;
-- Adjust range to reflect number of values left if not cycling
IF @SequenceIsCycling = 0  AND (@SequenceMaxValue - 
           @SequenceCurrentValue) / @SequenceIncrement < @RangeSize
   AND @SequenceIncrement > 0
   SET @RangeSize = (@SequenceMaxValue - 
         @SequenceCurrentValue) / @SequenceIncrement
IF @SequenceIsCycling = 0  AND 
        (@SequenceMinValue - @SequenceCurrentValue) / 
           @SequenceIncrement < @RangeSize
   AND @SequenceIncrement < 0
   SET @RangeSize = (@SequenceMinValue - 
         @SequenceCurrentValue) / @SequenceIncrement
-- Are the more sequences available
IF @RangeSize > 0
   --Get range of values
   EXEC sys.sp_sequence_get_range  
     @sequence_name = @SequenceName, 
     @range_size = @RangeSize,
     @range_first_value = @RangeFirstValue OUTPUT,
     @range_last_value = @RangeLastValue OUTPUT, 
     @range_cycle_count = @RangeCycleCount OUTPUT;
ELSE
-- No more 
  PRINT 'No more sequence numbers to return';
SET @CurrentSeqNum = CAST(@RangeFirstValue AS INT);
-- Cycle Through Range
WHILE @RangeSize > 0  
BEGIN
    IF @CurrentSeqNum > @SequenceMaxValue
           SET @CurrentSeqNum =  @SequenceMinValue;
        IF @CurrentSeqNum < @SequenceMinValue
           SET @CurrentSeqNum = @SequenceMaxValue
        PRINT @CurrentSeqNum;
        SET @CurrentSeqNum = @CurrentSeqNum + @SequenceIncrement;
        SET @RangeSize = @RangeSize - 1
END

To verify the code in Listing 5 will cycle through the values of the CountTo7 sequence object, the object first needs to be altered, so it supports cycling by using the code in Listing 6.

Listing 6: Altering sequence object to support cycling

USE tempdb;
GO
ALTER Sequence CountTo7 
   RESTART WITH 7
   CYCLE;
GO

Listing 6, in addition to altering the object to cycle, also sets the sequence object to restart at 7. Setting this sequence object to restart at 7 updated the current_value in metadata to 7 and reset the last_used_value to NULL. This can be verified by running the code in Listing 7.

Listing 7: Reviewing metadata for CountTo7 sequence object

USE tempdb;
GO
SELECT name, current_value, last_used_value FROM sys.sequences
WHERE name = 'CountTo7';

When the code in Listing 7 is run the output in Report 3 is displayed.

Report 3: Output when Listing 7 is run

With the CountTo7 sequence object set up to cycle, the code in Listing 5 can be executed. The first time this code is run, it will generate the range shown in Report 4.

Report 4: Output from the first execution of Listing 5

The sequence started generating values starting at 7 and then cycled, creating values 1 and 2, as the last two values in the range. The second time the code in Listing 5 is executed, it will return 3, 4, and 5. Each time it is rerun, it will generate the next three sequence number values based on the information stored in the database metadata.

The @range_cycle_count variable in the Listing 5 code is used to determine if the range of numbers cycled past the maximum value. In Listing 5, the sp_sequence_get_range stored procedure will never cycle through the range of values more than one time. This behavior happens because a range of 3 values is too small to cycle through the complete list of 7 values associated with the @CountTo7 sequence object.

To test if the code in Listing 5 will cycle through the CountTo7 sequence values more than once, all that needs to be done is to pick a @RangeSize parameter setting greater than 7. I’ll leave it up to you to test out different @RangeSize values to verify that this code can cycle multiple times through a range of sequence numbers.

The code in Listing 5 not only supports cycling but also has been written to handle sequence objects with a negative increment value. Keep in mind the code shown here is only sample code and has not been thoroughly tested to cover all situations. Therefore, use it at your own risk and fully test it before using this sample code in your application.

Missing sequence number values

The sp_seqeunce_get_range has a few issues of which you should be aware. When this stored procedure is called to retrieve a range, it updates the last used sequence number for the sequence object in the database metadata. If all of the values in the range requested are not used, any unused values will be lost. Additionally, if the database engine crashes, the last used sequence number stored in metadata will not be rolled back. Therefore, any values not used before the crash will also be lost.

Return multiple sequence numbers with sp_sequence_get_range

The sequence object was introduced with SQL Server 2012. Sequence objects can be used to populate multiple columns in a single table, as well as to synchronize a series of generated numbers across multiple tables. Using the sequence object to generate a series of numbers has more flexibility than using an identity column. When multiple sessions request sequence numbers simultaneously, the numbers generated for a given session may not be continuous sequence numbers. When this is an issue, the sp_sequence_get_range stored procedure can be used. By using this stored procedure, application code can be written, so a range of sequential sequence numbers can be obtained for a session. The next time you need to ensure that sequence number values are contiguous, you should use the sp_sequence_get_range system stored procedure to guarantee your range of sequence numbers does not have missing values.

If you liked this article, you might also like 

The post How to return multiple sequence numbers with sp_sequence_get_range appeared first on Simple Talk.

from Simple Talk https://ift.tt/3rouVuf
via