Learn SQL from Sarab

#Recover deleted #Azure #SQL Database @SQLChamp Yes, its true. You can indeed recover deleted Azure SQL database during the retention period for the SQL Database automated backups for your service tier. To know more about automated backups and retention period refer this post. Database recovery is possible but there are a few caveats http://www.sqlchamp.com/2016/06/recover-deleted-azure-sql-database/278 http://www.sqlchamp.com/2016/06/recover-deleted-azure-sql-database/278

View On WordPress

#Copy #Azure #SQL Database Deep-dive @SQLChamp #learn If you are supporting Azure you might have to copy Azure SQL Database, In-fact sometimes this becomes a daily task at big\dynamic environments. So before its too late, let's learn the process inside-out to perform this confidently. (In-case you're just a beginner and don't know how to create\provision Azure SQL Database kindly refer this post) To migrate also you need to use Copy command only. http://www.sqlchamp.com/2016/06/copy-azure-sql-database/252

If you are supporting Azure you might have to copy Azure SQL Database, In-fact sometimes this becomes a daily task at big\dynamic environments. So before its too late, let's learn the process inside-out to perform this confidently. (In-case you're just a beginner and don't know how to create\provision Azure SQL Database kindly refer this post) To migrate also you need to use Copy command only.   How Copy actually works under the hood? The copy process uses CREATE DATABASE command and internally under the hood it takes a tail-log backup which is then used in conjunction of Full, Differential and Transaction Log automated backups. (Provide link for automated backups). This command just initiates the process which is asynchronous in nature.   What's the T-SQL statement to copy Azure SQL Database? on same server: If you opt to create a copy of your database using T-SQL, the statement is pretty simple, here is the syntax and one example for the same: CREATE DATABASE AS COPY OF ; here is one example: CREATE DATABASE SQLChampdb1_UAT AS COPY OF SQLChampdb1; on a different server: In case you want to create copy of the database on a different server then in that case you need to use the below mentioned statement and execute it after connecting to master DB of destination server, the statement is like this: CREATE DATABASE Database1_copy AS COPY OF server1.Database1;   What all permissions do I need in order to copy Azure SQL Database? To successfully copy a database, you must be a member of DBManager role. You can also achieve the same task if you are logged-in using the login that created the database, if that login id is also not there server level principal ID anyways has all the required permissions to copy any database on a particular server.   What happens to the service tiers of newly created DB? Since we are copying database, it selects the same service tier which is there on source DB. Be cautious while making copy of a database with premium service tiers because the performance requirements may not be same & this will directly affect your billing. $$ :)   why my ID is listed as DBO? The default behavior is that the login ID which initiates the COPY DB process becomes the database owner of the newly created copy database. You may change it once the database becomes ONLINE.   How to monitor the copy progress of database and how to find when its completed? Azure provides these two catalog views which can help you monitor the database copy process: sys.databases and sys.dm_database_copies. While the copy is in progress the state_desc column value of sys.databases shows COPYING. The other view sys.dm_database_copies view only exists in master DB and you get rows for source and destination DB both while copy process is in progress, once the process completes the rows are removed automatically.   Is it possible to cancel the copy process? Yes, you can easily cancel the copy process by executing a DROP DATABASE statement on the new DB. You may also choose to execute it on the source DB {just to clarify the statement would be DROP DATABASE and by executing on source DB means you execute after connecting to source Database ;) }   Troubleshooting: Why the state of newly copied database is suspect? This happens if the copy fails for any reason, the moment copy operation fails the state of the Database gets changed to SUSPECT mode. You can find the reason in catalog view sys.dm_database_copies there are a couple of columns for the same: error_code, error_desc, error_severity & error_state. In case you face this situation you must drop the database and retry the copy operation.   Troubleshooting: How to fix orphan users\re-map users You won't face this situation if you choose to migrate or copy Azure SQL Database on the same logical server. But when you copy the DB on another server, it only carries the contained users, database users and its permissions without login IDs. This happens to users which are based on logins. Obviously the users won't be able to login because either the logins don't exist or the SIDs won't match. So in order to fix this you must use the ALTER USER statements to remap the users with logins on the destination server. I'll shortly write a dedicated blog on this topic.   How to migrate or copy Azure SQL Database using portal? Copying the DB using Azure portal is very simple & easy but in case someone still needs assistance, here are the screen shots of the same: (I am pasting screenshots from both New and Old Portal) New portal:     Old portal: Just login on the old Classic portal and select the Azure SQL Database (the one which you want to copy) and look at the bottom of the frame (portal) there you'll find an option to "copy" - select the option.   Step -2 to Copy Azure SQL Database. Happy Learning! feel free to leave a comment. :) Thanks, Sarabpreet Singh Anand Subscribe now to get latest Tips\blog posts in your Inbox Follow @SQLChamp#Copy #Azure #SQL Database Deep-dive @SQLChamp #learn If you are supporting Azure you might have to copy Azure SQL Database, In-fact sometimes this becomes a daily task at big\dynamic environments. So before its too late, let's learn the process inside-out to perform this confidently. (In-case you're just a beginner and don't know how to create\provision Azure SQL Database kindly refer this post) To migrate also you need to use Copy command only. http://www.sqlchamp.com/2016/06/copy-azure-sql-database/252 http://www.sqlchamp.com/2016/06/copy-azure-sql-database/252

Automated #Azure #SQL Database #backup - @SQLChamp @Azure Hi Friends Azure has a new functionality called Automated SQL Database backup, since its new you all must be having lot of questions regarding the same. I've compiled a list of questions with their answers, here you go: What is Automated SQL Database backup? http://www.sqlchamp.com/2016/06/automated-sql-database-backup/246 http://www.sqlchamp.com/2016/06/automated-sql-database-backup/246

