Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Leave positive messages for yourself to see regularly

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Custom SQL Server on RDS

One of the challenges in moving to the cloud is achieving a similar level of performance to what you would get with dedicated hardware on-premises. The cloud is someone else's computer, and you are limited to the choices that the vendors allow you to rent. In many cases, you might even share the physical hardware resources with other customers, though many cloud providers have started to allow dedicated hardware reservations in the last few years.

One of the challenges of choosing a PaaS cloud database platform is that there are often restrictions that differ from a similar platform on-premises. This is often to ensure that one customer doesn't affect another, or that customers don't make changes that might prevent the vendor from supporting the system. Azure SQL Database gives us a database only, no instance features, though we do see a virtual master database.

AWS, however, is changing its RDS offering to allow customers to customize their database software. They started with Oracle and then added SQL Server. For the SQL Server offering, they will let you not only change the instance config with things like CLR and install drivers in the host OS. They'll continue to manage the overall service, and handle backups, DR, etc. You can focus on your database, but if you have special configuration needs, you can set them up.

This is a nice evolution of the cloud by giving customers more control and meeting their needs, but not forcing them to handle everything. While a lot of technologists don't think the OS adds a burden to a DBA, I'd disagree. I think the popularity of RDS and Azure SQL Managed Instance show that a lot of customers feel the way I do. Paying for a service to be managed is valuable, and it allows your staff to focus on more helpful tasks, like performance tuning, finding data quality issues, etc.

Of course, your staff has to take advantage of that extra time. I see plenty of companies that don't culturally change how they work with their systems, and that means that a lot of the benefits the cloud offers aren't being seen by the company.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
 Featured Contents
Stairway to SQL Server Security

Stairway to SQL Server Security Level 5: Schemas and Security

Don Kiely from SQLServerCentral.com

In this stairway level you’ll learn how you can give principals access to groups of objects by assigning permissions on schemas instead of individual tables, code modules, and other objects. You’ll also learn about the benefits of user-schema separation and how it can increase object security, and how using default schemas for users and groups can simplify object access management and security.

External Article

SQL Server and Oracle Transparent Data Encryption Differences and Similarities

Additional Articles from MSSQLTips.com

In this article, we look at how to set up Transparent Data Encryption for SQL Server and Oracle along with the differences and similarities.

External Article

Data Governance: Joining the Dots

Additional Articles from Redgate

Every organization must perform data governance. This requires planning, oversight, and control over the management, security, resilience and quality of data and over the use of data by the organization. In larger organizations, it can be a complex task. William Brewer explains what's involved.

Blog Post

From the SQL Server Central Blogs - Creating a SERVER ROLE created a LOGIN as well.

Kenneth.Fisher from SQLStudies

One of my co-workers came to me the other day and told me that they found their network id as ... Continue reading

Blog Post

From the SQL Server Central Blogs - Distributed Replay: The Little Engine That Almost Could

Grant Fritchey from The Scary DBA

Honestly, sincerely, no kidding, I love Distributed Replay. Yes, I get it. Proof positive I’m an idiot. As we needed proof. To be a little fair to me, I...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Another Conversion Question

What is the result of this code? 
select 4444.0 * N'1111.0'

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

More Conversion Quetsions

I have this code in SQL Server 2019.

select 4.0 * N'1111.0'

What is the result when I run this?

Answer: Arithmetic overflow error converting nvarchar to data type numeric.

Explanation: This creates an overflow error. The data types are converted based on precedence. In this case, we have two data types:

  • decimal(2,1)
  • nvarchar(6)

The decimal has a higher precedence and converting the 6 character value into a 2,1 scale is an overflow. If we do this, it works:

select CAST(4.0 AS numeric(6,1)) * N'1111.0'

