Q&A archive
Questions and answers Andy Mallon contributed across the Stack Exchange network.
DBA Stack Exchange
Question My company moved to azure and started to use azure Sql database and geo replication. For our on-premise servers, we heavily rely on Microsoft transactional replication to move data. Now we setup transactional replication between on-premise server …
Read answerDBA Stack Exchange
Question We have source control in Visual Studio Database Projects (SSDT). To make cross database references, we utilize something like this below in Project References. We need Project references, since Otherdatabase, can choose between two options at our …
Read answerDBA Stack Exchange
Question We've been building a replication network of DBs to give us some degree of Always On without paying the full price tag. Currently we have a publisher DB which replicates to a handful of subscribers, so that if the publisher goes down, we can promote …
Read answerDBA Stack Exchange
Question We're working on migrating some SQL Server VMs to Azure SQL Managed Instances. We did an initial backup/restore without a problem: RESTORE DATABASE [xxx] FROM URL = 'https://<account-name>.blob.core.windows.net/<container-name>/<filename>.bak'After …
Read answerDBA Stack Exchange
Question I have system where my database gets restored every night, and off late the restore is taking a lot longer from past few days. Here is server config: ES 2637 XEON @ 3.5 Ghz Windows virtual server 2012 R2 hosted in a Hyper V environment RAM: 128 GB 8 …
Read answerDBA Stack Exchange
Question At what size should we consider applying Columnstore Index on a table? Is there a SQL Server storage or design guidance? Our Data Warehouse is 5TB total. Additionally, we have tables which are 100GB to 1TB which compose the database. Just curious if …
Read answerDBA Stack Exchange
Question I'm stuck in a way that how to do sum on a column let's say first column of a table (result from another query) without knowing the column name with something like column position id. it's something like this
Read answerDBA Stack Exchange
Question I'm noticing this wait stat on my Azure SQL database, and I've never seen it before. I've tried Googling this wait stat but haven't been able to come up with any good information on what this is, although I saw a couple mentions of it possibly being …
Read answerDBA Stack Exchange
Question I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes. I'm able to rebuild all other indexes with online=on option and it won't …
Read answerDBA Stack Exchange
Question My problem: I have a set of views from which I want to query dynamically. I want to load the view names by SELECT * from sys.all_views into a cursor (let's call it view_cursor) and then do something like this in a procedure:
Read answerDBA Stack Exchange
Question This is in relation to a rollback scenario from a 2012 -> 2016 upgrade. The scenario is that the SQL Server 2012 database is restored onto 2016 and log files applied until it is up to date. Then production systems are cut over to 2016. Database is …
Read answerDBA Stack Exchange
Question Is there a simple way to reconnect a query window that is “not connected” to the original connection that was used by that query without manually picking the server name needed? If I click the Connect button on the SQL Editor toolbar or right click …
Read answerDBA Stack Exchange
Question I have a two node sql server 2008 r2 cluster that we just did an OS upgrade from windows 2008 r2 to windows 2012 r2, the OS upgrade was done in place so the SQL Server Installation stayed in tack. The cluster role won't come back online saying it's …
Read answerStack Overflow
Question I am wondering if it is possible to split up a very large query to have parts of it done on the secondary read only servers, or If this feature only allows me to run separate queries or scripts on the secondary servers. I would also like to know if …
Read answerDBA Stack Exchange
Question I have created a table with 650 Numeric(19,4) columns. When I switch on Page Compression, by running ALTER TABLE fct.MyTable REBUILD WITH (DATA_COMPRESSION = PAGE);I get Msg 1975, Level 16, State 1 Index 'PK_Mytable' row length exceeds the maximum …
Read answerDBA Stack Exchange
Question I have a vendor solution that uses a sql server (2008r2) database. We are allowed to do anything we want in our own schema, but cannot modify dbo objects without vendor permission. Any customization we make is in our own schema (cust). We have full …
Read answerDBA Stack Exchange
Question I am just trying to look up more verbose information for a maintenance plan task that failed. I open up the log file viewer and check the box to view the logs for my maintenance plan. There are no active filters besides that in this example. Then I …
Read answerDBA Stack Exchange
Question I'm trying to balance read traffic between two SQL Server 2016 Enterprise Edition instances (Primary and Secondary) using availability groups and the read only routing. Here is my current setup: The servers are in AWS in two different AZ The servers …
Read answerDBA Stack Exchange
Question I have a powershell script that I am trying to add in to a sql agent job, but the job step is failing with the following error A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS …
Read answerDBA Stack Exchange
Question In our AlwayOn environment I have two SQL servers 2014 Ent.edition (SERVER A, and SERVER B). I setup a transaction replication for [database A ], pointing to listener which shouldn't cause any issue if a failover happened. Tested, worked just fine …
Read answerDBA Stack Exchange
Question I am using SQL Server Express edition, so there is no features for backup plan, I want to take databases backup using bat script, and create a scheduler to be run everyday. I have created .bat file but need to place a time stamp at the end of backup …
Read answerDBA Stack Exchange
Question In SQL Server, if the Database is in Read_only mode, can we drop/remove unused roles? Please let me know. Thanks in advance Asked2017-04-17byBharath Kuamr reddy Answer If a database is in READ_ONLY mode, it is completely read-only. The read-only-ness …
Read answerDBA Stack Exchange
Question Let's imagine a stored procedure that retreives data and do some kind of pagination. This procedure has some inputs describing which set of data we want and how we sort it. Here is a very simple query, but let's take it as an example.
Read answerDBA Stack Exchange
Question I just got a Sql Server 2016 Availability Group setup (A Primary, a Syncronous Secondary and a Async Secondary). I have five databases on the servers. I setup an application to run queries against the server (using Entity Framework with a "failover …
Read answer