In SQL Server, "Sort" refers to the operation of arranging data in a specific order. Sorting data can be useful when you want to present the data in a particular way, or when you need to perform certain calculations or analysis that depend on the order of the data. How do You Sort Items In SQL? In SQL Server, you can use the "ORDER BY" clause to sort data in a specific way. This clause is typically used in conjunction with a "SELECT" statement to retrieve data from one or more tables. Sort By One Or More Columns - Syntax The basic syntax of the ORDER BY clause is as follows: SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ... SQL Server Versions And Sorting There may be some differences in sorting behavior between different versions of SQL Server, although these are generally minor and unlikely to cause issues for most applications. Some versions sort the query results in an ascending order by the collation default. Here are a few differences to be aware of: Unicode sorting: SQL Server 2008 introduced new sorting rules for Unicode character data that are designed to better match the expectations of users in different regions. These new rules are used by default in SQL Server 2008 and later versions, but can be disabled if necessary. Collation behavior: The collation setting used in a SQL Server database can affect the way that data is sorted. In some cases, different collation settings may be used in different versions of SQL Server, which could cause differences in sorting behavior. However, this is generally a rare occurrence. Query optimizer changes: SQL Server's query optimizer, which is responsible for generating efficient execution plans for SQL statements, may change between versions. While these changes are unlikely to affect sorting behavior directly, they could have an impact on the performance of sorting operations. Sample Data For Use Below Customers Table And Employee Table Here's the T-SQL code to create the employees table: CREATE TABLE employees ( [First Name] VARCHAR(50), [Last Name] VARCHAR(50), [Age] INT, [State Name] VARCHAR(50), [State Abbreviation] VARCHAR(2) ); Here's the T-SQL code to insert the data into the employees table: INSERT INTO employees ([First Name], [Last Name], [Age], [State Name], [State Abbreviation]) VALUES ('John', 'Smith', 28, 'California', 'CA'), ('Sarah', 'Johnson', 28, 'New York', 'NY'), ('Alex', 'Brown', 42, 'Texas', 'TX'), ('Emily', 'Davis', 26, 'Florida', 'FL'), ('David', 'Lee', 31, 'Massachusetts', 'MA'), ('Michael', 'Wilson', 47, 'Pennsylvania', 'PA'), ('Jessica', 'Garcia', 24, 'Arizona', 'AZ'); Best Sorting features Of Sorting In SQL Server: Sorting The FirstName Column In Descending Order And Ascending Order: Here's the T-SQL code to select all the rows in ascending or descending order sorted alphabetically. SELECT * FROM employees ORDER BY [First Name] ASC; SELECT * FROM employees ORDER BY [First Name] DESC; Sql Sort By Multiple Columns You can sort data on multiple columns in SQL Server by specifying multiple column names in the ORDER BY clause. This is helpful when you want to sort data first by one column, and then by another column within the groups created by the first sort. First, let's create the "aircraft" table with some sample data: CREATE TABLE aircraft ( aircraft_id int, manufacturer varchar(50), model varchar(50), range_in_km int, seating_capacity int ); INSERT INTO aircraft (aircraft_id, manufacturer, model, range_in_km, seating_capacity) VALUES (1, 'Boeing', '737', 5500, 189), (2, 'Boeing', '777', 9700, 440), (3, 'Airbus', 'A320', 6100, 150), (4, 'Airbus', 'A330', 11800, 277), (5, 'Embraer', 'E190', 3700, 114); Now let's say we want to retrieve a list of aircraft sorted first by manufacturer, then by seating capacity, and finally by range in descending order. We can use the following SELECT statement with an ORDER BY clause: The following query will return rows in the descending order SELECT * FROM aircraft ORDER BY manufacturer, seating_capacity, range_in_km DESC; This will return the following result: As you can see, the data is first sorted by manufacturer (Airbus, Boeing, Embraer), then by seating capacity (ascending order within each manufacturer group), and finally by range in descending order (within each seating capacity group). Sorting On Expressions: SQL Server allows you to sort data based on expressions that you create using mathematical operators, functions, or any other operations that produce a value. This can be useful when you want to sort data based on calculations or transformations of the underlying data. Suppose we want to retrieve a list of aircraft sorted by the ratio of their range to seating capacity. We can use an expression in the ORDER BY clause to calculate this ratio and sort the data accordingly. Here's the SQL statement to achieve this: SELECT *, range_in_km / seating_capacity AS range_to_capacity_ratio FROM aircraft ORDER BY range_to_capacity_ratio DESC; In this example, we're selecting all columns from the "aircraft" table and also calculating a new column called "range_to_capacity_ratio" using an expression that divides the "range_in_km" column by the "seating_capacity" column. We're then sorting the results in descending order by the value of this new column. Here's the result of the above query: Sorting On Case-Sensitive Or Case-Insensitive Basis: SQL Server allows you to specify whether sorting should be done in a case-sensitive or case-insensitive manner. This is particularly useful when you're sorting text data. Suppose we want to retrieve a list of aircraft sorted by their manufacturer name, but we want to sort the data in a case-insensitive manner. We can use the COLLATE keyword in the ORDER BY clause to specify a case-insensitive collation. Here's the SQL statement to achieve this: SELECT * FROM aircraft ORDER BY manufacturer COLLATE SQL_Latin1_General_CP1_CI_AS; Here's the result of the above query: As you can see, the data is sorted in ascending order by the "manufacturer" column, with manufacturers whose names start with the same letters appearing next to each other. The case of the letters is ignored in this sorting, so "Airbus" and "airbus" are considered equivalent. If we want to sort the data in a case-sensitive manner, we can use a different collation that distinguishes between uppercase and lowercase letters. For example, we could use the "SQL_Latin1_General_CP1_CS_AS" collation instead. Here's the SQL statement to achieve this: SELECT * FROM aircraft ORDER BY manufacturer COLLATE SQL_Latin1_General_CP1_CS_AS; This will produce the following result: As you can see, the data is again sorted in ascending order by the "manufacturer" column, but this time the case of the letters is taken into account, so "Airbus" and "airbus" are considered different. Sort A Result Set By A Column That Is Not In The Select Column List Suppose we want to retrieve a list of aircraft models and their seating capacity, sorted by the range in kilometers of the aircraft. However, we don't want to include the "range_in_km" column in the select list. We can achieve this by including the "range_in_km" column in the ORDER BY clause, even though it's not in the SELECT list. Here's the SQL statement to achieve this: SELECT model, seating_capacity FROM aircraft ORDER BY range_in_km; In this example, we're selecting only the "model" and "seating_capacity" columns from the "aircraft" table, and sorting the results in ascending order by the "range_in_km" column. Here's the result of the above query: Sorting with NULL Values: SQL Server provides options for how to handle NULL values when sorting data. You can either specify whether to treat NULL values as the highest or lowest values in the sort order, or you can specify a custom sort order for NULL values. Here's an example of an UPDATE statement to set the Age value to NULL for the employee with First Name 'David': UPDATE employees SET [Age] = NULL WHERE [First Name] = 'David'; To sort NULL values, SQL Server has two sorting options: NULLS FIRST and NULLS LAST. By default, SQL Server sorts NULL values as NULLS FIRST, which means that NULL values are sorted before non-NULL values. Here's an example of a SELECT statement to show how SQL Server sorts NULL values: SELECT [First Name], [Last Name], [Age], [State Name], [State Abbreviation] FROM employees ORDER BY [Age] ASC NULLS LAST; This code retrieves all the rows from the employees table and sorts them by the Age column in ascending order, using the ORDER BY clause. The NULLS LAST option specifies that NULL values should be sorted after non-NULL values. Therefore, in this case, the rows with non-NULL ages will be sorted first in ascending order, and then the row with a NULL age will be sorted last. These are some of the best features of sorting in SQL Server that make it a powerful tool for working with data. By using these features, you can easily sort data in various ways to suit your needs and gain insights from your data. Additional Resources