Ref: Cast and Convert - https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration
Backup directly to cloud storage for 1.5 tb database - Today we looking to take backup from on prem SQL to cloud storage in blob storage directly and DB size is 1.5 tb, network speed 100 mbps. Yes, its possible to do backup directly to cloud storage blob. But want to know which method this copy / backup can work like AZ copy or any […]
SQL Server 2016 - Administration
Permissions for Conflict Viewer - Hi, I'm trying to find the minimum permissions necessary to give a user access to the Replication Conflict Viewer and allow user to view and resolve conflicts in Management Studio.
SQL Server 2016 - Development and T-SQL
How to drill down in recursive cte? - I have the following working example: DROP TABLE IF EXISTS consignment_levels; GO CREATE TABLE consignment_levels( consignment_id INT NOT NULL, parent_id CHAR(10) NULL, level_id CHAR(10) NOT NULL, level_type CHAR(10) NOT NULL,--case, master_case, pallet ); CREATE UNIQUE INDEX consignment_levels_U01 ON consignment_levels (consignment_id,parent_id,level_id); GO DECLARE @v_consignment_id INT = 1000; DECLARE @c_pallet CHAR(6) = 'PALLET', @c_master_case CHAR(6) = 'MASTER', […]
Parsing XML with SQL Server - Thanks in advance for the assistance. There is some data on a website that I would like to import into a SQL Server database to query: Disclosure Table The data is also presented in an XML file: https://www.thetakeoverpanel.org.uk/new/disclosureTable/v3/disclosuretable.xml https://www.thetakeoverpanel.org.uk/new/disclosureTable/v3/disclosuretable.xsd I'm having difficulties parsing the data. I think I'd like three tables variables (or temp tables) […]
Complex SQL Pattern Matching Assistance - Hello Community, Can someone assist in SQL pattern matching. I have two tables company and organisations. In the organsations table there is a field 'cbname'. I would like to find a 85% match between the field 'cbname' and the field in company table called 'companyname'. I would then like the results to be added to […]
How to calculate canceled accounts / total accounts - Good Morning, I have the two tables customers (joined) and their orders (dateoforder- record perday) CREATE TABLE Customers ( CID int NOT NULL PRIMARY KEY, GroupName varchar(255) NOT NULL, FirstName varchar(255), Age int, CustomerStatus SMALLINT, ---1 Means Active, 0 Inactive CustomerCancelDate Date, DateofJoin DATE ); INSERT INTO Customers Values(100, 'ABC123', 'John',30,1,NULL,'01/14/2022') INSERT INTO Customers Values(101, […]
Development - SQL Server 2014
SQL XML help - I have the following query that generates the output SELECT top 1 AccountNumber as 'ExternalId' from Account FOR XML PATH('Element'),type --output generated 3002543105   How can I get the following output 3002543105   Thank you in advance
Recursive calculation // calculation column referring to itself - I have following data table with quantities and minimum stocks. I would like to check if the cumulative quantity goes below the minimum stock. If so the column "ProductionProposal" should calculate the quantity that needs to be produced in order to fill up the stock (with consideration of the defined minimum lot size). My problem is […]
SQL 2012 - General
SQL Server 2012 Migration ideas? - Hi Guys, I have a 2008R2 VM running SQL Server 2012. I would like to migrate hosts to a 2012 box that will also be running SQL server 2012. The original SQL box has an OS partition and a Data partition (separate VMDK's). Data partition is the root directory and contains all DB's. I could […]
SQL Server 2019 - Administration
Issue with SQL Server mirroring endpoint creation using SQL Authentication - I am trying to create EndPoint in SQL Server 2019 using transact SQL CREATE ENDPOINT endpoint_mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 5022 ) FOR DATABASE_MIRRORING ( AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = SUPPORTED, ROLE=ALL); GO   I want to use SQL Authentication for creation of mirroring endpoints. I can use SQL Management studio to do that, but […]
SQL Server 2019 - Development
2 new Column from string - I have string in table where column has the value 163 Bathroom {BATHROOM CLEANING}{2.5%} I want to 2 new columns from this string column1 BATHROOM CLEANING column2 2.5% The first curly bracket { is starting point for first new column and will end data closing curly } bracket and second curly bracket { for second […]
Azure Data Factory
Upskill Azure Data Factory knowledge - Hey guys, I'm a DE with of 2 experience and I've been working with Azure Data Factory as the main ETL tool for 6 months now. However, I feel I need to gather more knowledge and assume more responsibilities inside my team but most of the senior professionals don't have much time to teach me. […]
Reporting Services
Combine large tables into one dataset - Hello everyone, I'm facing a small challenge. I have four different data pots and need to merge them into one data set. The join at the database level is generally not a problem, but the performance is sub-par. The four tables are as follows. projectStructure (65,527 rows) cashManagement (261,135 rows) bookedHours (48,233 rows) budgetValues (35,836 […]
Analysis Services
MDX Year on Year - Hi Guys Trust you keeping well. I have an issue with an meausre where we need to show the previous years data. Basically we need to compare a certain month on a daily basis the MTD value vs the previous year for the same month MTD per day. I am not that well versed in […]
Integration Services
Data Flow Task Error showing generic message - I'm having difficulties performing a Data Flow Task between a .csv file to OLE DB. By default, the column [ID] is in DT_STR datatype. It throws the following generic error: GF Data Flow Task:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (515) failed with error code 0xC0209029 while processing input […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -