All Questions

Filter by
Sorted by
Tagged with
0
votes
1answer
23 views

does automatic checkpoint flushes dirty pages which are related to un-committed transactions to MDF File in SQL Server

when an automatic checpoint runs in background in sql server, i know it flushes all the dirty pages to data file. does it also flushes dirty pages which are related to un-committed transactions into ...
0
votes
0answers
64 views

Data integrity in one-to-many distributed architecture

We are a retail shop that has a central data repository which collects data continuously from geographically distributed 1500 odd servers (each store has one SQL Server instance). Like a one to many ...
2
votes
1answer
288 views

Horizontal and Vertical Fragmentation on Distributed Database

My Architecture: I need to build a database with SQL Server, where the data from each server is distributed to the other server. How can I achieve this kind of architecture? How do I maintain ...
1
vote
1answer
50 views

Architecture design for a large number of db records upgrade

Honestly struggled a bit with the title of the question. Here is the deal. I have a specific set of db operations on various tables across an db. In a simpler sense it's kind of a migration, that I ...
3
votes
1answer
382 views

Should SSIS packages and SQL database be on same server?

I'm a bit new to SSIS. I have a lot of 'dev', building, query, tuning performance on SQL Server but am not the master of admin/ overall performance modeling. So here's my question --- Should SSIS ...
2
votes
2answers
163 views

Difference between OLTP and Inmon Data Warehouse

What is the general difference between OLTP and Inmon-style Datawarehouse? It seems like they are the same. OLTP just copies all the tables into a Read-Only Datawarehouse DB. I know what a Kimball ...
2
votes
2answers
79 views

Splitting the servers

We have a Windows 2012 VM running SQL Server 2012. There are around 20 databases with a total size of 5.83 TBs. There is 64GB of RAM and 16 logical processors on the box. The server is extremely busy ...
1
vote
1answer
70 views

Redundant data or UNION

I couldn't come up with better title. I'm preparing data structure for GDPR and I ran into one problem. I have two types of legal basis for storing the personal data: Law User specific (consent) ...
4
votes
1answer
133 views

Split Brain Administrators

I have been appointed the DBA for our organization. I have been asked by senior officials to allow the two divisions of IT to have full control of their databases. They have asked for the following ...
4
votes
1answer
62 views

Approach to Handling Errors/DataCorrections in Aggregated Data

We're in the process of building a vNext Data/Reporting Platform for capturing Web & Sales Traffic. At a very high level we're capturing Impressions, Clicks & Sales Data which then needs to ...
1
vote
2answers
92 views

Database Roles Security: In SQL Server architecture where does permissions are evaluated in RE or SE or Access methods

I got a question if i create a login and database user for a database named TEST, and i have given permissions to user as db_datareader only. 1) Where does this permission evaluated when a user ...
1
vote
3answers
3k views

Primary and secondary replicas on same server

We're considering different options for HA/DR with SQL Server 2016. AlwaysOn Availability Groups are obviously an option. I understand we can read from secondary replicas, unlike DB mirroring for ...
2
votes
2answers
87 views

Optimize architecture for SQL Server projects [closed]

I often find myself hacking together multiple .sql files for a project and calling them one after the other through Task Scheduler. Everything I know about SQL is self-taught, so I don't know anything ...
2
votes
2answers
4k views

SQL Server 2016 Multiple Availability Groups and instances

So we are getting ready to make a major architecture shift and I get to be on the hook for it, yay! Currently we have 22 separate availability groups each with their own windows cluster across 44 ...
12
votes
4answers
6k views

Drives vs. Mount Points?

The previous Senior DBA set up mount points for all of our drives across every SQL Server throughout the company. The new Senior DBA is horrified by mount points wants to change our standard (mainly, ...
0
votes
1answer
127 views

SQL Server grant to multiple domains

I am working in a joint venture of four different contractors doing one big project. Many of the application will have to use the different ADFS servers each of the four companies have. Some are ...
2
votes
2answers
3k views

Sync two databases on different servers

I can't pick the right solution for my below requirement. Can you people please advise? We have two SQL Server 2012 database servers: First one is in Texas (USA). Our client will use this for UAT. ...
1
vote
1answer
469 views

Automatic Failover or Database Mirroring?

We have a software backed with database. We want to keep database at a remote location and have it all the time available. For this task, a 5 year old project experience came into my mind. I remember, ...
2
votes
1answer
5k views

Changing varchar(max) to varchar(255) requires rebuilding table

Our developers (prior to me being DBA) set the data type for a field to VARCHAR(MAX) when in reality it only needs to be VARCHAR(255). This wasn't discovered until after our table had grown to over ...
7
votes
3answers
1k views

Data warehouse staging architecture

This is a question about data warehouse design. We are setting up a healthcare datawarehouse and starting with 2 major source systems that combine for about 20,000 tables and 2 TB of data. 1) It is ...
3
votes
1answer
58 views