View On WordPress

#New Database scoped Configurations #SQL #Azure #SQLAzure @SQLChamp MS introduced new Database scoped configurations in SQL Azure (V12) and SQL Server 2016; Need for new Database Scoped Configurations Have you ever faced a situation where one of the database requirements are totally different from other like MAXDOP settings and you wanted to set a different MAXDOP for a single DB; or you wanted a feature which can keep such Primary and secondary database configurations in sync. MS is listening and they introduced Database Scoped Configurations and you can change them with this DDL statement: Alter Database Scoped Configuration. This was a much needed enhancement. Here's a list of Database scoped configurations which are now available on DB level. These are not new configurations, but now you can also configure these options at the database level rather than the server level. So imagine you are planning to consolidate servers on a single instance, but earlier you were not able to do it because of different needs of such configurations, http://www.sqlchamp.com/2016/06/database-scoped-configurations/230 http://www.sqlchamp.com/2016/06/database-scoped-configurations/230

#New Database scoped Configurations #SQL #Azure #SQLAzure @SQLChamp MS introduced new Database scoped configurations in SQL Azure (V12) and SQL Server 2016; Need for new Database Scoped Configurations Have you ever faced a situation where one of the database requirements are totally different from other like MAXDOP settings and you wanted to set a different MAXDOP for a single DB; or you wanted a feature which can keep such Primary and secondary database configurations in sync. MS is listening and they introduced Database Scoped Configurations and you can change them with this DDL statement: Alter Database Scoped Configuration. This was a much needed enhancement. Here's a list of Database scoped configurations which are now available on DB level. These are not new configurations, but now you can also configure these options at the database level rather than the server level. So imagine you are planning to consolidate servers on a single instance, but earlier you were not able to do it because of different needs of such configurations, http://www.sqlchamp.com/2016/06/database-scoped-configurations/230 http://www.sqlchamp.com/2016/06/database-scoped-configurations/230

View On WordPress

How to get last used #timestamp in #SQL #Azure database? @SQLChamp A timestamp is the time at which an event is recorded (inserted or updated), and timestamp is guaranteed to be unique in the database. So what will you do if for some reason you need to find the last used timestamp in SQL Azure Database? Well, the answer is @@DBTS function. @@DBTS This function is there since http://www.sqlchamp.com/2016/06/lastused-timestamp/217 http://www.sqlchamp.com/2016/06/lastused-timestamp/217

View On WordPress

AT Time Zone – Convert #timezone directly using #SQL #azure @SQLCHAMP Globalization brings new opportunities and enhance economic growth to any business, but along with all the goodness it also brings complexity to manage different time zones. One has to be always on toes to match different times. but what if you've got an application\code where you have to convert timezone within SQL Server data? If you are using SQL Azure Databases or SQL Server 2016 you don't have to worry about this trouble because Microsoft introduced this brand new function to make things easy for you: AT TIME ZONE; AT Time Zone This function converts an inputdate to target time zone as per your wish, isn't it cool. I am loving it, it simplified too many things. Now I don't have to write code\use dlls to convert date time – DST was the biggest issue and this function simply takes care of DST on its own. http://www.sqlchamp.com/2016/06/at-time-zone/206 http://www.sqlchamp.com/2016/06/at-time-zone/206

AT Time Zone – Convert #timezone directly using #SQL #azure @SQLCHAMP Globalization brings new opportunities and enhance economic growth to any business, but along with all the goodness it also brings complexity to manage different time zones. One has to be always on toes to match different times. but what if you've got an application\code where you have to convert timezone within SQL Server data? If you are using SQL Azure Databases or SQL Server 2016 you don't have to worry about this trouble because Microsoft introduced this brand new function to make things easy for you: AT TIME ZONE; AT Time Zone This function converts an inputdate to target time zone as per your wish, isn't it cool. I am loving it, it simplified too many things. Now I don't have to write code\use dlls to convert date time – DST was the biggest issue and this function simply takes care of DST on its own. http://www.sqlchamp.com/2016/06/at-time-zone/206 http://www.sqlchamp.com/2016/06/at-time-zone/206

View On WordPress

Find End-Of-Month ( #EOMONTH ) date from #SQLAzure #SQL Hi Friends, have you ever struggled to find the end of the month date; I know its not a big deal you can get it from Calendar but what if you need that date for some business logic of yours? May be for Invoice processing or some other scheduling within SQL Server and you want to store \process that date? Yes, it becomes time consuming. Thankfully SQL Server has this (EOMONTH) datetime function which can be used to calculate the end of the month date automatically for any given month. Function:EOMONTH (this functions is available in Azure SQL Databases & starting SQL Server 2012) this takes two parameters http://www.sqlchamp.com/2016/05/eomonth-sql/199

View On WordPress

#RLS (Row Level Security) basics #sql #Azure RLS (Row Level Security) is a brand new feature which has been introduced in Azure, SQL Databases (v12) and SQL Server 2016. This feature provides you access control on rows of tables for users\logins querying the data. Basically, it filters the rows based on certain characteristics of Users\Logins. After getting this feature it becomes very easy to implement separation of duties & you get the power to deny read rights to even those users who have membership of SysAdmin role. Which in turn provides more confidence to your end users. Your data remain safe & accessible to only authorized users. This is a great security feature which will help multiple industries, in-fact many organizations developed in-house solutions\workarounds to implement something similar on their own; but the maintenance overhead was too much & it becomes very complex to manage on multiple tables. With this new feature you get this functionality straight out of the box and you don't even have to change anything on your application side. RLS is one of the most robust and reliable security features, reason being RLS (Row level security) is implemented at the database level itself, which means there are no back doors\alternatives, http://www.sqlchamp.com/2016/05/rls-basics/192