Modifying Scheduled Jobs
https://learn.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-ver16
Topics
1. Lower case, point in time recovery, web & standard edition, constraints, dump file, Configuration Manager
2. Head Blocker, NT Authority System, Auto increment
- LAZYWRITER_SLEEP, PAGEIOLATCH_KP, PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC, FILESTREAM_CHUNKER
3. Power BI, FT_IFTS_SCHEDULER_IDLE_WAIT, QPJOB_WAITFOR_ABORT, HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO, HADR_WORK_POOL
Additionally,
An IRC which is nothing but Initial Research on CheckDB (with the help of Profiler).
The Three important key factors always...
1. Performance
2. Configurations
- Waittype related questions.
Ans. 1. Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type isn't typical, except when a task is waiting for a tape mount.
Ans. 2. Occurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type doesn't indicate a problem.
Three types of roles we have...
1. Server Roles
2. Database Roles
////////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
1. DAC
2. CLR
3. TRY....CATCH
1. DAC:- SQL Server provides a dedicated administrator connection (DAC). You can use the DAC to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection.
By default, the DAC is only available from a client application directly on the server. To enable client applications on remote computers to use the DAC, use the remote admin connections option of sp_configure.
By default, the DAC only listens on the loop-back IP address (127.0.0.1), port 1434. If TCP port 1434 isn't available, a TCP port is dynamically assigned when the Database Engine starts up. When more than one instance of SQL Server is installed on a computer, check the error log for the TCP port number.
The following table lists the possible values for the remote admin connections option.
Value Description
0 Only local connections are allowed by using the DAC.
1 Remote connections are allowed by using the DAC.
Example
The following example enables the DAC from a remote computer:
SQL
Copy
EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO
------------------------------------------
2. CLR:- SQL Server and Azure SQL Managed Instance enable you to implement some of the functionalities with .NET languages using the native common language runtime (CLR) integration as SQL Server server-side modules (procedures, functions, and triggers). The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support.
For SQL Server users and application developers, CLR integration means that you can write stored procedures, triggers, user-defined types, user-defined functions (scalar and table valued), and user-defined aggregate functions using any .NET Framework language, including C# Visual Basic .NET. SQL Server includes the .NET Framework version 4 preinstalled.
This 6-minute video shows you how to use CLR in Azure SQL Managed Instance:
Code access security no longer supported
CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with PERMISSION_SET = SAFE might be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. In SQL Server 2017 (14.x) and later versions, the sp_configure option, clr strict security, enhances the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. The clr strict security option can be disabled for backward compatibility, but isn't recommended.
We recommend that you sign all assemblies by a certificate or asymmetric key, with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database. SQL Server administrators can also add assemblies to a list of assemblies, which the Database Engine should trust. For more information, see sys.sp_add_trusted_assembly.
When to use CLR modules
CLR Integration enables you to implement complex features that are available in .NET Framework such as regular expressions, code for accessing external resources (servers, web services, databases), custom encryption, etc. Some of the benefits of the server-side CLR integration are:
A better programming model. The .NET Framework languages are in many respects richer than Transact-SQL, offering constructs and capabilities previously not available to SQL Server developers. Developers can also use the power of the .NET Framework Library, which provides an extensive set of classes that can be used to quickly and efficiently solve programming problems.
Improved safety and security. Managed code runs in a common language run-time environment, hosted by the Database Engine. SQL Server uses this to provide a safer and more secure alternative to the extended stored procedures available in earlier versions of SQL Server.
Ability to define data types and aggregate functions. User-defined types and user-defined aggregates are two new managed database objects that expand the storage and querying capabilities of SQL Server.
Streamlined development through a standardized environment. Database development is integrated into future releases of the Visual Studio .NET development environment. Developers use the same tools for developing and debugging database objects and scripts as they use to write middle-tier or client-tier .NET Framework components and services.
Potential for improved performance and scalability. In many situations, the .NET Framework language compilation and execution models deliver improved performance over Transact-SQL.
SQL Server Language Extensions provide an alternative execution environment for runtimes close to the Database Engine. For a discussion of the differences between SQL CLR and SQL language extensions, see Compare SQL Server Language Extensions to SQL CLR.
-------------------------------
Dealing with hard work and perfection, because, We also believe in Query Alignment & Integrity.
Any query will be considered.
T. Amit Vaid S.
The flexiblity of Port
is for any environment.
No comments:
Post a Comment