What Is A Trigger In T SQL Triggers in SQL Server are used to automate tasks whenever a change is made to the database. This allows for greater efficiency, accuracy and scalability compared to manual processes. In this guide, we’ll provide a detailed overview of triggers so you can understand exactly what they are and how they work. Triggers are special stored procedures that automatically execute whenever an event or change occurs in the database. A SQL trigger is a database object that fires when INSERTs, DELETEs, or UPDATEs to particular tables are executed. When the trigger is fired, it runs the stored procedure which contains the logic for performing whatever task is required by the trigger. For example, if a new record is added to table A, an associated trigger could be written to move that record into another table. If the trigger fails, the original operation also fails. What Are The Best Features Of Trigger In T-SQL Triggers in T-SQL provide a powerful mechanism for automating common tasks in a SQL Server database. Here are some of the benefits of using triggers: Data Validation: Triggers can be used to enforce data integrity rules and validate the data before it is inserted, updated or deleted in a table. For example, a trigger can be created to prevent the insertion of invalid data in a table or to enforce cross database referential integrity and referential integrity between related tables. Auditing: Triggers can be used to log changes made to a table for auditing purposes. By capturing information such as the user ID, date and time, and the action performed, you can maintain a history of the changes made to the data in the table. Automatic Updates: Triggers can be used to update related tables automatically when changes are made to a particular table. For example, when a record is inserted or updated in a table, the trigger can automatically update a corresponding record in another table. Complex Business Logic: Triggers can be used to implement complex business logic that involves multiple tables or business rules. By using triggers, you can encapsulate the business logic in the database itself, making it easier to maintain and update. Performance Optimization: Triggers can be used to improve database performance by precomputing or caching frequently used data. By using triggers to maintain precomputed data in a separate table, you can avoid the need to recalculate the data every time it is needed, which can improve query performance. Overall, triggers in T-SQL provide a flexible and powerful mechanism for automating common tasks in a SQL Server database. By using triggers, you can improve data integrity, automate business processes, and optimize database performance, making your database more efficient and reliable. Create Trigger Syntax - How You Create A Trigger In T-SQL In T-SQL, a trigger schema is the set of properties and attributes that define a trigger. The schema specifies when the trigger is executed and what actions are performed by the trigger. The basic syntax for creating a trigger in T-SQL is: CREATE TRIGGER trigger_name ON table_name AFTER {INSERT | UPDATE | DELETE} AS BEGIN -- Trigger logic here END In this syntax, trigger_name is the name of the trigger, table_name is the name of the table that the trigger is associated with, and {INSERT | UPDATE | DELETE} specifies the DML operation that triggers the trigger. The AFTER keyword indicates that the trigger should be executed after the DML operation completes. The trigger schema can also include other attributes, such as the INSTEAD OF keyword, which specifies that the trigger should replace the default behavior of the DML operation rather than execute after it. The schema can also include conditions or constraints on when the trigger should be executed, such as a WHEN clause or a reference to a user-defined function. The trigger schema can also define the actions that the trigger should perform, such as modifying data in the table or executing a stored procedure. The schema can include T-SQL statements or references to user-defined functions or procedures. By defining the trigger schema in T-SQL, you can customize the behavior of your SQL Server database and automate common tasks, such as auditing or data validation. However, it's important to carefully design and test your triggers to avoid problems with performance, security, or data integrity. CREATE TRIGGER trigger_name ON table_name AFTER {INSERT | UPDATE | DELETE} AS -- SQL statements to execute when trigger is fired Here are the steps to create a trigger in T-SQL: Identify the trigger name: Choose a name for your trigger that is descriptive and unique. Identify the table: Determine which table the trigger will be associated with. Choose the trigger event: Select the event that will fire the trigger. You can choose from INSERT, UPDATE, or DELETE. Write the SQL statements: Write the SQL statements that will execute when the trigger is fired. Recursive Triggers Recursive triggers in SQL Server are triggers that execute recursively, which means that they trigger themselves repeatedly. A recursive trigger occurs when a DML (Data Manipulation Language) operation, such as an insert, update or delete, causes the trigger to fire, which in turn causes another DML operation, which causes the trigger to fire again, and so on. Recursive triggers can be problematic because they can cause infinite loops, which can lead to excessive resource consumption and poor database performance. In some cases, a recursive trigger may cause the SQL Server engine to run out of stack space, resulting in an error message. To avoid recursive triggers, you can use the following strategies: Use the nested triggers server configuration option to limit the depth of recursion. By default, this option is set to 1, which means that triggers cannot call other triggers. You can increase the setting to allow nested triggers, but this can increase the risk of recursion. Use the INSTEAD OF trigger instead of AFTER trigger. INSTEAD OF triggers can replace the default behavior of a DML statement, and can be used to prevent recursive triggers by avoiding the triggering DML statement. Use application logic to avoid recursive operations. For example, you can use a flag in the data to indicate that a trigger has already been fired for a particular operation, and prevent the trigger from firing again. In general, it's important to be aware of the risks associated with recursive triggers, and to take steps to prevent them from occurring. By using best practices and carefully managing triggers, you can avoid problems with recursive triggers and ensure optimal performance of your SQL Server database. Memory Optimized Tables In Microsoft SQL Server in SQL Server are designed to provide high-performance data access by keeping data in-memory and minimizing disk I/O. However, memory optimized tables have some limitations when it comes to SQL Server triggers: No support for DDL triggers: Memory optimized tables do not support DDL triggers, which are used to respond to changes to the database schema. This means that you cannot create DDL triggers on memory optimized tables. Support for AFTER triggers only: Memory optimized tables only support AFTER triggers, which execute after the data has been modified. They do not support INSTEAD OF triggers, which can be used to replace the default behavior of a DML statement. Limitations on actions: Memory-optimized tables have certain limitations on the actions that can be performed within a trigger. For example, you cannot use INSERT, UPDATE, or DELETE statements in a trigger on a memory-optimized table. Additionally, you cannot use the OUTPUT clause in a trigger on a memory-optimized table. Performance impact: Triggers on memory-optimized tables can have a performance impact, especially if they perform complex or time-consuming actions. It's important to ensure that the trigger is optimized for performance and doesn't cause undue delays for users. Overall, while memory-optimized tables offer high-performance data access in SQL Server, there are some limitations when it comes to triggers. It's important to be aware of these limitations and to ensure that any triggers on memory optimized tables are optimized for performance and limited in scope. Types of Triggers In SQL Server, there are two types of triggers that can be used to automate certain actions or enforce data integrity rules: Data Manipulation Language (DML) Triggers DML Triggers: Data Manipulation Language (DML) triggers are invoked automatically in response to data manipulation operations, such as INSERT, UPDATE, and DELETE statements. They can be used to enforce data integrity rules and business logic, audit data changes, and perform other actions based on changes to the data. The key distinction between the trigger and procedure is that a trigger is called automatically when a data modification event occurs against a table. DML triggers can be defined on tables or views, and can be either AFTER triggers, which execute after the data manipulation statement has been completed, or INSTEAD OF triggers, which execute instead of the data manipulation statement. In DML triggers, there are two virtual tables during the execution of the trigger that holds the data being affected by the trigger execution. Those tables are named inserted and deleted and they have the same table structure as their base table. Something to keep in mind is that the inserted and deleted tables. DML (Data Manipulation Language) triggers are database triggers that are executed in response to specific data manipulation events such as INSERT, UPDATE, or DELETE operations on a table. In SQL Server. Here are some common types of DML triggers in SQL Server: AFTER INSERT Trigger: This trigger fires after a new row is inserted into a table. AFTER UPDATE Trigger: This trigger fires after an existing row is updated in a table. AFTER DELETE Trigger: This trigger fires after a row is deleted from a table. INSTEAD OF Trigger: This trigger is fired instead of the DML statement, and allows the trigger to modify the statement or perform some other action instead. FOR INSERT Trigger: This trigger fires only for INSERT statements and can be used to enforce business rules, data validation, or perform other actions based on the new data being inserted. FOR UPDATE Trigger: This trigger fires only for UPDATE statements and can be used to enforce business rules, data validation, or perform other actions based on the updated data. FOR DELETE Trigger: This trigger fires only for DELETE statements and can be used to enforce business rules, data validation, or perform other actions based on the deleted data. Here Is An Example Of An INSTEAD OF Trigger For The "Orders" Table: First, let's create a table called "orders" against your database server with three columns: CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_name VARCHAR(50), order_date DATE ); Here's an example of a database objects, INSTEAD OF a trigger on the "orders" table: CREATE TRIGGER tr_instead_of_insert_order ON orders INSTEAD OF INSERT AS BEGIN DECLARE @order_id INT SELECT @order_id = ISNULL(MAX(order_id), 0) + 1 FROM orders INSERT INTO orders (order_id, customer_name, order_date) SELECT @order_id, i.customer_name, i.order_date FROM inserted i END; This trigger is an INSTEAD OF trigger on the "orders" table that fires instead of an INSERT statement. It generates a new order_id for each inserted row, and then inserts the new row into the "orders" table with the generated order_id. Note that the "inserted" table is a special table in SQL Server that contains the rows that were inserted by the triggering statement. In this case, the trigger selects the values from the "inserted" table and inserts them into the "orders" table with a new order_id. SQL Insert Data Trigger – Example Here's an example of a SQL INSERT trigger using T-SQL to create the necessary tables and sample data: let's create a table called "order_logs" with four columns: CREATE TABLE order_logs ( log_id INT PRIMARY KEY, order_id INT, log_date DATETIME, log_message VARCHAR(200) ); This table will store a historical record of all order insertions, along with a log message that describes the action performed. Now let's use an Insert statement to inject some sample data into the "orders" table: INSERT INTO orders (order_id, customer_name, order_date) VALUES (1, 'John Smith', '2022-02-14'); INSERT INTO orders (order_id, customer_name, order_date) VALUES (2, 'Jane Doe', '2022-02-13'); And let's create an INSERT trigger on the "orders" table that will automatically insert a record into the "order_logs" a separate table that tracks each time a new order is added: CREATE TRIGGER order_insert_trigger ON orders AFTER INSERT AS BEGIN INSERT INTO order_logs (order_id, log_date, log_message) SELECT i.order_id, GETDATE(), 'New order inserted for customer: ' + i.customer_name FROM inserted i; END; This trigger fires after an INSERT operation on the "orders" table, and inserts a record into the "order_logs" table with the same order ID, the current date and time, and a log message that describes the action performed. Now, if we insert a new order into the "orders" table, we'll see a corresponding record in the "order_logs" table: INSERT INTO orders (order_id, customer_name, order_date) VALUES (3, 'Bob Johnson', '2022-02-14'); And if we query the "order_logs" table, we'll see a record with a log message indicating that a new order was inserted for customer "Bob Johnson": SELECT * FROM order_logs; DELETE Statement Trigger – Example Here's an example of a SQL DELETE trigger using the same tables we created in the previous example: CREATE TRIGGER order_delete_trigger ON orders AFTER DELETE AS BEGIN INSERT INTO order_logs (order_id, log_date, log_message) SELECT d.order_id, GETDATE(), 'Order deleted for customer: ' + d.customer_name FROM deleted d; END; This trigger fires after a DELETE operation on the "orders" table, and inserts a record into the "order_logs" table with the same order ID as the deleted record, the current date and time, and a log message that describes the action performed. Now, if we delete an order from the "orders" table, we'll see a corresponding record in the "order_logs" table: DELETE FROM orders WHERE order_id = 2; And if we query the "order_logs" table, we'll see a record with a log message indicating that an order was deleted for customer "Jane Doe": SELECT * FROM order_logs; Note that the "deleted" table is a special table that is available within triggers, and contains the rows that were just deleted from the triggering table. Both types of DML triggers can be useful in certain scenarios, but they each have their own advantages and limitations. It's important to understand the differences between the two and use them appropriately for your specific requirements. Optimizing DML Triggers In SQL Server be sure that it is as efficient as possible and does not impact the performance of other database operations. Here are a few tips for optimizing a DDL trigger: Keep the trigger code simple: Complex trigger logic can be difficult to maintain and debug, and can also slow down the performance of the trigger. Keep the trigger code as simple as possible. Use the appropriate trigger type: There are three types of DDL triggers in SQL Server: AFTER triggers, INSTEAD OF triggers, and combination triggers. Each type has its own use case, so choose the appropriate trigger type for your scenario. Minimize the number of triggers: If you have multiple triggers on the same table or database, they can interfere with each other and cause performance issues. Try to consolidate your triggers into as few as possible, and avoid redundant or unnecessary triggers. Limit the scope of the trigger: Use the EVENTDATA() function to identify the specific event that triggered the DDL trigger, and limit the scope of the trigger to only the relevant objects. Avoid complex queries in the trigger: Complex queries, especially those that join multiple tables, can slow down the trigger's performance. Instead, use simple queries and avoid accessing other tables if possible. Test and tune the trigger: Before deploying the trigger to production, test it with a large dataset to make sure it performs well. Monitor the trigger's performance and make adjustments as needed. By following these tips, you can optimize your DDL trigger and ensure that it has minimal impact on your database's performance. Data Definition Language (DDL) Triggers DDL Triggers: Data Definition Language (DDL) triggers are fired in response to Data Definition Language (DDL) events, such as CREATE, ALTER, and DROP statements. They can be used to enforce administrative policies, perform maintenance tasks, and audit database schema changes. DDL triggers can be defined at the server or database level, and can be either AFTER triggers, which execute after the DDL statement has completed, or INSTEAD OF triggers, which execute instead of the DDL statement. DDL (Data Definition Language) triggers in a database management system are a special type of trigger that execute automatically in response to certain DDL events such as CREATE, ALTER, or DROP statements. Here are some common types of DDL triggers: AFTER CREATE Trigger: This trigger fires after a new object (such as a table or stored procedure) is created in the database. AFTER ALTER Trigger: This trigger fires after an existing object is modified (such as a table being altered to add or remove columns). AFTER DROP Trigger: This trigger fires after an existing object (such as a table or stored procedure) is dropped from the database. INSTEAD OF Trigger: This trigger is fired instead of the DDL statement, and allows the trigger to modify the statement or perform some other action instead. LOGON Trigger: This trigger is fired when a user logs into the database, and can be used to enforce security policies or perform other actions. SERVER Scope Trigger: This type of trigger applies to the entire server and can be used to enforce global policies or perform actions such as auditing. These are some of the common types of DDL triggers, but the specific types and capabilities may vary depending on the database management system being used. Here's an example of an AFTER ALTER trigger that logs to an "Audit" table: CREATE TABLE Audit ( event_type VARCHAR(50), object_name VARCHAR(50), event_date DATETIME ); CREATE TRIGGER tr_after_alter_table ON DATABASE AFTER ALTER_TABLE AS BEGIN DECLARE @object_name VARCHAR(50) SET @object_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)') INSERT INTO Audit (event_type, object_name, event_date) VALUES ('ALTER_TABLE', @object_name, GETDATE()) END; This trigger is an AFTER ALTER_TABLE trigger that fires after a table is altered in the database. The trigger first retrieves the name of the altered object using the EVENTDATA function and inserts a new row into the "Audit" table with the event type, object name, and current date and time. Note that the "Audit" table needs to be created before the trigger can be created. The "Audit" table has three columns: "event_type" to store the type of event that occurred, "object_name" to store the name of the altered object, and "event_date" to store the date and time when the event occurred. Logon Trigger A Logon Trigger in SQL Server is a special type of trigger that fires in response to a user's connection to the SQL Server instance AKA a logon event It is executed after the authentication process is successfully completed, but before the user session is actually established SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is established. So, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers can be used to enforce security policies, auditing, or custom login procedures. For example, a logon trigger can be set up to prevent certain users from logging in during specific time periods, or to redirect users to a custom login screen. It can also be used to record login attempts, monitor user activity, or enforce password policies. Logon triggers are created using Transact-SQL commands and are associated with a specific SQL Server instance or database. They can be enabled or disabled using the ALTER TRIGGER statement. Here is an example of a Logon Trigger in SQL Server that restricts access to the SQL Server instance based on the time of day: CREATE TRIGGER RestrictLogins ON ALL SERVER FOR LOGON AS BEGIN DECLARE @hour int SET @hour = DATEPART(HOUR, GETDATE()) IF (@hour < 8 OR @hour > 18) AND ORIGINAL_LOGIN() NOT IN ('sa', 'domain\admin') BEGIN ROLLBACK; PRINT 'Access to SQL Server is only allowed between 8am and 6pm.' END END In this example, the trigger is created on the ALL SERVER level and is set to fire FOR LOGON events. The trigger then uses the DATEPART function to extract the current hour of the day, and checks if the login user is not 'sa' or 'domain\admin'. If the login user is not an administrator and the current time is outside of business hours (i.e. before 8am or after 6pm), the trigger rolls back the login attempt and displays a message to the user. This is just one example of how a Logon Trigger can be used in SQL Server to enforce security policies. The possibilities are endless, and Logon Triggers can be used to perform a wide range of custom login procedures, such as redirecting users to a custom login screen, enforcing password policies, or logging login attempts. SQL Server Trigger Usage Scenarios Triggers in T-SQL are used to automate certain actions or enforce data integrity rules when data is modified in a database. Here are some situations when you might want to use a trigger: Enforcing business rules: Triggers can be used to enforce business rules by checking the validity of data being modified in a table before the data is inserted, updated, or deleted. For example, you might use a trigger to enforce a rule that prevents the deletion of a record if it is referenced by another record. Auditing data changes: Triggers can be used to audit changes to data in a database by writing the changes to a separate audit table. This can be useful for compliance purposes, or for tracking changes made by users. Replicating data: Triggers can be used to replicate data from one database to another. For example, you might use a trigger to replicate data from a production database to a reporting database. Performing complex calculations: Triggers can be used to perform complex calculations on data when it is modified. For example, you might use a trigger to calculate a running total or average of values in a table. Maintaining referential integrity: Triggers can be used to maintain referential integrity by checking that foreign key constraints are not violated when data is modified. For example, you might use a trigger to prevent the deletion of a record that is referenced by another record. It's important to use triggers judiciously, as they can have a performance impact on a database, especially if they are complex or fire frequently. Before using a trigger, consider whether the same functionality can be achieved using other mechanisms, such as stored procedures or application code. While Triggers In SQL Server Can Be Useful In Certain Situations, There Are Potential Drawbacks: Performance: Triggers can slow down database operations because they add additional processing overhead. In high-volume transactional systems, this can lead to performance issues and slow response times. Complexity: Triggers can be complex to write and maintain, especially if they involve multiple tables or complex business logic. This can make it harder to understand and debug database behavior. Unintended Consequences: If triggers are not designed carefully, they can cause unintended consequences such as data inconsistencies, circular references, or infinite loops. This can result in data corruption or other issues that can be difficult to diagnose and resolve. Scalability: Triggers can make it harder to scale a database because they add additional processing overhead and can make it more difficult to partition data across multiple servers. Security: If triggers are used to enforce security policies, they can be bypassed by users with administrative privileges. This can create security vulnerabilities and make it harder to ensure compliance with regulatory requirements. Create A Trigger In SQL Server Management Studio Each trigger will be created in the triggers folder in SSMS New And Modify Triggers Additional Resources

