Mustafa EL-Masry SQl server Administration

Moving Database Files on SQL Server Always On


I think all of us know how to move files for SQL Server databases from disk to another disk but today I will explain how we can do it on SQL Server always on to reduce the downtime and taking new backup to return the database to always-on again. Let us start on the technical steps.

  • Preparations Steps
  • Check the current DB Files locations
  • Check Always on Health Status
  • Disable all backup jobs on Primary and secondary servers
  • Suspend the data movement on Secondary server
  • Remove Database from Primary server
  • Update DB files by new disk location on secondary server
  • Stop SQL Server instance on Secondary server
  • Move (CUT) DB Files from old location to new Location (Past)
  • Start the SQL Server instance again
  • Verify the DB File Location Using T-SQL on the post
  • Join the DB to availability group from Primary server
  • Check SQL Server always on Health Status Using T-SQL on the post
  • Enable the SQL Server Jobs\
  • Failover the SQL Server for Primary (X) to Secondary Server (Y) and do the same steps on (X)

Let’s Start The Technical Demo

Continue reading “Moving Database Files on SQL Server Always On” →
Mustafa EL-Masry SQl server Administration

How to hide SQL databases from users using SSMS


I know the First question you will ask it  “Why we need to hidden it IF the user doesn’t have access on it and he can’t do any operation on this DB 🤔” Simple answer “Business needs & Business Case & Management Request” Whatever the answer but this is the request from management So let us see How we can do it using two solutions.

First Solution hide the DB using Permissions

Before starting on the solution, I need to highlight important point “to implement this solution the user will take ownership of the DB” and this will break the security role. To hide the DB, we need two steps after it the user when he will log in to SQL Server management studio, he will see only this database:

  • Deny or remove the permission of View on databases from the Public User on the instance level.

At this time no one will able to see any databases on the SQL Server management studio except the admins only this means you should grant this permission for specific users that you need them to view all databases “It is just view Permission, accessing the DB is another permission”.

  • Change the DB Authorization by the user name you need him to view this DB only or you can do this step on two or three or more databases at this time this user when he login to SQL Server Management studio he will view these databases only the other databases will be hidden.

Technical deployment scripts

Continue reading “How to hide SQL databases from users using SSMS” →