should I perpetuate bigint?

I have a base table used everywhere called Customers. Of course, its unlikely that table will ever grow past int size, but it is keyed on a bigint nonetheless. I don't believe that I can change the ...
1
vote
1answer
128 views

Seeking Suggestion for faster DB Insertion Policy

I have written a server which keeps on receiving concurrent requests from 30K-40K different clients concurrently. Now I need to dump all those individual requests into a database. The database right ...
1
vote
0answers
86 views

SQL Server and Network considerations to transfer backup file to a shared folder

I'm facing with a situation where backups are being transferred manually to a Shared Folder and my goal is to optimize all of this. What I have to deal with is: 8 Windows 2008 R2 Servers 2 Windows ...
0
votes
2answers
46 views

Guidance on inter-server database or data transfer and system architecture

I'm a database developer (not a DBA) responsible for about 20 different non-transactional 1TB SQL Server 2008 - 2012 databases each on its own server. In order to be able to query across databases, ...
23
votes
3answers
6k views

What's the best way to archive all but current year and partition the table at the same time

Task Archive off all but a rolling 13 month period from a group of large tables. The archived data must be stored in another database. The database is in simple recovery mode The tables are 50 mil ...
3
votes
1answer
98 views

Basic question: Simplest way to share company databases

My company is looking to access large quantities of data from another company we have good relations with. Currently we are using a web interface to interrogate their information, however it is quite ...
1
vote
0answers
44 views

Real-time Synchronization [duplicate]

I have created an application in .Net with Microsoft SQL Server for desktop with a local database and an online application with an online database. Data entry will be done on both platforms in real-...
6
votes
1answer
2k views

Multi-Datacenter design for SQL Server

I'm not a DBA and perhaps I can't explain myself properly. I'm looking for some fresh ideas about how, and what I need to deploy, to get a full SQL Server database available across different ...
6
votes
1answer
907 views

Do multiple SQL Server instances increase security?

Context Let's assume you have a server which exposes a web server and one or more web services to store and manage sensible information about physical persons (assume, for this example, complete ...
6
votes
3answers
6k views

Is SQL Server's FILETABLE suitable for storing large files (bigger than 10 GB)?

There is a project for lab. The requirement to save the lab data (raw data and related infomation ) for research for years. So the data must be saved for years, but the data is so big, every raw ...
2
votes
2answers
750 views

Storing revisions in SQL Server (simply much like TFS)

So, what I've got so far: Table: ChangeSets Columns: Id | Comment | Committer Table: Customers Columns: Id | Name Table: CustomerChangeSets Columns: Id | CustomerId (FK) | ChangeSetId (FK) So ...
7
votes
2answers
2k views

Is a geographically distributed application with SQL Server replication a good idea?

We are currently thinking about a target architecture for a geographically distributed application. This application tracks and schedules the movements of all material units across various production ...
3
votes
3answers
2k views

Which is better: one database per application, or just one database? [duplicate]

I ask this specifically for SQL Server, the advantages that I can see for multiple databases are: Logs are smaller (so one runaway transaction cannot cause others (replication etc) to stall) A page ...
0
votes
3answers
723 views

AlwaysOn Architecture - SQL Server 2012 for creating a dev environment?

I was thinking if the AlwaysOn architecture in SQL Server 2012 can be leveraged somehow to create a replica for DEV and QA environment properly in addition to setting up HADR using the same ...
0
votes
1answer
3k views

Real-time merge of two databases on different servers

One of the requirements our customer has given is to merge two databases running on two different machines in real time. This means that we have to build some sync policy (or use SQL Server tools to ...
5
votes
1answer
205 views

Many:Many with Shared Relation

I'm modelling data with multiplicity like this: Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
1
vote
1answer
161 views

Public Database for Mobile Application

What are the proper steps to hosting a public database. Currently I a building a mobile application with Adobe Flash Builder. I have used Microsoft SQL Server, My SQL as well as WAMP Server. All of ...
0
votes
1answer
2k views

32-bit versus 64-bit ODP.NET to Oracle connection

I seem to be having an issue connecting a 32-bit .NET application to a 64-bit server, without a full Oracle client. If I build a 64-bit application, using the 64-bit ODP.NET dll's or a 64-bit instant ...
14
votes
5answers
25k views

Pros/Cons Using multiple databases vs using single database

I was working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree, I'm having trouble collecting ...