What Is Data Visualization With SSRS Data visualization is the process of representing data, information, and insights in a graphical or pictorial format. The goal of data visualization is to help people understand the significance of data by placing it in a visual context. This makes it easier to identify patterns, trends, and insights that might not be immediately apparent from raw data. Data visualization is used in various fields such as business, science, and journalism, and it can be used to communicate information in a clear, concise, and effective manner. Common forms of data visualization include bar graphs, line charts, scatter plots, and heat maps. SQL Server Reporting Services (SSRS) is a reporting platform provided by Microsoft that allows you to create, manage, and deliver reports. Here are some of the visual report parts you can add to an SSRS report: Tables: A table is used to display data in a structured format, where each row represents a record and each column represents a field. Matrices: A matrix is similar to a table, but it allows you to display data in a more flexible way by organizing it into rows and columns. Charts: A chart is used to display data in a graphical format, making it easier to identify trends and patterns. SSRS supports several types of charts, including bar charts, line charts, pie charts, and scatter charts. Maps: A map is used to display data on a geographical basis. You can use maps to visualize data such as sales by region or customer locations. Gauges: A gauge is used to display data in a visual format, such as a thermometer or speedometer. You can use gauges to represent data such as sales targets or service levels. Below are some examples from the Microsoft Learn website. Sparklines: Sparklines are small, simple charts that are used to display data in a compact format. Indicators: Indicators are graphical elements that are used to represent data, such as a traffic light or stop sign. You can use indicators to represent data such as pass/fail results or performance levels. These are just some of the visual report parts you can add to an SSRS report. By combining these parts, you can create powerful and visually appealing reports that communicate data and insights in a meaningful way. Using SQL Reporting Services For Data Visualisation SQL Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It is used to produce formatted reports with data from SQL Server databases. Reports can be generated in various file formats including Excel, XML and PDF. SSRS can be used to design and develop reports that can be published directly to a web application or embedded into an application. It also provides features such as caching, subscriptions, scheduling and security that make it easy to manage and distribute reports. What Is SQL Server Data Tools And Report Builder SQL Server Data Tools (SSDT) and Report Builder in SQL Server Reporting Services (SSRS) are both development environments for creating reports, but they serve different purposes and have different target audiences. SSDT is a more comprehensive development environment for building and deploying data-tier applications, including SQL Server databases and report server project reports. It provides a rich set of tools for designing, developing, and deploying databases, including a visual designer for creating tables, relationships, and data sources. It also provides a robust environment for creating and deploying reports, including a report designer, report builder, and a visual studio-based development environment. Report Builder, on the other hand, is a more focused report-authoring tool, designed for business users and report authors who need to create ad-hoc reports. Report Builder provides a more intuitive and user-friendly interface for creating reports, and it is designed to be easy to use for users who do not have extensive experience with SQL Server or data analysis. In summary, SSDT is a more comprehensive development environment for both database and report development, while Report Builder is a simpler and more user-friendly report authoring tool, designed for non-technical users. -- In this demo, we will focus on using the using report builder then publish this to SQL Server Reporting Services SSRS. Create SSRS Reports - Project Development Tutorial To create this report test project, you will need an install SQL Server 2019 and Bennyhoff Products and Serves Sample Data (both are free, please see the links below for help. We will be using SSDT or Business Intelligence Development Studio (BIDS) instead we will focus on report builder. In addition we will be using the report builder graphical interface vs any code in RDL? report definition language. Install And Connect SSMS Install SQL Server Configure Reporting Services Download Sample Data For SSRS Reports In Report Builder, you can connect to data by using a data source. A data source is a connection to a database that defines the data that you want to use in your report. To connect to a data source in Report Builder, follow these steps: Open Report Builder: You can launch Report Builder from the Reporting Services web portal or from the start menu in Windows. Create a New Report: To create a new report, click on the "New Report" button in the Home tab of the ribbon. Define the Data Source: To connect to a data source, you need to create a new data source or use an existing data source. To create a new data source, click on the "Data Source" button in the Home tab of the ribbon and select "New Data Source". Connect to the Database: To connect to the database, you need to provide the necessary connection information. This typically includes the server name, database name, user name, and password. You can choose either Windows authentication or SQL Server authentication to connect to the database. Test the Connection: After providing the connection information, click on the "Test Connection" button to verify that Report Builder can successfully connect to the database. Save the Data Source: If the connection is successful, save the data source by clicking on the "Save" button in the Home tab of the ribbon. You should end up with a new data source called "DataSource1" Once you have created a data source, you can use it to connect to the data in your report. You can use the Report Builder's Report Designer to create tables, matrices, charts, and other report items that retrieve and display data from the data source. Defining Data Source And Dataset A dataset in SQL Server Reporting Services (SSRS) is a collection of data from one or more data sources that is used to populate a report. You can create a dataset in Report Builder by following these steps: Create a New Dataset: To create a new dataset, click on the "Dataset" button in the Home tab of the ribbon and select "New Dataset". Select the Data Source: In the "Dataset Properties" dialog box, select the data source that you want to use for your dataset. Write the Query: To retrieve data from the data source, you need to write a query. You can write a query using Transact-SQL or another query language that is supported by the data source. In the "Dataset Properties" dialog box, enter the query in the "Query" text box. Note you will need to get the BPS Sample Data To Create This Example For this report we are going to create a view in SQL Server then query the view. Here is the T-SQL for the view. If you downloaded the sample data the view has already been created. - Called View1 - This is what the view looks like inside SQL Server. Test the Query: To verify that the query is valid, click on the "Validate" button in the "Dataset Properties" dialog box. Save the Dataset: If the query is valid, save the dataset by clicking on the "OK" button in the "Dataset Properties" dialog box. Be sure to click "Text" and type Select * From View_1 for the query. Report Data Pane and Report Layout This is what the install screen will look like before we add any data. We can manually add columns to the report or we can use the Wizards help place data on the canvas before we hit "Run" and preview report. Creating Tables To create a table in SQL Server Reporting Services (SSRS) Report Builder, you can follow these steps: Open Report Builder: To open Report Builder, go to the Start menu and find the "Report Builder" option. Click on it to open the application. Create a new report: Once you have opened Report Builder, click on the "File" menu and select "New" to create a new report. Add a data source: To add a data source to your report, click on the "Home" tab, and then select "Data sources" from the left-hand panel. In the data source dialog box, provide the necessary information to connect to your data, such as the server name, database name, and authentication information. Create a dataset: Once you have added a data source, you can create a dataset by clicking on the "Home" tab, and then selecting "Datasets" from the left-hand panel. In the dataset dialog box, select the data source you created earlier, and then specify the query you want to use to retrieve data from your data source. Insert a table: To insert a table into your report, click on the "Insert" tab, and then select "Table." You will be prompted to choose the data source and dataset you want to use to populate the table. Add columns to the table: To add columns to the table, select the table in the report design surface, and then right-click on it. From the context menu, select "Insert Column." You can add as many columns as you need, and you can format each column to display the data in the way you want. Preview the report: To preview the report, click on the "View" menu, and then select "Preview." This will show you how the table will look when the report is run. Once you have completed these steps, you will have created a table in your report. You can further customize and format the table as needed, and add additional tables, charts, and other elements to your report as needed. Using The Table Wizard To Create SSRS Reports SQL Server Reporting Services (SSRS) Offers Several Types Of Charts That You Can Use To Visualize Your Data. Some Of The Most Commonly Used Charts In SSRS Include: Column chart: A column chart is used to represent data in a vertical bar format. It is useful for comparing data between categories. Bar chart: A bar chart is similar to a column chart, but the bars are horizontal instead of vertical. This type of chart is useful for comparing data between categories. Line chart: A line chart is used to show trends over time or across categories. The data points are connected with a line to help visualize the changes in the data. Pie chart: A pie chart is used to represent data as a proportion of the whole. Each category is represented as a slice of the pie, with the size of the slice representing the size of the category. Shown below is the chart preview wizard. Area chart: An area chart is similar to a line chart, but the space between the line and the x-axis is filled with color. This type of chart is useful for showing trends over time or across categories. Scatter chart: A scatter chart is used to display the relationship between two sets of numerical data. The data points are represented as individual markers on the chart. Stacked column chart: A stacked column chart is used to represent data as a proportion of the whole, but with multiple categories. Each category is represented as a separate column, with the height of the column representing the size of the category. Stacked bar chart: A stacked bar chart is similar to a stacked column chart, but the bars are horizontal instead of vertical. Funnel chart: A funnel chart is used to represent the stages in a process, with the size of each stage representing the size of that category. These are some of the most commonly used charts in SSRS, but there are many other chart types available, including combination charts, 3D charts, and more. Deploy Reports To The Web Service URL To deploy a report created in SQL Server Reporting Services (SSRS) Report Builder to a report server, you can follow these steps: Open Report Builder: To open Report Builder, go to the Start menu and find the "Report Builder" option. Click on it to open the application. Open the report: In Report Builder, open the report you want to deploy by selecting "File" from the menu bar, and then selecting "Open." Choose the report you want to deploy from the list of available reports. Save the report to the report server: To save the report to the report server, select "File" from the menu bar, and then select "Save As." In the "Save As" dialog box, select "Report Server" as the location, and then enter the URL of the report server in the "Server Name" field. Provide the report server credentials: If prompted, enter your report server credentials, such as your username and password, to access the report server. Choose the target folder: In the "Save As" dialog box, select the folder on the report server where you want to save the report. If the folder does not exist, you can create a new folder by clicking on the "New Folder" button. Publish the report: To publish the report, click on the "Publish" button. This will upload the report to the report server and make it available for use. Once you have completed these steps, the report will be deployed to the report server and will be accessible to users with appropriate permissions. You can then use the Report Manager website or other tools to manage the report, including setting permissions, scheduling report runs, and creating report subscriptions Managing Reports The Report Manager website in SQL Server Reporting Services (SSRS) is the primary tool for managing reports, folders, data sources, and other components in a reporting environment. You can use the Report Manager website to perform the following tasks: Organize Reports: You can create folders to organize reports and other components within the Report Manager. You can also move reports and other components between folders as needed. Manage Permissions: You can set permissions on folders and reports to control who can access and interact with them. You can grant or deny access to individual users and groups, and you can specify the level of access, such as view or edit. Manage Data Sources: You can create, modify, and delete data sources in the Report Manager. You can also specify the connection information, such as the server name, database name, and authentication information, for each data source. Manage Report Properties: You can view and modify the properties of reports in the Report Manager, such as the name, description, and data source. You can also set properties that control the behavior of the report, such as the report execution timeout and caching options. Manage Report Subscriptions: You can create and manage report subscriptions in the Report Manager. A report subscription is a way to schedule a report to run at specific intervals and deliver the results to specific users or groups. Manage Report History: You can view the history of a report in the Report Manager, including information about when the report was run, by whom, and with what parameters. Manage Report Snapshots: You can create and manage report snapshots in the Report Manager. A report snapshot is a saved version of a report that is stored on the report server. You can use report snapshots to quickly view the data in a report without having to run the report. By using the Report Manager website, you can effectively manage and organize your reporting environment, and ensure that your reports are accessible and secure. Scheduling Reports The Report Manager website in SQL Server Reporting Services (SSRS) allows you to schedule reports to run automatically at specified intervals and to deliver the results to specific users or groups. To schedule a report using the Report Manager website, follow these steps: Open the Report Manager website in your web browser. Navigate to the folder that contains the report you want to schedule. Click on the report to open its properties. Click on the "Subscriptions" tab. Click on the "New Subscription" button to create a new subscription. In the "Delivery options" section, select the delivery method, such as email or file share, and enter the necessary information, such as the email addresses of the recipients. In the "Report parameters" section, select the parameter values you want to use for the report. In the "Schedule options" section, select the frequency and start date for the report. Click on the "OK" button to save the subscription. The report will now run automatically according to the schedule you specified, and the results will be delivered to the recipients you specified. You can view and manage your subscriptions by clicking on the "Subscriptions" tab in the report properties. You can also edit or delete subscriptions as needed, and create new subscriptions for other reports.

What Is SQL Reporting Services? Microsoft SQL Server Reporting Services (SSRS) is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. SSRS allows you to create, manage, and deliver a wide range of interactive and printed reports, as well as manage and secure these reports. SSRS Is Not Included In Express or Developer Edition Or Web Express Edition: This is a free, entry-level edition of SQL Server that is designed for small, non-critical applications. It does not include SSRS. Web Edition: This edition of SQL Server is designed for web-facing applications and is optimized for internet use. It does not include SSRS. Developer Edition: This edition of SQL Server is a full-featured version of the product that is intended for developers to use for development and testing purposes. It does not include SSRS. Install SQL Server Reporting Services Obtain And Run the SSRS Installer If you do not have Microsoft SQL Server installed you will need to do that first, as the SSRS installer prompts you to create a report server database You can download the download installation media for SSRS below https://www.microsoft.com/en-us/download/details.aspx?id=104502 Here are the SQL Server reporting services install prompts When You Are Asked For The Key SQL Server Key You can obtain the key by running the Microsoft SQL Server from the Microsoft Download Center to obtain download compatible installation media. Microsoft SQL Server SQL Reporting Services Setup To configure Microsoft SQL Server Reporting Services, you can follow these steps: Open the Reporting Services Configuration Manager: You can find it in the Microsoft SQL Server program group. Connect to the report server: In the Configuration Manager, click on the "Connect" button and enter the report server name and database connection information. Service Account Configuration Window Accounts Explanation Domain user accounts A domain user account is recommended because it isolates the Report Server service from other applications. Running multiple applications under a shared account, such as Network Service. If you use a domain user account, you have to change the password periodically if your organization enforces a password expiration policy. local Windows user account Avoid using a local Windows user account. Local accounts typically don't have sufficient permission to access resources on other computers. For more information about how using a local account limits report server functionality, see Considerations for Using Local Accounts on the Microsoft website. Virtual Service Account Virtual Service Account represents the Windows service. It is a built-in least-privilege account that has network log-on permissions. This account is recommended if you don't have a domain user account available or if you want to avoid any service disruptions that might occur as a result of password expiration policies. Network Service If you select Network Service, try to minimize the number of other services that run under the same account. A security breach for any one application compromises the security of all other applications that run under the same account. Report Server Database: There are two options. Create report server database: The "Create Report Server Database" option in SQL Server Reporting Service Configuration Manager is used to create a new create report server database. for storing report data and configuration information. The Report Server database is an essential component of the SSRS architecture as it acts as a repository for reports, data sources, security settings, and other metadata. The Report Server database can connect either by SQL Server Account or SQL Server authentication. When you create a Report Server database, you specify the database server and the database name, and then the Configuration Manager creates connections to the SQL server instance of the database and configures it for use with SSRS. The process involves creating the necessary tables, stored procedures, and other database objects needed by SSRS. Once the database is created and configured, you can use it to store and manage reports and other data used by SSRS. Use An Existing Database: Use an existing database Databases The create new Report Server Database screen is where we select the databases ReportServer and ReportServerTempDB. Configure The Web Service URL: Specify the URL for the report server web service. You can use either the default URL or a custom URL. Host name A TCP/IP network uses an IP address to uniquely identify a device on the network. There is a physical IP address for each network adapter card installed in a computer. If the IP address resolves to a host header, you can specify the host header. If you are deploying the report server on a corporate network, you can use the network name of the computer. Port A TCP port is an endpoint on the device. The report server will listen for requests on a designated port. Virtual directory A port is often shared by multiple Web services or applications. For this reason, a report server URL always includes a virtual directory that corresponds to the application that gets the request. You must specify unique virtual directory names for each Reporting Services application that listens on the same IP address and port. SSL settings URLs in Reporting Services can be configured to use an existing TLS/SSL certificate that you previously installed on the computer. For more information, see Configure TLS Connections on a Native Mode Report Server. Once you have completed your configuration you must select apply, you know your settings have been successfully applied then you see a buck of green checkboxes below the results. Configure Email Settings (optional): So let me start with this using a 365 account will not work unless you configure office 365. You can find setup instructions here. However, most network admins will simply backhand you if you suggest this to them. Do not even think about setting up a simple SMTP Mail server on your network, this quickly turns into a resume-producing event when it goes bad and someone spams your entire company with a graphic e-mail. I have used MailGun with success and avoided link below. https://documentation.mailgun.com/en/latest/quickstart-sending.html#how-to-start-sending-email Start the Reporting Services: After configuring the settings, start the Reporting Services by clicking on the "Start" button in the Configuration Manager. Backup Encryption Key To back up an encryption key for SQL Server Reporting Services (SSRS), you can use the following steps: to the Open SQL Server Reporting Services Configuration Manager. Click on the "Encryption Keys" tab. Click "Backup" to open the Backup Encryption Key Wizard. Enter a password to protect the backup file. This password will be required when you restore the encryption key. Select the location where you want to save the backup file. Click "Finish" to complete the backup process. It's important to keep the backup file in a secure location, as it contains sensitive information and can be used to gain access to your reports and data sources. Setup Issues Open the Report Server Configuration Manager validate the user that you added to access the reporting database server. Then open Server Management Studio and verify that user has permission to the database. Verify you can connect SQL Server Instance with SQL Server Management Studio and that the service is started. You Opened the Web Portal URL instead of the Web Service URL

Overview Locking, blocking and deadlocks are related concepts in SQL Server that deal with controlling concurrent access to data in a database. Concurrent access to data in a database refers to multiple users or processes accessing the same data simultaneously. This can occur when multiple transactions are executed at the same time, each trying to read or modify the same data. To ensure data integrity and consistency, a database management system like Microsoft SQL Server uses mechanisms such as locking and blocking to control access to data and prevent conflicting updates from occurring. Proper management of concurrent access is important for the performance and stability of a database system. Locking: Locking is a mechanism used by SQL Server to control concurrent access to data and ensure data integrity and consistency. Locks are acquired on data when a transaction starts and released when it ends. Blocking: Blocking problems when one transaction holds a lock on a piece of data that another transaction wants to access. The second transaction has to wait for the first to release the lock before it can continue. This can lead to performance issues and delay the completion of transactions. Deadlocks: Deadlocks occur when two or more transactions are blocked and each is waiting for the other to release a lock, resulting in a circular wait. This can lead to an indefinite wait situation and requires manual intervention to resolve. Deadlocks can be identified and resolved using SQL Server's Dynamic Management View (DMV) or by using SSMS, performance monitor or custom code as discussed below. What Is The Difference Between Deadlock And Blocking? Blocking: Blocking occurs when one transaction holds a lock on a piece of data that another transaction wants to access. The second transaction has to wait for the first to release the lock before it can continue. This can lead to performance issues and delay the completion of transactions. Deadlocks are an unavoidable reality in SQL Server systems. They occur when two or more transactions become locked and must wait for the other to release a lock - creating an indefinite waiting situation that can cripple system performance issues and cause performance degradation unless promptly addressed. Fortunately, built-in tools like Deadlock Graphs allow users to identify and swiftly resolve these potentially with solid database administration and the use of the alerting process described below. Locking Detail SQL Server enables users to access data safely and securely by using locks. These range from shared locks, allowing multiple readers simultaneous access, to exclusive ones that prohibit any other user activity - ensuring only one person can interact with the guarded data at a time. Types Of Locking In SQL Server In SQL Server, there are two types of locking mechanisms: shared locks and exclusive locks. Shared Locks: A shared lock allows multiple transactions to read a piece of data, but only one transaction can hold an exclusive lock on the same piece of data at a time. Shared locks are used when a transaction needs to read data, but does not need to modify it. Exclusive Locks: Exclusive locks are used when a transaction needs to modify data. Exclusive locks prevent other transactions from reading or modifying the same data until the lock is released. Lock Escalation Lock escalation is a process in SQL Server where many fine-grained locks (e.g. row-level locks) are consolidated into fewer, higher-level locks (e.g. table-level locks) to reduce lock overhead and improve performance. Lock escalation is triggered when a transaction acquires a large number of locks on a single resource, such as a table. In such cases, SQL Server automatically converts the fine-grained locks into a fewer number of coarser-grained locks, freeing up system resources and reducing lock contention. However, lock escalation can also cause blocking and affect the performance of other transactions that need to access the same data. Thus, it's important to monitor and manage lock escalation to balance its benefits and drawbacks in a database system. The Infamous (NOLOCK) and Other Locking Hints In SQL Server, locking hints are used to specify the type of lock that should be acquired on a table or row when a query is executed. Locking hints can be used to override the default locking behavior of the SQL Server query optimizer. Some examples of locking hints are Below: NOLOCK: Specifies that a query should not acquire any locks on the table or row being accessed. This can improve performance, but can also lead to dirty reads (reading data that is in the middle of being modified) or non-repeatable reads (reading data that has been modified since it was last read). READPAST: Specifies that a query should skip over rows that are locked, rather than waiting for the lock to be released. This can be used to avoid blocking in read-intensive workloads. UPDLOCK: Specifies that an update lock should be acquired when a query is executed. This prevents other transactions from modifying the data while the query is running, but allows other transactions to read the data. TABLOCK: Specifies that a table lock should be acquired when a query is executed. This prevents other transactions from accessing the table while the query is running. ROWLOCK: Specifies that a row-level lock should be acquired when a query is executed. This prevents other transactions from accessing the same row while the query is running. It's important to note that Locking hints can affect the performance and scalability of a database, so they should be used judiciously and only when necessary. It is also important to test the impact of locking hints on your workload and to monitor the locking behavior of your queries to ensure that they are not causing contention or blocking. How Can I Prevent SQL BLocking With NOLOCK In T-SQL, the NOLOCK hint can be used to specify that a query should not acquire any locks on the table or row being accessed. This hint can be added to a SELECT statement by including the keyword "WITH (NOLOCK)" in the FROM clause, like this: SELECT * FROM MyTable WITH (NOLOCK) It can also be added to a specific table within a JOIN statement like this: SELECT * FROM MyTable1 JOIN MyTable2 WITH (NOLOCK) ON MyTable1.Id = MyTable2.Id It's important to note that using the NOLOCK hint can improve query performance, but it can also lead to dirty reads (reading data that is in the middle of being modified) or non-repeatable reads (reading data that has been modified since it was last read). If your application requires accurate and consistent data, you should avoid using the NOLOCK hint. Also, in case of a high concurrency environment, it could lead to high contention and negatively impact the overall performance. To Detect Locking In SQL Server Profiler, Follow These Steps: Launch SQL Server Profiler and create a new trace. In the Trace Properties dialog, select the Events Selection tab. Select the Locks category and the Lock:Timeout and Lock:Deadlock events. Run the trace and monitor the events to detect any lock timeouts or deadlocks. Save the trace results to a table or a file for further analysis. Blocking Transactions Blocking is a situation that occurs when one process holds a lock on a resource and another process tries to acquire a conflicting lock on the same resource. The second process is blocked until the first process releases the lock. Blocking can occur when two processes are trying to update the same data concurrently, when one process is trying to read data that is being modified by another process. Blocking can have a negative impact on the performance of a database, as it can cause processes to wait for locks to be released, leading to delays in query execution. To address blocking issues, you can use tools such as the sp_who2 system stored procedure or the sys.dm_exec_requests DMV to identify the processes that are causing blocking and determine the cause of the blocking. You can then take steps to optimize the queries or indexes to reduce or eliminate the blocking. In the image below we can see two transactions try to access the same resources. One session (in green) locks acquired and the other is processes blocking transactions (red) Creating A Blocking Situation To Observe Blocked Processes Blocking situations can occur when two or more transactions are trying to access the same data at the same time. This can be both a nuisance and a major problem for database administrators. To test for blocking conditions, you can use a Transact-SQL (T-SQL) script. In this blog post, we will look at how to create a blocking situation test case with T-SQL. Step 1: Create New Session The first step is to create a new session in your database server environment. This can be done through the SQL Server (SSMS) by going to File > New > Query with Current Connection. Once the session is created, you can open up two separate query windows in SSMS and then connect each window to your database server environment using the same credentials. Step 2: Set Isolation Level Once you have opened up both query windows, you need to set the isolation level of each query window so that it is not blocked by other queries running in parallel. To do this, you need to run the following command in each query window: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; This command will allow the queries running in both sessions to run without being blocked by each other. Step 3: Run Queries Simultaneously BEGIN TRAN SELECT * FROM YourTable WITH (TABLOCKX, HOLDLOCK) WAITFOR DELAY '00:10:00' -- 10 minutes ROLLBACK TRAN Then in a 2nd SQL Server window, add the Blocked SQL Statement, to show the normal behavior or blocking SELECT * FROM YourTable Now that our isolation levels are set, we can now begin running our queries simultaneously from both query windows. For example, let’s say that we want to update some data in one table while also performing an insert into another table at the same time. We would run both of these queries simultaneously from their respective query windows and see if any blocking occurs as a result of us running them together at the same time. If there is no blocking occurring then our test case has been successful! blocking 35-second video showing blocking and the blocking report. Blocked Process Report In SQL Server Professional What Does the Blocked Process Report Show? The Blocked Process Report shows detailed information about all blocked processes on your server at that time—including the host name of each computer involved in the blocked process; their login times; the SPID (server process ID); status; wait type; waits; resource description; current command executed by the process; and more. Using this information, you can determine which process is blocking other processes from completing their tasks correctly—which gives you the ability to take corrective action quickly and efficiently. How Can I Interpret The Results? Interpreting the results of a Blocked Process Report isn’t difficult—as long as you know what to look for. To start, you should look for processes with a wait type of “LCK_M_X” or “LCK_M_SCH-M” since these are typically associated with locking issues due to blocking. Once you have identified those processes, focus on their SPIDs, logins, hosts names, status codes, resource descriptions, current commands executed by each process—all of which will help you pinpoint where there might be an issue with blocking. From there, you can start troubleshooting any issues accordingly until they are resolved. Types Of Blocking In SQL Server Blocking occurs when a transaction requests a lock on a piece of data that is already locked by another transaction. This can happen in several ways: Read/Write Blocking: A transaction that is trying to read data is blocked by a transaction that has an exclusive lock on the same data. Similarly, a transaction that is trying to modify data is blocked by a transaction that has a shared or exclusive lock on the same data. Intent Locks: An Intent lock is a type of lock that is used to indicate that a transaction intends to read or modify a specific range of data. Key-Range Locks: A Key-Range lock is a type of lock that is used to protect a specific range of data within a table or index. Deadlocks: Deadlocks occur when two or more transactions are blocking each other, each waiting for the other to release a lock. It is important to understand how locking and blocking works in SQL Server, so that you can write efficient, concurrent code that avoids unnecessary blocking and deadlocks. Detecting Blocking In SQL Server Management Studio To detect blocking in your SQL Server instance using SQL Server Management Studio (SSMS), you can follow these steps: Open SSMS and connect to the SQL Server instance. In the Object Explorer, expand the "Management" node and click on "Activity Monitor". In the Activity Monitor window, click on the "Processes" tab. This will show you a list of all processes that are currently running on the server. To view details about the blocking processes, click on the "Blocking Processes" column header to sort the processes by the number of blocking processes. To view more information about a specific blocking process, right-click on the process and select "View Process Detail" from the context menu. This will open a new window showing the details of the selected process, including the blocking SPID and the query that is being executed. An Overview of the Blocked Process Report and How to Interpret It A database administrator’s job often revolves around identifying, troubleshooting, and resolving performance issues. One tool that can help with this process is the Blocked Process Report in SQL Server. This report provides detailed information about any blocking issue on a server, which helps you isolate and address them quickly. Let’s break down what the report includes and how to interpret it. What Does the Blocked Process Report Show? The Blocked Process Report shows detailed information about all blocked processes on your server at that time—including the host name of each computer involved in the blocked process; their login times; the SPID (server process ID); status; wait type; waits; resource description; current command executed by the process; and more. Using this information, you can determine which process is blocking other processes from completing their tasks correctly—which gives you the ability to take corrective action quickly and efficiently. How Can I Interpret The Results? Interpreting the results of a Blocked Process Report isn’t difficult—as long as you know what to look for. To start, you should look for processes with a wait type of “LCK_M_X” or “LCK_M_SCH-M” since these are typically associated with locking issues due to blocking. Once you have identified those processes, focus on their SPIDs, logins, hosts names, status codes, resource descriptions, current commands executed by each process—all of which will help you pinpoint where there might be an issue with blocking. From there, you can start troubleshooting any issues accordingly until they are resolved. Detecting Blocking In Code In Your SQL Server Instance It's important to monitor for blocking on a regular basis to ensure that the performance of the database is not impacted by blocking issues. If you do identify blocking, you can use tools such as the sys.dm_exec_requests DMV to investigate the cause of the blocking and take steps to optimize the queries or indexes to reduce or eliminate the blocking. I have also attached code in Github to detect blocking and send an HTML e-mail when blocking occurs for more than 5 minutes. Alternatively, you can use the sp_who2 system stored procedure to view information about blocking processes. This stored procedure returns a result set that includes the SPID, Status, Blocking, and Wait Time columns, which can be used to identify blocking processes and troubleshoot performance issues. Detecting Blocked Processes With SP_WHO2 SP_WHO2 is a stored procedure in SQL Server that is used to display information about current users and processes connected to a SQL Server instance. It provides information such as the login name, host name, process ID, and status of the connections. To use SP_WHO2, you can execute the following query "EXEC SP_WHO2" in the query window of SQL Server Management Studio. This will display all the information about the current connections to the and Blocked Processes. Killing Blocking Processes In SQL Server Management Studio Once you have found the blocking SQL statement that is causing issues, you can stop that process by using the Kill command - See my blog here for more information and end the blocked processes. How To Kill A Process In SSMS Deadlock A deadlock in SQL Server occurs when two or more transactions are blocked and are unable to proceed because they are each waiting for one of the other transactions to release a lock. This results in a situation where neither transaction can move forward, creating a "deadlock" situation. Deadlocks can cause performance issues and may also cause certain transactions to fail. SQL Server can detect and resolve deadlocks automatically, but they can also be prevented by carefully designing and indexing tables, and by using the appropriate locking hints and isolation levels in the Transact-SQL code. Additional resources: You can also purchase my product, Product SQL Maintenance And Management, if you want to Troubleshoot blocking, expand standard reports, If lock based concurrency and lock escalation are not your true calling. Let me help you administer your SQL Server with my 20 years of experience and my software package that has the following built-in... Blocked Process Report, Locking and Blocking Report and Extended Events Session Monitoring --Cheers Mike B

Table Visualization The table visualization in Power BI is a type of visualization that displays data in a tabular format, with rows and columns. It is one of the most basic and commonly used visualizations in Power BI and is useful for displaying large amounts of data in a clear and organized way. The table visualization in Power BI allows you to select the columns and rows of data that you want to display, and also to format the table and customize the appearance of the data. You can also apply filters and sorting to the data in the table, to help you find the information you need. You can also add a table visualization to a report and use it to display data alongside other types of visualizations such as charts, maps, and gauges. The table visualization in Power BI also allows you to add drill-through functionality, which is a way to explore your data in more detail. You can use drill-through functionality to go from a high-level view of your data to a more detailed view of specific data points. Overall, the table visualization in Power BI is a useful and versatile tool for displaying and exploring data in a tabular format. It is a great way to present large amounts of data in a clear, organized manner and it's also useful for data exploration and drill-through analysis. How To Add A Table Giff Visualizing A Table: Importing data: The first step is to import data into Power BI. You can import data from various sources such as an Excel spreadsheet, a CSV file, or a SQL database. Transforming data: Once the data is imported, you can use the Power Query Editor to clean, transform and manipulate the data. Creating a table: After you have transformed the data, you can load it into a table. You can select the columns and rows of data that you want to include in the table, and then format the table to customize the appearance of the data. Creating a report: Once you have created a table, you can add it to a report by dragging and dropping it from the Fields pane to the report canvas. Applying filters and sorting: You can also apply filters and sorting to the data in the table, to help you find the information you need. Customizing the appearance of the table: You can customize the appearance of the table by changing the font, font size, color, and other formatting options. Adding a table to other visualizations: You can also add a table visualization to a report and use it to display data alongside other types of visualizations such as charts, maps, and gauges. Exploring your data: You can also use the table visualization to explore your data in more detail by adding drill-through functionality. Matrix Visualization A matrix in Power BI is a type of visualization that is similar to a table, but with the added ability to group data by multiple columns. It is also called a "pivot table" or "cross-tabulation" and it's a useful tool for summarizing and comparing large amounts of data. A matrix in Power BI allows you to organize and display data in a grid format, with rows and columns. You can select the columns of data that you want to display, and then group the data by one or more columns to create a hierarchical view of the data. For example, you can group sales data by product category, and then by product sub-category, to see how different products are performing within each category. You can also add a measure to the matrix, such as total sales, to see how the data is aggregated at each level of the hierarchy. A matrix in Power BI also allows you to add subtotals and grand totals, and customize the appearance of the data. You can also apply filters and sorting to the data in the matrix, to help you find the information you need. Like the table visualization, you can add a matrix visualization to a report and use it to display data alongside other types of visualizations such as charts, maps, and gauges. Overall, a matrix in Power BI is a powerful and versatile tool for summarizing and comparing large amounts of data, by grouping it in a hierarchical way. It's a great way to present data and analyze it in a clear and organized manner. 20 Tips And Trix For Matrix Visualization To Create A Matrix Visualization: Using a matrix in Power BI involves several steps: Importing data: The first step is to import data into Power BI. You can import data from various sources such as an Excel spreadsheet, a CSV file, or a SQL database. Transforming data: Once the data is imported, you can use the Power Query Editor to clean, transform, and manipulate the data. Creating a matrix: After you have transformed the data, you can create a matrix by dragging and dropping fields from the Fields pane to the Rows and Columns areas of the matrix. Grouping data: Once you have created a matrix, you can group data by one or more columns to create a hierarchical view of the data. You can group data by dragging and dropping fields from the Fields pane to the Rows and Columns areas of the matrix. Adding a measure: You can also add a measure, such as total sales, to the matrix, to see how the data is aggregated at each level of the hierarchy. Creating a report: Once you have created a matrix, you can add it to a report by dragging and dropping it from the Fields pane to the report canvas. Applying filters and sorting: You can also apply filters and sorting to the data in the matrix, to help you find the information you need. Customizing the appearance of the matrix: You can customize the appearance of the matrix by changing the font, font size, color, and other formatting options. Adding a matrix to other visualizations: You can also add a matrix visualization to a report and use it to display data alongside other types of visualizations such as charts, maps, and gauges.

What Is Python Scripting Python is a high-level, interpreted programming language that is widely used for web development, scientific computing, data analysis, artificial intelligence, and many other tasks. It is known for its simple and easy-to-read syntax, making it a popular choice for beginners and experienced developers alike. Python also has a large and active community which creates and maintains a wide variety of libraries and modules that can be easily imported and used in Python programs. Some of the key features of Python include: Dynamically typed Supports multiple programming paradigms (procedural, object-oriented, functional) Has a large standard library Has an interactive interpreter Can be used for a wide range of tasks, such as web development, data science, and artificial intelligence Power BI Desktop And Python Integration To enable Python scripting on your Power BI Desktop. To do this, just go to File, then click on Options and Settings. Under Options, you'll find Python Scripting. Always make sure that you set the right installation path for Python. If Python script options page appears, supply or edit your local Python installation path under Detected Python home directories. Here is a Vido Showing You How To Do This: Ways To Use Python In Power Integration Using the "Python Script" Using the "Python Script" data connector: This allows you to connect to a data source using a Python script and then use that data in your Power BI report. Using the "Python Visual" sing the "Python Visual" in Power BI: This allows you to create custom visualizations in Python and then use them in your Power BI report. Embedding a Python script in Power Query: Embedding a Python script in Power Query: Power Query is a data connection tool that is integrated into Power BI. It allows you to perform data transformations and cleaning using a visual interface or M language. By using the Python script in Power Query you can perform more complex data manipulation. Automating Power BI tasks with Python: Automating Power BI tasks with Python: You can use Python libraries such as powerbi-python to automate tasks such as creating reports, managing datasets, and more. By using Python in Power BI you can leverage the powerful data manipulation and visualization capabilities of Python with the interactive reporting and sharing capabilities of Power BI. It's important to note that you need to have Python installed on your computer and have the necessary libraries installed to be able to use Python in Power BI. Benefits Of Python In Power BI: Data Preparation: Python has a wide variety of libraries and tools for data manipulation and cleaning, which can be used to prepare data for analysis in Power BI. Advanced Analytics: Python has a large number of libraries for advanced analytics, such as machine learning, natural language processing, and computer vision, which can be used to add additional functionality to Power BI. Automation: Python can be used to automate the process of data extraction, transformation, and loading (ETL) into Power BI, making it easier to maintain and update the data pipeline. Integration: Power BI and Python can be integrated to allow for the use of Python's libraries and functionality within Power BI and to easily visualize and share results. Scalability: Python is a versatile and powerful language that can handle large amounts of data, making it a great choice for data analysis and visualization tasks in Power BI. Community: Python has a large, active and supportive community which allows you to leverage the work of others and to find solutions to problems you encounter quickly. Importing Data With Python To import data using a Python script in Power BI, you can use the Power BI Python Data Connector. The connector allows you to connect to external data sources using Python, and then import the data into Power BI. Here are the general steps to do this: Install the Power BI Python Data Connector by going to the "File" menu in Power BI Desktop, selecting "Options and settings" and then "Python scripting". Once the connector is installed, you can use Python code to connect to a data source and retrieve data. For example, you can use the pandas library to read data from a CSV file or a SQL database. After you've retrieved the data, you can use the powerbi library to import the data into Power BI. The powerbi library provides a push_data method that you can use to send the data to Power BI. Once the data is imported into Power BI, you can use it to create visualizations, reports, and dashboards. You can use the Power BI Python SDK to automate Power BI tasks using Python. You can use the SDK to create reports and dashboards, import data, and perform other tasks programmatically. Please note that the above steps are just an overview, and you will need to have knowledge of Python, Pandas and Power BI SDK to perform the task. Also, you need to have a valid credentials to connect to the external data sources and make sure that the data source is accessible from the machine where you are running the script. How To Install Python and Python Scripting On Your PC There are several ways to install Python on your PC, depending on your operating system and desired setup. Here are a few common methods: Downloading an installer from the official Python website: Downloading an installer from the official Python website: Go to the official Python o avoid these issues, use the official Python distribution from https://www.python.org and download the appropriate installer for your operating system. Once the download is complete, run the installer and follow the prompts to install Python. Using the package manager for your operating system: Using the package manager for your operating system: On Windows, you can use the Chocolatey package manager to install Python. On MacOS and Linux, you can use Homebrew or apt-get, respectively. Using the Anaconda distribution: Using the Anaconda distribution: Anaconda is a distribution of Python that includes many popular libraries for data science and machine learning. It also includes a package manager called aconda that makes it easy to install and manage packages. Once Python is installed, you can check the version of Python by running python --version in the command line. It's important to note that when you install python it comes with the Python Standard Library, which includes a large number of modules that provide useful functionality such as file I/O, string manipulation, and more. However, many additional libraries and modules are available that are not included in the standard library, so you may need to install them separately. Can Python Be Used With Power BI Gateway ? Yes, Python can be used with Power BI Gateway in order to fetch and manipulate data from different sources and then connect it to Power BI. Power BI Gateway is a service that allows you to refresh your Power BI data when it's coming from sources that are not supported by Power BI or when the data is located on-premises. You can use Python to fetch data from different sources, clean and transform it and then use the Power BI Gateway to connect to it. One way to do this is to write a Python script that connects to the data source, performs the necessary data manipulation and then saves the data in a format that Power BI can connect to (such as a CSV or Excel file). Then you can set up a scheduled refresh for the dataset in Power BI, which will automatically update the data in Power BI when the data changes in the source. Another way is to use Python to create an API that serves the data and then connect Power BI to this API using the Web connector. It's important to note that for the above methods to work, you need to have the Power BI Gateway installed and configured on your machine, and also have Python installed and the necessary libraries to perform the data manipulation.

Crystal Reports Server offers an unparalleled server-based approach to creating, managing, and delivering reports with business intelligence content. Leveraging the powerful Crystal Reports engine at its core, organizations can effortlessly produce reportage that is accessible across multiple web channels or conveniently sent via email. If Crystal Report Server Is In Your Menu Of IT Options, Keep Reading. Some Of The Key Features Of Crystal Server Include: Report scheduling and distribution: Crystal Reports Server allows administrators to schedule reports to run at specific times and to automatically distribute the reports to a specified list of recipients via email or to a shared location. Report management: Crystal Reports Server provides a central location for storing and managing reports, making it easy for users to find and access the reports they need. Security: Crystal Reports Server provides robust security features, such as user authentication and role-based access control, to ensure that only authorized users can access and view reports. Interactivity: Crystal Reports Server allows users to interact with the reports by providing features such as drill-down, sorting, and filtering, which allows users to quickly find the information they need. Integration: Crystal Reports Server can be integrated with other systems, such as business applications, enterprise resource planning (ERP) systems, and customer relationship management (CRM) systems, to provide a comprehensive view of business data. Scalability: Crystal Reports Server can handle large numbers of users and reports, and it can be deployed on a variety of platforms, including Windows, Linux, and UNIX. Overall, Crystal Reports Server is an ideal solution for organizations that need to create, manage, and distribute large numbers of reports, and want to provide their users with a centralized location for accessing those reports. Here Is A Video Overview Of Crystal Report Server How Is Crystal Server Licanced Crystal Reports Server is licensed based on the number of users who will be accessing the server. The licensing model is concurrent user-based, which means that the number of licenses required is determined by the maximum number of users who will be accessing the server at the same time. There are two types of licenses available for Crystal Reports Server: Named User License: This license is assigned to an individual user and allows that user to access the server from multiple devices. Concurrent User License: This license is not assigned to a specific user, but rather allows a specific number of users to access the server at the same time. In addition to the user licenses, Crystal Reports Server also requires a separate license for the server itself, which is based on the number of processors and cores used by the server. It's also worth noting that there are different versions of Crystal Reports Server, such as Crystal Reports Server 2016, Crystal Reports Server 2020, and so on. The features and pricing of the different versions vary, so it's important to check the specific version of Crystal Reports Server to understand its licensing model. Overall, Crystal Reports Server is licensed based on the number of users who will be accessing the server, with a separate license required for the server itself. The licensing model is concurrent user-based, and there are different versions of Crystal Reports Server available with different features and pricing. More Info and Pricing At SAP

My Prices & Rates Jan 2023 My rates are $195 Per hour, and non-profits at $150 per hour. Products 30 Minute Consultation (1 Per Customer) $50 Pre-Paid Package of 15 hours (I only sell hours in increments of 15) - $2925 SQL Server Audit $995 SQL Server M&M $995 Per Month for 3 servers The Package comes with 5 hours of consulting per month. Package hours are lost if they are not used. Contracts: Bennyhoff Products and Services can provide contracts if you do not have your own. Sample Projects: Smart project management Smart project management is a method of managing projects that focuses on using data and technology to make informed, evidence-based decisions. The goal of smart project management is to improve the efficiency and effectiveness of project management processes, and to deliver better results for the project. There are several key principles of smart project management: Define clear goals: Smart project management starts with defining clear, measurable goals for the project. This helps to ensure that everyone is working towards the same objectives and that progress can be tracked. Use data to inform decision making: Smart project managers use data to inform their decision making. This can include metrics on project performance, customer feedback, and market trends. Use technology to streamline processes: Smart project managers use technology to streamline processes and reduce the need for manual work. This can include project management software, collaboration tools, and automation. Continuously improve processes: Smart project managers are always looking for ways to improve their processes and deliver better results. This can involve using data to identify bottlenecks and inefficiencies, and implementing changes to address them. Overall, smart project management is a proactive and data-driven approach to managing projects that can help teams deliver better results and achieve their goals more efficiently. Project Management Software Jira is a powerful project management tool that can help you plan, track, and release software more efficiently. Here are some reasons why you might consider using Jira for your project management: Customizable workflows: Jira allows you to define and customize your own workflows to fit the needs of your team and your project. This can help you streamline your process and make it easier to track progress and identify bottlenecks. Issue tracking: Jira has a robust issue tracking system that allows you to capture and prioritize issues, assign them to team members, and track progress towards resolution. This can help you stay on top of problems and ensure that they are addressed in a timely manner. Reporting and analytics: Jira provides a range of reports and charts that can help you understand how your team is performing and identify areas for improvement. You can also use integrations with other tools, such as Google Analytics, to get even more insights into your project. Collaboration: Jira includes a range of collaboration features, such as the ability to @mention teammates, assign tasks, and add comments to issues. This can help your team stay informed and aligned on the project. Integrations: Jira integrates with a wide variety of tools and services, such as Git, Slack, and Trello, which can help you streamline your workflow and reduce the need for manual work. Billing Management With Tempo Tempo is a third-party app for Jira that provides a range of time tracking and project management features. It is designed to help teams track and manage their time more effectively, and to provide insights into how time is being spent on different projects and tasks. Some of the features of Tempo include: Time tracking: Tempo allows team members to track their time spent on tasks and projects, and provides tools to help them accurately capture their time. Resource planning: Tempo provides tools to help managers plan and allocate resources more effectively, including features for forecasting and capacity planning. Billing and invoicing: Tempo includes tools to help teams track and invoice for their time and work, including support for multiple billing rates and currencies. Reporting and analytics: Tempo provides a range of reports and charts that can help managers and team members understand how time is being spent and identify areas for improvement. Stats: My average customer uses Bennyhoff Products And Services for 53 months! Resume Linked In Profile - https://www.linkedin.com/in/mike-bennyhoff-78317282/

SQL Server Reporting Services (SSRS) SQL Server Reporting Services (SSRS) is a powerful platform that enables organizations to construct, manage and deliver custom reports with ease. SSRS provides an extensive selection of tools which are integrated into Microsoft SQL Server Database technology—making it the most effective way for businesses to generate actionable insights from their data. This database technology allows you to create and manage various types of reports, such as tabular, matrix, and graphical reports, as well as ad-hoc reports. Reports can be viewed and exported to various formats such as HTML, PDF, Excel, and CSV. SSRS can connect to various types of data sources including SQL Server, Oracle, and other relational databases, and can also be used to generate reports from OLAP cubes. It allows you to design and create reports using a drag-and-drop interface, and also includes features such as parameters, filters, and sorting to customize the report's data. SSRS also includes a web-based report viewer, which allows users to view and interact with reports via a web browser, and also has a scheduling feature which enables automatic delivery of reports to specified recipients at specified intervals. Additionally, it supports both on-premise and cloud-based deployment and it can be integrated with Sharepoint and Power BI. You can download Reporting Services From Here: https://www.microsoft.com/en-us/download/details.aspx?id=104502 The Report Server Configuration Manager: The Report Server Configuration Manager is a tool in SQL Server Reporting Services (SSRS) that is used to manage the configuration settings for a report server. It allows you to perform a wide range of tasks, including: Configuring the report server database: You can use the Report Server Configuration Manager to connect to the report server database and configure the database connection settings. Setting up SMTP settings: You can use the Report Server Configuration Manager to configure the Simple Mail Transfer Protocol (SMTP) settings for the report server, which is used to send email notifications when reports are delivered or when errors occur. Managing report server security: You can use the Report Server Configuration Manager to configure security settings for the report server, including authentication and authorization settings, and to manage roles and users. Managing report server execution: You can use the Report Server Configuration Manager to configure the execution settings for the report server, such as the maximum number of concurrent connections, the maximum number of running jobs, and the history settings. Managing subscriptions and delivery: You can use the Report Server Configuration Manager to manage the subscriptions and delivery settings for the report server, such as the schedule and delivery options for reports, and to manage the recipients of the reports. Managing the Web service and web portal URLs: You can use the Report Server Configuration Manager to configure the URLs for the web service and web portal of the report server, as well as to configure the virtual directories for the report server. Managing the encryption key: You can use the Report Server Configuration Manager to manage the encryption key that is used to encrypt sensitive data in the report server. Monitoring the report server: You can use the Report Server Configuration Manager to monitor the status of the report server and to view the server logs for troubleshooting purposes. What Tools Do I Use To Create SSRS Reports? There are several tools that can be used to create SQL Server Reporting Services (SSRS) reports: Report Designer: This is a visual tool that is included with the SQL Server Data Tools (SSDT) and allows you to create, design, and test SSRS reports. Business Intelligence Development Studio (BIDS): This is the older version of the Report Designer tool and is included with SQL Server 2008 and earlier versions. Report Builder: This is a stand-alone application that can be used to create ad-hoc reports. It is designed for business users and does not require advanced technical skills. Visual Studio: You can also use Visual Studio to create SSRS reports by installing the SQL Server Data Tools (SSDT) extension. This allows you to use the Report Designer and other tools within Visual Studio to create and test SSRS reports. Power BI: Power BI is a Business Intelligence tool that allows you to create interactive and visual reports, you can also publish Power BI reports to SSRS and use them as a data source for other reports. Third-Party Tools: There are also third-party report design tools available such as Crystal Reports, DevExpress, and Telerik that can be used to create SSRS reports. It's important to note that the tools available to you may depend on the version of SSRS that you are using, and some of them may require additional licenses or subscriptions. You can download SSRS Report Builder Here: https://www.microsoft.com/en-us/download/details.aspx?id=104502 How Is SSRS Different Than Power BI? SQL Server Reporting Services (SSRS) and Power BI are both Microsoft products, but they are designed for different purposes and have different features. SSRS is a server-based reporting platform that is primarily used for creating and delivering traditional, pre-defined reports. It is designed for enterprise-level reporting and is often used in large organizations to generate reports from large data sets and to deliver them to a wide range of users. SSRS allows IT departments to create, manage, and deliver reports centrally, and it also includes advanced features such as scheduling, security, and data visualization. Power BI, on the other hand, is a cloud-based business intelligence platform that is designed for self-service business intelligence. It is geared towards individual business users and smaller teams who need to analyze and visualize data in an interactive and intuitive way. Power BI allows users to connect to various types of data sources, including SQL Server, and create interactive dashboards, reports, and visualizations with minimal IT support. Power BI also offers a range of collaborative features, such as the ability to share and publish reports and dashboards, as well as integration with other Microsoft products such as Excel and SharePoint. In summary, SSRS is a more robust and enterprise-level reporting platform, while Power BI is a more flexible and user-friendly business intelligence platform. They can also be integrated together to provide a comprehensive reporting and analytics solution. You Can Downloads Power BI Here: https://powerbi.microsoft.com/en-us/downloads/ What are the differences between SSRS 2016 and 2019? SQL Server Reporting Services (SSRS) 2016 and SSRS 2019 are both versions of the Microsoft reporting platform, but there are some key differences between the two versions: Modern UI Experience: SSRS 2016 has a classic mode user interface (UI) while SSRS 2019 has a modern UI experience, which is similar to the Power BI service. Mobile Reports: SSRS 2016 has limited mobile report capabilities while SSRS 2019 includes a new mobile report publisher tool that allows you to create and view mobile-optimized reports. KPI: SSRS 2016 supports KPI (Key Performance Indicator) but it was limited, SSRS 2019 has more capabilities for creating and displaying KPIs. Paginated Reports: SSRS 2016 supports paginated reports which are formatted, print-ready reports, SSRS 2019 also supports paginated reports and added new features such as the ability to create and edit reports using the web portal, improved data visualization and new data connectors. Power BI Integration: SSRS 2016 has a limited integration with Power BI while SSRS 2019 has a deeper integration, allowing you to embed Power BI reports within SSRS reports, and schedule and deliver Power BI reports to a wider range of recipients. Cloud-Ready: SSRS 2019 is designed for cloud deployment, which means that you can deploy SSRS 2019 in Azure and take advantage of the scalability and cost-effectiveness of the cloud. Security Improvements: SSRS 2019 has improved security features such as Always Encrypted, Row-level Security, and Dynamic Data Masking. Support: SSRS 2016 support ended on April 2022, SSRS 2019 is the latest version of SQL Server Reporting Services and will be supported by Microsoft. When Will SSRS 2019 Be Out Of Support By Microsoft The end of support date for SQL Server Reporting Services (SSRS) 2019 has not been announced by Microsoft yet. However, it is important to note that the support timeline for SSRS 2019 will follow the same pattern as previous versions of SSRS. **Screenshot from https://sqlserverbuilds.blogspot.com/ Typically, Microsoft provides Mainstream Support for a new version of SSRS for 5 years after its initial release, and provides Extended Support for an additional 5 years. This means that SSRS 2019 will be in Mainstream Support until the end of its 5 years period, and will be in Extended Support for an additional 5 years after that. It's also important to note that the end of support date for SSRS 2019 will be aligned with the end of support date for the underlying version of SQL Server. As the end of support date for SQL Server 2019 is October 14, 2029, SSRS 2019 will also be out of support on this date. It's recommended to plan and budget for an upgrade to a newer version of SSRS before the end of support date to continue receiving updates, security patches, and support from Microsoft.

SQL Server Analysis Services (SSAS) is a component of the Microsoft SQL Server database software that provides online analytical processing (OLAP) and data mining functionality for business intelligence (BI) applications. It allows you to create and manage multidimensional data structures, called cubes, that can be used to quickly analyze and understand large amounts of data. A cube is a multidimensional data structure that is optimized for fast, complex queries and calculations. It stores data in a hierarchical format, where each level of the hierarchy represents a different aspect of the data, such as time, location, or product. SSAS also supports data mining, which allows you to discover patterns and relationships in your data. It includes several data mining algorithms and it's possible to create custom data mining models to suit your specific needs. It also allows you to create and manage dimensions and hierarchies, which are used to organize the data in a cube and make it easier to navigate and analyze. It also includes a visual design tool, called SQL Server Data Tools (SSDT), which makes it easy to create and manage cubes and dimensions. It supports both on-premise and cloud-based deployment and it can be integrated with other Microsoft products such as Excel and SharePoint, and Power BI. SQL Server Analysis Services (SSAS) is a Microsoft technology for creating and managing multidimensional databases, also known as OLAP (Online Analytical Processing) databases. There are two main editions of SSAS: Standard and Enterprise. The main features of SSAS Standard edition include: Support for creating and managing multidimensional databases Support for creating and deploying cubes, dimensions, and hierarchies Support for creating calculated members and named sets Support for basic data mining The main features of SSAS Enterprise edition include: All the features of SSAS Standard edition Support for advanced data mining, including time series and sequence clustering Support for creating and managing multiple partitions per cube Support for creating and managing multiple aggregations per cube Support for creating and managing translations of metadata Support for data access through OLE DB for OLAP and XML for Analysis (XMLA) Support for creating and managing perspectives Please note that the above list is not exhaustive, and there are other features available in both Standard and Enterprise editions, but the above mentioned are the key features differentiating both edition. SQL Server Analysis Services (SSAS) supports both tabular and multidimensional cubes. Tabular cubes are a newer type of cube model, introduced in SQL Server 2012, that uses a relational model to represent data, similar to a table in a relational database. Tabular cubes can be created using the Power Pivot for Excel add-in, and are designed for use with Power BI and Excel. Multidimensional cubes, also known as OLAP (Online Analytical Processing) cubes, use a multidimensional model to represent data, consisting of dimensions and hierarchies. These cubes have been around since the early days of SSAS and are designed for use with tools like Excel PivotTables and PivotCharts. Both types of cubes can be managed and deployed through SSAS, and can be accessed through the same data access methods, such as OLE DB for OLAP and XML for Analysis (XMLA).