SQL SERVER DBA, Linux and Azure

Finding an object in server in SQL Server using SP_MSFOREACHDB

 Today one of my friend asked me that she has created a stored procedure in a database, suddenly she has recognized she has forgotten the database in which the procedure was created. We suggested him to use the below query which searches each and every database in the server and returns the database name and the object name that is specified in where clause.

SP_MSFOREACHDB searches the objects in all databases in the server.

Example:

SP_MSFOREACHDB
'
USE ?
select
    ''?'' Database_Name
   , Name Object_name
from sys.procedures
WHERE name LIKE ''USP_Test''

Using SQL Server script to get list of users and their database roles in SQL Server

use Database_Name;

go 

SELECT  dPrinc.name AS [Members],dRole.name AS [Database Role Name]

FROM sys.database_role_members AS dRo  

JOIN sys.database_principals AS dPrinc  

    ON dRo.member_principal_id = dPrinc.principal_id  

JOIN sys.database_principals AS dRole  

    ON dRo.role_principal_id = dRole.principal_id;

About Update stats and Indexing Optimize in SQL Server

 Update stats:

Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases, you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

Indexing Optimize:

SQL Server Indexes are special data structures associated with tables or views that help speed up the query. 

Reorganize or rebuild a fragmented index in SQL Server by using SQL Server Management Studio or Transact-SQL. The SQL Server Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. Over time, these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly, especially scan operations.

You can remedy index fragmentation by reorganizing or rebuilding an index.

Creating, backing up, and restoring a database are critical databases administration tasks that we must perform regardless of Operating System platform. Good news! If you had done these steps in Windows, the same steps apply in Linux. You just need to be aware of the file path structure.

 Creating a Database and Verifying Its Data and Log Files

To create a database and verify its data and log files, perform the following steps:

1. Connect to your Ubuntu Server from your PuTTY remote terminal if not already connected.

2. Connect to your SQL Server:

sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

3. Create the database, sqldbanow:

create database DVSQLRocks
go

4. Specify the database to use:

use sqldbanow
go

you will see the message “Changed database context to ‘DVSQLRocks'”.

5. Create a table, major_conference, and insert two rows of data:


create table major_conference (id int, name nvarchar(50), start_date datetime)
go
insert into major_conference values (1, ‘SQL Saturday LA’, ‘2019-06-15’)
insert into major_conference values (2, ‘SQL PASS Summit’, ‘2019-11-05’)
insert into major_conference values (3, ‘AWS Re:Invent’, ‘2019-12-02’)
go

Figure 1: Create a database and table and insert data into a table


6. Find the location of the data and log files for the newly created databases:


select DB_NAME(database_id), physical_name from sys.master_files
where DB_NAME(database_id) = ‘sqldbanow’
go

Figure 2: Query the sys.master_files to find the location of the data and log files

Backing up a Database:

To backup a database, perform the following steps:

1. Connect to your SQL Server if not already connected:


sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

2. Backup our sample database, sqldbanow:


backup database [sqldbanow] to disk = N’/var/opt/mssql/data/sqldbanow_20210127.bak’
go

Figure 3: Backup a database

If executed successfully, you will see the confirmation message as in Figure 3.

Restoring up a Database

To restore a database, perform the following steps:

1. Find the location of the backup file. In our example, it is 

/var/opt/mssql/data/sqldabanow_20210127.bak.

2. Connect to your SQL Server if not already connected:
sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

3. Change the database to master:


use master
go

Just as in SQL on Windows, you can’t restore a database if it is in use.

4. Restore the database. In this example, we will restore and replace the existing sqldbanow databases:
use master
go
restore database [sqldbanow] from disk = N’/var/opt/mssql/data/sqldbanow_20210127.bak’ with norecovery, replace



Figure 4: Restore a database



How many IP Addresses we require for setting up Active\Active SQL Server cluster with Analysis services?

  •  2 Windows nodes – Public
  •  2 Private IP Addresses – Private
  • 1 Windows Virtual Cluster Name
  • 1 MSDTC (Optional)
  • 1 SQL Server Virtual Network Name
  • 1 SQL Server Analysis Services