Azure / SQL Server / Azure DB / Add Logins, Users, Permissions

Posted by in Azure, SQL Server on Oct 30th, 2017 | 0 comments
Azure / SQL Server / Azure DB / Add Logins, Users, Permissions
After we created our SQL Server and Azure DB now we need to connect to our Azure DB using SSMS. First of all, we need to add firewall rule to be able to access our Azure SQL Server from our location. Click on yours Azure SQL Server on Azure portal, and choose Firewall / Virtual Networks.   Then just add your firewall rule and IP Address, or IP range if you want. After firewall rules have been added, users can connect to a Azure SQL Database using administrator accounts, or as an database owner or database users (If they already exist)....
read more

Azure / SQL Server / Create DB on Azure

Posted by in Azure, SQL Server on Oct 30th, 2017 | 0 comments
Azure / SQL Server / Create DB on Azure
First of all, we need to login to Azure Portal and to create Logical SQL Server on Azure   Next, we need to put the name of the server, as well as server admin login account for this Azure Logical SQL Server. After that, we can create a new one or to use existing resource group.   When we finish this part of configuration we can start with creating Azure SQL Server Database. We need to define database name, and subscription we want to use for this SQL Database   After that, we need to select source of our database (blank,...
read more

Azure / SQL Server / Cannot open user default database (Login failed)

Posted by in Azure, SQL Server on Oct 26th, 2017 | 6 comments
Azure / SQL Server / Cannot open user default database (Login failed)
You want to create a new login on Azure Logical SQL Server using your Azure Admin account. To do that you must be connected to the master database on SQL Azure with the administrative login. CREATE LOGIN [azure_db_user] WITH PASSWORD=N'password' GO   After that, you need to create new DB user for that login (db_owner in this example). To be able to do that you must be connected as an admin to the database where you want to create that user. CREATE USER azure_db_user FOR LOGIN azure_db_user GO EXEC sp_addrolemember N'db_owner',...
read more

Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)

Posted by in SQL Server, SQL Tips and Tricks on Oct 11th, 2017 | 2 comments
Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)
“Operating system error 32 (The process cannot access the file because it is being used by another process)” message in SQL Server error log BackupDiskFile::OpenMedia: Backup device ‘\\NAS\Shared_LogShip\DB_log.trn’ failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.). If you are using shared NAS for your logs backup location for the primary server and you restoring those logs directly to the secondary server (without copy job) you can face this kind...
read more

SQL Server / Upgrading the Secondary Server Instances (STANDBY)

Posted by in SQL Server, SQL Tips and Tricks on Oct 9th, 2017 | 0 comments
SQL Server / Upgrading the Secondary Server Instances (STANDBY)
If you planning to upgrade to the new SQL Server Version 2016 or 2017 and if there is log shipping in place between those servers you should follow some upgrading order. Always upgrade the secondary SQL Server instances first, before upgrading the primary server instance. If you do the opposite log shipping will fail because a log backup created on a newer version of SQL Server cannot be restored on an older version of SQL Server. But this is not a reason why I am writing this post. I am writing to alert you that in case you have a secondary...
read more

SQL Server / Different SQL Versions different query hashes

Posted by in SQL Monitoring, SQL Server on Oct 9th, 2017 | 0 comments
SQL Server / Different SQL Versions different query hashes
Recently I did upgrade from SQL Server 2012 towards version 2016, and I had some PRTG maps in place for two really important queries. After upgrade monitoring failed. Query hash has been changed for the exact same query. Honestly, I did not pay much attention to that previously since the majority of those PRTG maps checking same versions of SQL Server. Also, I was under impression that query optimizer computes the query hash during query compilation on base on query logic.  In that case, if query hash is changed that also means query has been...
read more

SQL Server / Backup File Size Issue

Posted by in SQL Monitoring, SQL Server on Oct 8th, 2017 | 0 comments
SQL Server / Backup File Size Issue
Yesterday, one of my SQL alerts just sent me an email about my daily backups size. I noticed that my backup has doubled in size. What happened is that I changed my Task Scheduler which is running my PowerShell backup script and accidentally ran backup twice for the same backup set.  Nevertheless, SQL Server is smart enough and instead of overwriting your existing backup file its just append the new one. You can see from the picture below that default for SQL server (2012 in this case) is “append to the existing backup set”. Can be confusing...
read more

SQL Server / Index Usage Report Project / Most Expensive Query Plans

Posted by in Index Usage Report Project, SQL Monitoring, SQL Server on Oct 8th, 2017 | 0 comments
SQL Server / Index Usage Report Project / Most Expensive Query Plans
I created this ETL to fetch and save expensive execution plans daily. When ETL process runs again firstly it creates directory and copy all plans from previous day to that directory, and only after that fetch new execution plans. This way I can look old plan later if I like. SSIS Package Description. Control Flow Screen   I am using File System Task to create new directory using the name of  day before, to store my yesterday’s execution plans.   As you can see I am using variable FolderPath to create folder name with day before...
read more

SQL Server / Index Usage Report Project / Most Expensive Queries

Posted by in Index Usage Report Project, SQL Monitoring, SQL Server on Oct 8th, 2017 | 0 comments
SQL Server / Index Usage Report Project / Most Expensive Queries
Index Usage Report Project / Expensive Queries Table I created this ETL process (SSIS) to populate my WH Expensive Queries table, so I can later join this table using plan_handle to determine if an index is used within those expensive queries. Every time I run this ETL I am truncating this WH table and inserting new rows. Control flow > SQL Task   SQL Task   After that under the Data Flow, I created data source and destination of my ETL process   For OLE DB Source I used SQL command SET TRANSACTION ISOLATION LEVEL READ...
read more

SQL Server / Index Usage Report Project

Posted by in Index Usage Report Project, SQL Monitoring, SQL Server on Oct 5th, 2017 | 0 comments
SQL Server / Index Usage Report Project
 Index Usage Report Project   The general project idea was to create the report about every index usage within DB. To be able to see a daily number of scans, seeks, page splits for all indexes or key lookups for clustered. You also can change the date range to see how this index behaved in past.     Also, to be able to see all expensive queries using that index (red one are expensive).     To see and save their execution plans as well.       Project concept   Index Usage Stats system views...
read more

« Previous Entries